Oracle Database - Row Locks (TX)

Card Puncher Data Processing

About

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT … FOR UPDATE statement. The row lock exists until the transaction commits or rolls back.

Row locks primarily serve as a queuing mechanism to prevent two transactions from modifying the same row. The database always locks a modified row in exclusive mode so that other transactions cannot modify the row until the transaction holding the lock commits or rolls back. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.

If a transaction terminates because of database instance failure, then block-level recovery makes a row available before the entire transaction is recovered.

If a transaction obtains a lock for a row, then the transaction also acquires a lock for the table containing the row. The table lock prevents conflicting DDL operations that would override data changes in a current transaction. The figure below illustrates an update of a row in a table. Oracle Database automatically places an exclusive lock on the updated row and a subexclusive lock on the table.

Oracle Database Row Lock Tx

Storage of Row Locks

Unlike some databases, which use a lock manager to maintain a list of locks in memory, Oracle Database stores lock information in the data block that contains the locked row.

The database uses a queuing mechanism for acquisition of row locks. If a transaction requires a lock for an unlocked row, then the transaction places a lock in the data block. Each row modified by this transaction points to a copy of the transaction ID stored in the block header.

When a transaction ends, the transaction ID remains in the block header. If a different transaction wants to modify a row, then it uses the transaction ID to determine whether the lock is active. If the lock is active, then the session asks to be notified when the lock is released. Otherwise, the transaction acquires the lock.





Discover More
Data System Architecture
Concurrency - Latches (System Lock)

Latches are like semaphores. Latches are used to guarantee physical consistency of data, while locks are used to assure logical consistency of data. Latches are simple, low-level system lock (serialization...
Card Puncher Data Processing
Oracle Database - DML Lock

A DML lock, also called a data lock, guarantees the integrity of data accessed concurrently by multiple users. For example, a DML lock prevents two customers from buying the last copy of a book available...



Share this page:
Follow us:
Task Runner