Understanding Oracle Lock Mechanisms: DML, DDL, and Row‑Level Locks Explained

Oracle Lock Types

Locks are mechanisms that protect shared resources from being modified concurrently. Oracle defines several lock categories:

DML locks : TX lock (row‑level) ensures only one session modifies a row, and TM lock prevents table structure changes while a transaction is active.

DDL locks : automatically acquired during DDL operations to protect objects from concurrent modifications.

Internal locks and latches : lightweight serialization devices for shared memory structures (latches are mentioned but not discussed further).

General Lock Manager Workflow

Locate the address of the row to be locked.

Enter the lock manager’s queue.

Access the lock list.

Search the list to see if another session already holds the lock.

Create a new entry in the list to indicate the row is now locked.

Release the lock list when the operation completes.

Oracle‑Specific Lock Management

Find the address of the row to be locked.

Navigate to that row.

Lock the row.

Three Core Lock Components

Oracle’s lock architecture consists of:

Resource Structure : Describes each lockable resource; contains three pointer fields—owner, waiter, and converter—that link to lock structures.

Lock Structure : Holds lock mode, session ID, and other metadata; when a session cannot obtain a lock immediately, its lock structure is attached to the resource’s waiter list.

Enqueue : The FIFO algorithm that orders lock requests; it determines whether a request becomes an owner, waiter, or converter.

Common Lock Modes

Share mode allows concurrent read‑only access, while Exclusive mode grants exclusive write access. The Enqueue algorithm implements a first‑in‑first‑out queue, moving lock structures between owner, waiter, and converter lists based on request satisfaction.

Row‑Level Lock Mechanism

Key concepts:

ITL (Interested Transaction List) : Each data block header contains an ITL structure that records which transactions are modifying the block.

Record‑Header ITL Index : A field in each row pointing to its ITL entry.

TX lock : Transaction lock protecting rollback segment resources.

TM lock : Table lock preventing DDL changes during DML operations.

When a transaction starts, it must acquire a TX lock, which implicitly obtains a rollback segment. The typical modification sequence is:

Acquire the table’s TM lock.

Allocate a free ITL entry in the block header and record the transaction ID.

Set the row’s ITL index to the allocated entry, copy the original row image to the rollback segment, then modify the row.

Other sessions reading the same row consult the ITL entry; if the owning transaction has not committed, they wait on the lock.

Example Block Header Dump

Block header dump: 0x00411819
Object id on Block? Y
seg/obj: 0x10396 csc: 0x00.d62e7 itc: 2 flg: O typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01

The dump lists fields such as ITL slots, flags, and block type, helping diagnose lock status.

Transaction Commit Process

Generate a System Change Number (SCN) for the transaction.

LGWR writes remaining redo log entries to disk and records the SCN.

Locks recorded in v$lock are released, and all waiting queues are awakened.

If modified blocks remain in cache, they are quickly accessed and cleared.

Concurrency Model and Deadlock Causes

Oracle implements Multi‑Version Concurrency Control (MVCC), allowing readers to see a consistent snapshot without being blocked by writers, except during distributed (2PC) transactions. Most DDL statements acquire exclusive locks; some DDLs (e.g., CREATE INDEX … ONLINE) only obtain a TM lock.

Typical deadlock sources include foreign keys without indexes, bitmap index updates, and cascading deletes that lock parent and child tables.

Practical Example: Detecting Blocking Sessions

Two sessions illustrate blocking:

-- Session 1
CREATE TABLE p (x INT PRIMARY KEY);
CREATE TABLE c (x REFERENCES p);
INSERT INTO p VALUES (1);
INSERT INTO p VALUES (2);
COMMIT;
INSERT INTO c VALUES (2);

-- Session 2
DELETE FROM p WHERE x = 1;

Querying v$lock reveals the blocker and blockee:

SELECT (SELECT username FROM v$session WHERE sid=a.sid) blocker,
       a.sid,
       'is blocking',
       (SELECT username FROM v$session WHERE sid=b.sid) blockee,
       b.sid
FROM   v$lock a, v$lock b
WHERE  a.block = 1
AND    b.request > 0
AND    a.id1 = b.id1
AND    a.id2 = b.id2;

The result shows the session holding the lock (blocker) and the waiting session (blockee).

When Foreign‑Key Indexes Are Not Required

Indexes on foreign keys can be omitted safely if:

No rows are deleted from the parent table.

No updates modify the parent’s primary/unique key.

No joins are performed between the parent and child tables.