“insert-or-update”: locking pitfalls with EJB3 +

The problem sounds like something very common: a server-side program in a multi-threaded environment has to save an entity. It must decide either to create a new instance or to load an existing instance and update it. I will show you some examples when using EJB3-entities (with Hibernate, tested with Oracle and Postgres).

We could call merge(): ["MyEntity" is the name for a business entity, just for the examples.]

Example 1:

MyEntity persistentEntity = entityManager.merge(detachedEntity);

or do it the explicit way, when some more business logic is required before we save:

Example 2:

  MyEntity persistentEntity = entityManager.find(MyEntity.class, detachedEntity.getId());
  boolean isNew = false;
  if(persistentEntity == null) {
    persistentEntity = new MyEntity();
    isNew = true;
  }
  // ... some business logic here that works with detachedEntity and persistentEntity
  if(isNew) {
    entityManager.persist(persistentEntity);
  } // otherwise update happens implicitly

But: This does not solve the concurrency problems we get when two transactions are doing the same thing at (nearly) the same time!

What could happen is, that both transactions try to load the object first where find() returns null, because the object is not stored yet. Thus both transactions will try to insert the entity, which would create the entity twice in the database or would cause one of the transactions to fail (e.g. because of a constraint violation).

You do not need much code to solve this situation, but be careful, there are some pitfalls!

There is even a concurrency problem when the entity is already in the database:

When both transactions change the entity concurrently, this will result in lost updates or an OptimisticConcurrencyException. – I don’t like any of them!

The program must load and lock the entity (select-for-update), so that each subsequent transaction waits on the lock until the first transaction releases the lock (when the transaction ends with commit or rollback).

The simplest thing is to call

entityManager.lock(persistentEntity, LockModeType.READ);

but:

• according to EJB3-specifiation, lock() is only supported for entities with a @version attribute!
  The @version attribute normally is used for optimistic concurrency and I got bad surprises calling lock(): sometimes I got a StaleObjectStateException instead of a locked object!
  (Hibernate also supports lock() for entities without a @version attribute.)

• when the persistentEntity has not been inserted, there is no object to be locked!
  Lock() requires that the object has been saved before.

Let me suggest a solution:

I created an entity named “LockEntry”, that allows me to implement global locks.

@Entity
public class LockEntry {
  private String id;
  private int version;

  @Id public String getId() { return id; }
  public void setId(String id) { this.id = id; }

  @Version public int getVersion() { return version; }
  public void setVersion(int version) { this.version = version; }
}

Each kind of lock gets its own name and is inserted in LockEntry’s table when the database is created.

insert into Lockentry (id, version) values ('MyEntity', 0);

The algorithms of example (1) and (2) just start with two additional statements:

LockEntry entry = entityManager.getReference(LockEntry.class, "MyEntity");
entityManager.lock(entry, LockModeType.READ);

This places a lock on LockEntity with id=”MyEntity” to ensure that the transaction will not cause conflicts or lost updates.

But you must notice one more thing:

When working with different entityManagers (e.g. when using multiple databases or database schemas), the LockEntry entity must be locked with the same entityManager than your business entity (MyEntity)! Otherwise you can get lost updates or concurrency conflicts, because during the commit of the transaction the lock on “LockEntry” must be released in the same database transaction, in which the changes on MyEntity become visible to other transactions. (I am normally not using a two-phase-commit transaction manager.)

Optimization:

To improve throughput of concurrent transactions, you can decide to use the global lock only when you need to create the new entity:

MyEntity persistentEntity = loadAndLock(detachedEntity.getId());
boolean isNew = false;

if(persistentEntity == null) {
  LockEntry entry = entityManager.getReference(LockEntry.class, "MyEntity");
  entityManager.lock(entry, LockModeType.READ);
  persistentEntity = loadAndLock(detachedEntity.getId());
  if(persistentEntity == null) {
    persistentEntity = new MyEntity();
    isNew = true
  }
}

// ...

Note that loadAndLock() is called twice – double checked locking is required here!

The reason for the loadAndLock() method is that because of Hibernate’s implementation of lock(), calling find() and lock() can result in a StaleObjectStateException:

MyEntiy loadAndLock(long id) {
  Query query = entityManager.createNativeQuery("select e.* from myentity e where id=?1 for update");
  query.setParameter(1, id);
  MyEntity found = null;
  try {
    found = query.getSingleResult();
  } catch(EntityNotFoundException e) {} catch(NoResultException e) {}
  // this might be neccessary, too:
  if(found != null) entityManager.refresh(found);
  return found;
}

Weird, isn’t it? Or do you have a better solution?