Optimistic Concurrency Checking

Optimistic Concurrency Checking is also referred to as Optimistic Locking and Optimistic Concurrency Control. Note that in the EJB3 JPA spec the term used is Optimistic Locking but it means the same thing.

The point of Optimistic Concurrency Checking is to prevent "Lost Updates". A lost update is where two users update the same data with one of the updates overwriting the other. The effect is as if one of the updates never occured at all - it was lost.

The benefit of Optimistic Concurrency Checking over Pessimistic Locking is that Optimistic Concurrency Checking does not require Database locks. Pessimistic Locking will use Database locks and this generally will reduce the concurrency the database can support.

Only works in READ_COMMITTED Transaction Isolation

It is important to note that Optimistic Concurrency Checking only works in READ_COMMITED Transaction Isolation Level. Specifically, at higher Isolation levels such as SERIALIZABLE the UPDATE or DELETE will see the database as at the Transaction start time. In the time gap between Transaction start time and the UPDATE/DELETE statement there could be commited changes that will be lost (Lost Updates).

It is expected for both EJB3 JPA (Section 3.4) and Ebean that you will mostly run in READ_COMMITTED Isolation level due to this. That is, Ebean uses READ_COMMITTED as the default Transaction Isolation Level on its connection pools.

2 Options - Version and ALL

There are 2 options generally used for performing Optimistic Concurrency Checking with a database.

• Version Column
• All Columns

Version Column

In this case there is a column on the table that is nominated as the "Version" column. This matches the JPA @Version annotation. This is typically either a Counter, a Timestamp or a DBMS specific column such as Oracle "ora_rowscn" or MSSQLServer "RowVersion/Timestamp".

In performing the Optimistic Concurrency checking with a Version Column the UPDATE or DELETE statement has a WHERE clause that will include the primary key and the Version Column.

update b_product set name='Apple', sort_order=4, updtime=*NOW* 
where id=1 and updtime=*PREVIOUS updtime* 

The number of rows this effected is checked and if this was 0 then a OptimisticLockException is thrown. It is implied that the value of updtime has been changed by someone else since we fetched the data.

If the number of rows effected was 1 then the update was deemed successful.

Note: Because the primary key is part of the WHERE clause we should safely expect either 0 or 1 rows updated. It is assumed the primary key does not change.

Note: Users are not allowed to change the values of Version columns themselves.

All Column

In ALL Column mode the WHERE clause includes the Primary Key and All the columns. The values bound in the WHERE clause are the 'Old values' or 'Original values' and the values bound in the SET are the 'New values'.

In this example the value of name and sort_order was originally 'Banana' and '3' and these where changed to 'Apple' and '4'.

update b_product set name='Apple', sort_order=4      //New values 
where id=1 and name='Banana' and sort_order=3    //Old values 

The number of rows this effected is checked and if this was 0 then a OptimisticLockException is thrown. In this example this would imply someone has changed the name and or sort_order for this row.

Note that Ebean does not include Clob or Blob columns in ALL Column mode as this could be potentially very expensive.

NOTE: Code Simulating OldValues in Ebean

I have someCode that simulates how OldValues works in Ebean in the hope it sheds some light on this subject.

NOTE: int rows = PreparedStatement.executeUpdate()

We know the number of rows updated deleted by the value returned by PreparedStatement.executeUpdate().

NOTE: Hashing as another option

I also believe there is a way of using a Hash function and Stored Procedures to perform Optimistic Concurrency Checking.

Where are the 'Old Values' kept?

Ebean:

With Ebean the 'Old Values' are kept with the bean itself. The beans returned from a query are actually Byte Code Generated Subclasses. The 'setter' methods are intercepted to detect when a bean is being changed and when this occurs the current values are stored as 'Old Values'.

When the bean is saved and this becomes a UPDATE statement with ALL column concurrency the bean has the 'New/Current values' plus it also has its 'Old/Original values'. Technically the 'Old Values' is kept in the com.avaje.ebean.bean.EntityBeanIntercept object.

Here is some Code Simulating OldValues in Ebean

JPA:

With JPA it is implied that the 'Old Values' are kept/held by EntityManager. This results in the requirement for Session Management of EntityManager (Extended Persistence Context) so that the 'Old Values' are available for Optimistic Concurrency Checking. Refer to EJB Architecture