Oracle row locks and row level locking By Burleson Consulting

Scott Martin has an excellent description of Oracle row level locking:

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:

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

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

3. 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:

4. 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.

5. 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.

6. 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.