An Overview of Locking Mechanisms in Oracle
Locking: This is a key area in Oracle internals, and it often confuses DBAs because latches and
locks both sound like mechanisms for serialization, but they’re quite different in purpose and
implementation.
Oracle uses a sophisticated locking mechanism to ensure data consistency and concurrency,
primarily employing row-level locking for Data Manipulation Language (DML) operations and
automatically managing table locks for Data Definition Language (DDL) operations.
Locks come in two main modes: Share (S) locks for reading data (allowing multiple readers) and
Exclusive (X) locks for writing data (preventing any other access).
These locks are automatically acquired and released by Oracle for the duration of a transaction,
preventing conflicts when multiple users access the same data simultaneously.
Key Aspects of Oracle's Locking Mechanism
● Row-Level Locking:
Oracle's default strategy is to place locks on individual rows, not entire pages or tables. This
maximizes concurrent access because it only locks the specific data being changed, not the
whole table or larger sections of it.
● Automatic Management:
Oracle automatically manages the acquisition and release of locks for transactions,
eliminating the need for developers to intervene in most cases.
● Transaction Duration:
Locks are held for the entire duration of a transaction, from the moment a modification begins
until the transaction is committed or rolled back.
● Types of Locks:
○ DML Locks: Used to protect data during operations like INSERT, UPDATE, and DELETE.
○
These include row locks and the table locks that are automatically acquired to prevent
DDL on a table being modified.
○ DDL Locks: Protect the structure of schema objects (like tables and views) from
conflicting DDL operations.
○ Internal Locks and Latches: Protect internal database structures, such as datafiles.
What is a Latch in Oracle?
A Latch is a low-level serialization mechanism in Oracle, implemented in memory (SGA), to protect
shared data structures in the Oracle kernel.
Think of a latch as a lightweight spinlock.
Example: when Oracle needs to modify a structure like the library cache, buffer cache, or row
cache, it acquires a latch to ensure only one process changes it at a time.
Characteristics:
Very short duration (microseconds to milliseconds).
Not transaction-related — they are for in-memory consistency, not data consistency on disk.
Typically acquired and released very quickly.
If a process can’t get a latch, it will either spin (retry in a loop) or sleep briefly, depending on the
latch type.
What are TX and TM Locks?
These are enqueue locks (Oracle term for higher-level locks), related to user transactions and
table access.
TX Lock (Transaction Lock)
Taken at the row level to protect rows modified by a transaction.
Ensures that other sessions cannot modify the same row until the transaction commits or rolls back.
Example: if Session A updates a row, it holds a TX lock. Session B trying to update that row will
block.
TM Lock (DML Enqueue Lock / Table Lock)
Taken at the table level when performing DML (INSERT, UPDATE, DELETE).
Ensures data definition operations (like ALTER TABLE) don’t interfere with ongoing DML.
Multiple sessions can hold TM locks in ROW EXCLUSIVE mode at the same time.
Characteristics:
Held for the duration of a transaction (not just milliseconds like latches).
Related to data consistency and concurrency control.
If unavailable, a session waits (blocks), unlike latches which mostly spin.
Key Differences Between Latch vs TX/TM Locks
Analogy:
Latch = a mutex in memory → like grabbing a pen on a shared desk to write for a few milliseconds.
TX/TM Locks = database-level locks → like reserving a whole document (table/row) until you’re
done editing.
