How to prevent concurrency issue in UPDATE via iBatis

Question

Our Java EE web application performs database operations using iBatis (ORM). The database operation flow is as below

Flow : JSP --->Action--->ServiceIMpl--->DaoImpl---->Calling update query thru' IBatis

Note: The Action, Service & DAO classes are instantiated using Spring 2.5's Dependency Injection technique. We use Struts2.

Problem: 2 concurrent users search for the same record and User1 updates Attribute1 while User2 updates Attribute2. User1 first clicks on 'Save' and then User2 clicks on save (they follow each other with a difference of few seconds). When we see the data in database, only the User1's update is present. The Audit columns also show only the User1's update. The User2's update on Attribute2 is not done , at the sametime, no Exceptions are thrown.

We tried using the begin transaction and commit transaction in iBatis surrounding the invokation of the update query defined in the sqlmap xml. The same issue persists. We also tried removing those transaction commands, yet the problem persists.

Should we do anything special/different to handle concurrency during updates ? Wouldn't ORMs like iBatis handle by themselves ?

Additional Info: Further investigation revealed the following information.

When User1 & User2 clicks on the a record, it's complete data is fetched for viewing.

Now when both User1 & User2 changes few attributes and click save (concurrently), say first User1's data is updated and then while User2's data is being updated, the already updated data of User1 is over-written & lost.

What approach is usually followed in such screens to handle such scenarios ?

Answer 1

Your problem is not with transactions, it is that before you modify something you must be sure that no one has modified since you last read it.

In web applications you should use optimistic locking. Basically, you have a "version" field in your table, that has an initial value at insert (tipically 1), and gets incremented with each update. The procedure is:

1. Read data from the table, including the version field

2. Send data the client side, including the version field

3. Receive modified data from the client, including the version field unchanged

4. Update the table and increment the version, but only if the version field in the row is the same as the original one received from the client. That is, where you had

   update
   ... 
   where id = :id_from_client
   

   , now you should do

   update 
   ..., 
   version = version + 1 
   where 
   id = :id_from_client and 
   version = :version_from_client
   

5. Get number of updated rows (you can usually get this information right from the update operation of your persistence framework). If number of updated rows is 1, the operation succeded. If number of updated rows is 0, you should signal a concurrency error.

An example:

1. User1 get the data, with version = 17

2. User2 get the data, with version = 17

3. User1 atomically (atomicity comes from being a single SQL statement):

   • Check if version = 17
   • Updates the data
   • Sets version to 18

4. User2 atomically:

   • Check if version = 17
   • As version was really 18, signals a concurrency error and aborts the operation, informing the user that the operation could not be completed due to somebody else modifying the same data.

Most current persistence frameworks have support for this out of the box and mostly automatically (see JPA @Version annotation, for example). iBatis seems not to have it, but as it is very close to SQL, you should not have many problems implementing it yourself. See this blog entry for some details about optimistic locking with iBatis.

With this scheme, you will not get lost updates, the only problem is that some specific cases will annoy the users a lot: imagine you spend 5 minutes completing some form only to be told at the very end that your operation could not be completed and you need to retry from the start!! In these few cases, you should implement something more sophisticated on top of optimistic locking. I would use some sort of remote resource leasing (links??):

1. The client requests a lease for the data to the server.

2. If the data is already leased to another client, signal a concurrency error.

3. Else grant the lease to the client for a finite amount of time.

4. Before lease expiration, the server grants the client exclusive access to the data. The client can also request a lease renewal (via AJAX, for example). The server can accept or deny the renewal. (Lease times and renewal policies should be decided by the server depending on the specific use case that is being dealt with).

5. The lease expires (this can be done with a scheduled task at the server, for example). When the lease expires, the data should be considered "unlocked", and the server should deny access to the client unless the client is granted a new lease.

This way, your users can be informed that someone is modifying the same data before they even start whatever lengthy operation they were trying to complete.

Answer 2

Ibatis does not perform locking .Seeing yours scenario optimistic locking can solve the problem.To achieve optimistic locking you can have options. 1.Use framework that support for transaction management like org.springframework.jdbc.datasource. DataSourceTransactionManager.They provide propagation behaviour and isolation level.But,the datasource you are using must support propagation level and isolation level.Spring provides following isolation level. .ISOLATION_DEFAULT .ISOLATION_READ_UNCOMMITTED .ISOLATION_READ_COMMITTED .ISOLATION_REPEATABLE_READ .ISOLATION_SERIALIZABLE. 2.Next you can provide yours implementation of optimistic locking.