Recently trying to implement pessimistic locks with the Java Persistence API and Eclipse-Link turned out to be not so straightforward…

Firstly the official JPA specification doesn’t support pessimistic locking, instead only optimistic. The diffrence being that pessimistic will lock clients out from records completly (at least with write locks) and optimistic locks rely on versionining to check if a record/object has changed since it was read before writing and then flags an error if it has.

In my current appliucation I’m writing for my dissertation I need pessimistic locks this is to ensure (obviously) that no two users can edit the same record at the same time.

The solution to this was to use EclipseLink, though ensuring that the underlying database also supported the ability to place locks on records. MySQL with the innodb engine does support this. However as EclipseLink originated from TopLink originally written by Oracle it is no surprise that it is oriented towards the Oracle DB rather then MySQL or any other DB.

The only problem this really causes is that when using the EcliseLink PessimisticLock options with MySQL ensure that you only use the “PessimisticLock.Lock” option and not “PessimisticLock.LockNoWait”. The problem is that MySQL does not support the SELECT …. FOR UPDATE NOWAIT feature as Oracale does but only standard SELECT …. FOR UPDATE.

To get around this additionally set your innodb_lock_wait_timeout=0 in my.cnf that wait time to attain locks is minimal. If locks  are held on the required object then eventually (after a short period of time) a DatabaseException will be thrown this can in turn be caught to alert the user to the problem.

The next problem then comes in allowing rollbacks, again JPA does not support rollbacks and neither does EclipseLink in this case within the context of JPA or nativelly using DBMS  feature (though EclipseLink run nativelly does have UnitOfWork).

To get around these problems see the following code snippet..

EntityManager em;
…

public Savepoint getSavepoint() throws SQLException {
em.flush();
//Get the SQL connection
Connection conn = ((UnitOfWorkImpl) ((JpaEntityManager) em).getActiveSession().acquireUnitOfWork()).getAccessor().getConnection();

//Get a savepoint from the SQL connection
return conn.setSavepoint();

}

next to make use of it…
public void commitToSave1(Savepoint sp) throws SQLException {
Connection conn = ((UnitOfWorkImpl) ((JpaEntityManager) em).getActiveSession().acquireUnitOfWork()).getAccessor().getConnection();
//roll the connection back to the savepoint
conn.rollback(sp);
//commit the current state of the transaction
conn.commit();
//clear the persistence context causing all managed entities to
//become detached
em.clear();
//commit the transaction to release locks and reset the entity
//manager
em.getTransaction().commit();
}

That concludes my brief explanation of using JPA with pessimistic locks and rollbacks for the moment for anyone who would like any more information please dont hesitate to get in touch..

Also see my original post regarding this over at http://www.nabble.com/Savepoints%2C-Checkpoints%2C-Rollbacks–td21980310.html