Introduction

Databases serve multiple concurrent users. When several transactions access the same data simultaneously, controlling concurrency becomes essential to prevent data corruption and maintain consistency. Locks are the fundamental mechanism Oracle uses to enforce data integrity and isolate transactions.

Unlike some RDBMS that maintain a separate lock list, Oracle implements locks as attributes of data blocks. Each data block stores an Interested Transaction List (ITL) that records active transactions modifying data within that block. This lightweight design allows locks to be checked directly from the block header without external lock structures.

Lock Categories

By Lock Mode: Shared vs. Exclusive

• Shared Lock (S Lock): Allows multiple transactions to read the same resource concurrently. It prevents modifications but permits other shared locks. Inefficient transaction design with shared locks can lead to deadlocks or lost updates.
• Exclusive Lock (X Lock): Grants sole access to a resource. No other transaction can acquire any lock (shared or exclusive) on that object until the lock is released.

By Origin: Automatic vs. Manual

• Automatic Locks: Oracle automatically acquires necessary locks for DML, DDL, and system operations without user intervention.
• Manual Locks: Issued explicitly by the user via SQL statements (e.g., LOCK TABLE) to achieve finer control.

By Operation Type: DML, DDL, and System Locks

DML Locks ensure data integrity during data manipulation. Oracle automatically obtains two types of DML locks:

• TM Lock (Table-Level Lock): Protects table structure from being modified (e.g., dropped) while DML statements are executing. TM locks come in various modes: SS (sub-share), SX (sub-exclusive), S (share), X (exclusive), etc., represented by values 0–6.
• TX Lock (Transaction Lock or Row-Level Lock): Each transaction acquires a TX lock upon its first DML statement and holds it until commit or rollback. Row lock are implemented by setting a flag in the row header to the ITL entry number. When multiple sessions try to modify the same row, the first session locks it; others wait until the TX lock is released.

The lock negotiation process works as follows: When executing a DML, Oracle first acquires a TM lock on the table. Once the TM lock is granted, it acquires a TX lock and sets the row-level lock flags. This two-phase approach avoids scenning every row’s lock flag when checking lock compatibility.

TX Lock Details

To modify a data block, a transaction needs an ITL entry. The row header contains a lock byte; if it is zero (unlocked), the transaction writes its ITL index into that byte, effectively locking the row. The ITL links to the undo segment header’s transaction table, which indicates whether the transaction is active.

Manual Locking: LOCK TABLE Examples

Users can manually acquire table-level locks using the LOCK TABLE statement. Example:

LOCK TABLE employees, departments IN SHARE MODE NOWAIT;

This attempts to place a shared lock on the listed tables. If NOWAIT is specified and the lock cannot be obtained immediately, the statement returns an error. Shared locks are released upon COMMIT, ROLLBACK, logout, or program termination.

Shared table locks are typically used in consistent read scenarios where no data changes are alllowed during a query.