Differences between Pessimistic and Optimistic Locking

Locking is an RDBMS feature that prevents users from different transactions from causing data conflicts. When locking is acquired on a row, it prevents other transactions from changing that row until the transaction ends. In this post I will explain the differences between pessimistic and optimistic locking in the context of ADF framework.

pessimistic locking:

Most Oracle developers are already familiar with pessimistic locking, which was the default locking in BC4J (now optimistic is the  default in 11.1.2.x)). This means that the row is locked in advance once one of  its attribute is changed through a call to setAttribute() method. If anyone else attempts to acquire a lock of the same row during this process, he will be forced to wait until the first transaction has completed. This is achieved by using the familiar SELECT…FOR UPDATE syntax. This is the safest locking mode because two transactions will never make inconsistent change to the same row. However, this locking mode has disadvantages such that:

1. If a user selects a record for update, and then leaves for lunch without
   finishing or aborting the transaction. All other users that need to update that record are forced to wait until the user returns and completes the transaction, or until the DBA kills the offending transaction and releases the lock.
2. The Deadlock – Users A and B are both updating the database at the same time. User A locks a record and then attempt to acquire a lock held by user B – who is waiting to obtain a lock held by user A.

Pessimistic locking, which is the default, should not be used for web applications as it creates pending transactional state in the database in the form of row-level locks. If pessimistic locking is set, state management will work, but the locking mode will not perform as expected. Behind the scenes, every time an application module is recycled, a rollback is issued in the JDBC connection. This releases all the locks that pessimistic locking had created.

An example about pessimistic locking based on well known hr schema, suppose user1 and user2 are two different users (two distinct transactions) using pessimistic locking, both of them try to change the same row of data as follows:

1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, so user1 immediately acquire a lock on that row.
2. Now user2 calls EmployeesImpl.setSalary(2000) on the same row, user2 tries to acquire a lock on the row and receivesoracle.jbo.AlreadyLockedException.

Optimistic Locking:

Optimistic locking assumes that multiple transactions can complete without affecting each other. Oracle recommends using optimistic locking for web applications. instead of locking a row as soon as it is changed, under optimistic locking, BC4J waits until changed row is posted before attempting to acquire a lock. An exception is not thrown until the conflicting transactions attempt to post their changes to the database.

An example about optimistic locking, suppose user1 and user2 are two different users (two distinct transactions) using optimistic locking, both of them try to change the same row of data as follows:

1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, user1 does not immediately acquire a lock on that row.
2. User2 calls EmployeesImpl.setSalary(2000) on  the same row. User1 and User2 now have different entity cache for the same row.
3. User2 calls commit() action, as part of the commit cycle the changed row is posted to the database. before the update can be executed, user2 acquires a lock on that row. The lock expires immediately, when the commit command is sent to the database.
4. User1 now calls commit() action, BC4J tries to post the changed row to the database, right before posting it, it attempts to acquire a lock on that row. BC4J recognizes that the row has been changed by another user and that updating the row would overwrite another transaction’s changes, so it throws an oracle.jbo.RowInconsistentException.

Finally, Whatever you use, you can lock a row at any time by calling EntityImpl.lock() on the corresponding entity object instance, even if the locking mode is optimistic.

References:

[1] Oracle 9i Jdeveloper handbook, Peter Koletzeke, Dr. Paul Dorsey, Dr. Avrom Faderman.

[2] Fusion Developer’s Guide for Oracle Application Development Framework
11g Release 1 (11.1.1).

[3] http://www.orafaq.com/papers/locking.pdf