LOCKING IN ORACLE

Two types of Locking


In Optimistic locking the row will not be locked until the actual update is performed. In order to prevent the lost update issue a version number column will be used.

In Pessimistic locking the row will be locked at the time when the data is selected from the database. This will prevent other users who want to update the same record. This should not prevent the readers but this behaviour varies from database to database.


Optimistic : One who usually expects a favorable outcome
Pessimistic : Expecting the worst possible outcome (negative)


The Oracle database uses Optimistic locking by default. Any command that begins with UPDATE SET that is not preceded by a SELECT FOR UPDATE is an example of optimistic locking.

SELECT ..... FOR UPDATE ,
SET TRANSACTION ISOLATION LEVEL,
LOCK TABLE PROJECT_MASTER IN ROW EXCLUSIVE MODE
are examples of Pessimistic locking

Pessimistic has two major problems

The Lockout - An application 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

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. Both transactions go into an infinite wait state - the so-called deadly embrace or deadlock.