Oracle Internals: An Introduction

Chapter 4. Locks

Oracle uses latches to protect data structures that are accessed briefly and intermittently. However, latches are not suitable for protecting resources that may be needed for a relatively long time, such as database tables. In such cases, a lock must be used instead. Locks allow sessions to join a queue for a resource that is not immediately available. This avoids spinning. Locks also allow multiple sessions to share a resource if their activities are compatible.

Lock Usage

Oracle uses locks for many different purposes. The following are the most important ones to understand for performance tuning.

Transaction Locks and Row-Level Locks

Oracle’s much vaunted row-level locks are subtle. When a transaction modifies a row, its transaction identifier is recorded in an entry in the interested transaction list (ITL) in the header of the data block itself, and the row header is modified to point to that ITL entry. Once these changes have been made, no lock is retained. The ITL entry for the uncommitted transaction, together with the row header that references it, constitutes an implicit lock on the row.

When another transaction wants to modify the same row, and sees that an uncommitted transaction has modified that row, that transaction waits, not on a row-level lock, but on the transaction lock for the blocking transaction.

When the blocking transaction commits or rolls back, its transaction lock will be released. Its implicit row-level locks are thereby released, and so the ...