Optimistic Locking:
Optimistic locking ensure the locks are held between selecting, updating, or deleting rows. This process needs a way to ensure that the changes to data are not performed between the time of being read and being altered. The primary advantage of optimistic locking is , it minimizes the time for which a given resource is unavailable which is used by another transaction and in this way it more scalable locking alternative.
Pessimistic Locking:
The pessimistic locking strategy assumes the probability of modifying a row that is used by one user can be used by another user for modifying. In this scenario, a lock is held between the row selection time and the updation time for changing operations on that row. The pessimistic locking strategy guarantees the changes are made safely and consistently.
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.
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.