0

I have 2 transactions come into my application that want to update a subset of the same entities. However some of those entities are not in the database, so both threads create the same entity and try to commit it. Causing a database Duplicate entry for primary key. How do I handle this?

eg Transaction 1 wants to update entities A,B,C,D,E,F Transaction 2 wants to update entities D,E,F,G,H,I My Database only has records for entities A,C,E,G and I

So Transaction 1 and 2 attempt to create entity D. Which the second thread fails to commit as D is the primary key and now already exists.

I've tried using pessimistic_write, but this doesn't help records that don't exist yet.

I've tried wrapping my persist() call in a try catch but it appears that the exception is thrown internally to the JPA and my application never sees it.

Most google searches return examples about locking existing records, and if they happen to mention concurrent inserts it seems to be beyond the scope of the article.

Some articles say I should be using a database generated unique id, this doesn't work in my instance. Or I should be using a version in my entity, but again my entity model improves its accuracy based on its previous values, the values of the entities are not isolated but rely on previous transactions.

I've thought about pre-populating the database but there is probably over a billion possible valid entity keys, although I'm only working on a set of about 20,000 keys but I don't know what they are until a transaction comes in.

This is my persist code with try catch wrapper, I never see the message "INSERT Persistence collision," come out of my application, just the JPA stack trace.

if (cellmu == null)
{
    CellMUEntity cellmuNew = new CellMUEntity();
    cellmuNew.setId(cellOuter);
    cellmuNew.setDistribution(mus);
    try {
        em.persist(cellmuNew);
    } catch (Exception e) {
        System.out.println("INSERT Persistence collision, " +e.getClass().getName() +" read and refine (" + cellOuter+")" );
        cellmuNew = em.find(CellMUEntity.class,cellOuter,LockModeType.PESSIMISTIC_WRITE);
        cellmuNew.setDistribution(cellmuNew.getDistribution().refine(mus));
        em.merge(cellmuNew);
    }
}

here is part of the stack trace from the jpa...

[2019-05-01T21:07:07.654+1000] [glassfish 4.1] [WARNING] [jts.unexpected_error_occurred_in_after_completion] [javax.enterprise.system.core.transaction.com.sun.jts.jta] [tid: _ThreadID=70 _ThreadName=p: thread-pool-1; w: 2] [timeMillis: 1556708827654] [levelValue: 900] [[
  JTS5054: Unexpected error occurred in after completion
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.1.v20150605-31e8258): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '6ad63bd4' for key 'PRIMARY'
Error Code: 1062
Call: INSERT INTO mucell (cellid, mu_high, mu_low) VALUES (?, ?, ?)
    bind => [3 parameters bound]
Query: InsertObjectQuery((face=3, pos=ad63bd400000000, level=13):[0.0, 47499.492305012])
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
silicontrip
  • 906
  • 7
  • 23
  • Persist is a no-op that just indicates to JPA that the entity needs to be managed with the transaction, so if you want to be able to trap the exception, you wrap the transaction commit call in a try/catch block. You can also flush the context, forcing the insert statement to be executed, using em.flush(). If constraints are processed immediately, will also result in the constraint violation being thrown. You then back off and try a new transaction again since the object should be there this time around. – Chris May 01 '19 at 14:59
  • I added the em.flush() and ended up in my exception handler, but discovered that the duplicate key exception is not the same key just persisted. I assume that the flush is still taking time to commit all entities to the database. I thought that I may've had the wrong annotation in my entity class that says this column is unique. This is a bit frustrating, because if I let the exception throw up to the MDB it rolls back the wrong transaction. Do I have multiple copies of my entities? Is there something I need to add to my entity manager to make it a singleton? – silicontrip May 02 '19 at 00:08
  • You cannot call persist, catch the exception, and then try merging. Once you get a persistence exception, your transaction is in an inconsistent state; you must retry it from the beginning. I don't know why your code would be rolling back the wrong transaction; that is very very wrong and would have to do with your translation setup which you have not included in your question. You need to look into how you are managing your EntityManager instance as it is not thread safe and should be tied to JTA if your container is handling it for you. – Chris May 02 '19 at 18:55
  • It is container managed, I did have a beginTransaction call, got an error saying it could not used with JTA managed container. This inconsistent state might be what I was seeing. I wish I could include my updated code, you'd probably instantly see what's wrong. I'll try and summarize it: try {persist(); flush();} catch { ent = find(id); /* ent is always null */ } I don't understand why it's null, but the exception has a different primary key to the one I just persisted. Again I don't understand. I'm happy to be pointed at a best practices document, that deals with this scenerio. – silicontrip May 03 '19 at 02:41
  • It sounds like I need to create a singleton which abstracts the access to the JPA entity and put locking on the methods that create and update the entities. – silicontrip May 03 '19 at 04:29
  • Persistence exceptions close the EntityManager instance, it cannot be used afterward. I don't know what ID value you are using, but it doesn't have to be a primary key constraint that is causing the exception. If the insert fails, JPA doesn't know the state and the container pretty much kills the transaction on you. You should let the exception roll up and have retry behaviour in a layer above, which can try everything in a new transaction/EntityManager context a second time. – Chris May 03 '19 at 21:04

0 Answers0