Row Level Locking Mechanism

Oracle Row Level Locking Mechanism

Description

Oracle is the first database implements complete row level locking mechanism.

Oracle ITL of block header (both cached and datafile stored), rollback segment header, rollback segment entry implement sophisticated transaction, read consistent, row level lock mechanism at the same time. So in real heavy multi-transaction business environment, Oracle is faster than other common databases.

Scott Martin (MS MIT) has an excellent description of Oracle row-level locks:

When a transaction begins, it opens an exclusive lock on a resource named after the transaction ID of the newly started transaction. This resource will then serve as a wait point for any other transaction which needs to wait for the completion of the newly started transaction.

In our example, any transaction that wishes to update row #2 would detect that the row is still locked by transaction ID 0005.00e.0000013a. This concurrent transaction would then request a lock in exclusive mode on the resource named after the as yet uncommitted transaction. When the first transaction commits (or rollbacks), it releases the exclusive lock. Once this lock is released the second transaction's request for the lock succeeds allowing it to proceed.

Mark Bobak published this excellent description of Oracle row-level locking:

Transaction begins: Either explicitly via set transaction ... or implicitly, due to any
DML (select for update, update, insert, or delete)

Slot is allocated in rollback segment header: This is either chosen round-robin,
or, if specified, taken from 'set transaction use rollback segment ...' statement.

Statement is executed, blocks to be changed are identified: This is done via the
execution plan, utilizing whatever access paths are specified there. As each block
is identified rows are taken as follows:

Now, transaction looks for an empty ITL slot, and allocates it. If no slots are
available, one will be allocated from free space in the block, if available, and
not limited by MAXTRANS. If that fails, transaction will wait on a TX enqueue in
mode 4. When the ITL slot has been allocated it will be set to point to the
rollback segment header that was previously reserved by this transaction.

Once an ITL slot has been allocated in the block, specific rows must be marked
as locked. This is done in the row directory. The lock byte will be set to point to
ITL slot of this transaction. This is how an actual row-level lock is maintained
by Oracle. Once that's done, the row may be updated, (or not, if it's just a select
for update). If changes to the block are done, the before images are recorded in
the rollback segment where the header slot has already been reserved.

Finally, a commit or rollback will release the lock. Note that in the case of commit,
the only thing that must happen to release the lock is the rollback segment header
slot is marked as committed. Cleanup of the block itself can, any in many cases will,
be delayed to a later date.

Reference

Oracle row locks and row level locking by Burleson Consulting