As mentioned in the article Transaction Concurrentcy(1), most application choose read committed as the isolation level. The reason is that no matter whether there is concurrency occuring, serializable and repeatable read need to lock the rows in the database, which cause the reduction in the system performance, especially for these system where concurrency doesn’t happen frequently. However, Read committed cannot avert unrepeatable read and Second lost updates problem, therefore, we need to use optimistic locking or pessimistic locking to make up!
Today, I am going to make a brief introduction to optimistic locking. Optimistic locking is based on the assumption that most database transaction don’t conflict with other transactions. In fact, optimistic locking doesn’t actually lock anything. Instead, when a transaction updates a row it verifies that the row has not been changed or deleted by a different transaction since it was read. If it has, the transaction is typically rolled back and retried.
Usually, there are three methods to realize optimistic locking. The first one is adding a version column to the tables in the database. The second one is adding a timestamp column, and the third one is to compare current values of the columns with their previously read values. Commonly, we prefer the vrsion column to the other two methods, since the third method makes the sql sentences too complicated and the second method is not suitable for any case.
There is a sql sentence using version column:
Update ACTIVITIES Set PLACE = ‘?’ WHERE ACTIVITY_ID=? AND VERSION = ?;
The following is an simple example to show how to configure optimistic locking realized by version column. It is pretty easy!
<class name=”**” table=”**”>
<id name=”*” column=”*”>
<generator class=”*” />
<version name=”version” column=”VERSION” />