5. Oracle Locks, types, mechanism
Oracle employs an advanced locking mechanism and a Multiversion Concurrency Control (MVCC) system to ensure data integrity and high concurrency without sacrificing performance.
- Row-Level Locking:
— Oracle uses row-level locking, which means that locks are placed on individual rows rather than larger data structures like pages or tables. Unlike some other databases, Oracle does not implement lock escalation, where smaller granular locks (like row locks) are escalated to higher-level locks (like table locks). This ensures that concurrent access is maximized without unnecessarily restricting access to larger portions of data.
— Locks are automatically managed by Oracle. When a transaction modifies a row, Oracle places an exclusive lock on that row to prevent other transactions from changing it until the current transaction either commits or rolls back.
— Information about the lock is stored within the data block itself, eliminating the need for an external lock table and reducing overhead. These locks are held for the duration of the transaction, ensuring data consistency until the transaction’s final state is determined.
2. Multiversion Concurrency Control (MVCC):
— Oracle’s MVCC is designed to provide read consistency without blocking writers. It allows users to access a consistent snapshot of the database, even while other transactions are making changes to the same data. This is achieved using a timestamp-based approach, where each query sees data as it existed at a single point in time.
— When a row is modified, Oracle doesn’t overwrite the original data directly. Instead, it writes the original (pre-modified) data to an undo record in the Undo Tablespace. The undo record contains information that allows Oracle to reconstruct the previous version of the row as it appeared before the modification.
— For read consistency, queries can access this undo data. This ensures that readers do not see uncommitted changes from other transactions and are only exposed to a consistent view of the data from the time the query began. This mechanism prevents dirty reads and guarantees transaction isolation.
— MVCC effectively allows readers and writers to operate concurrently, as readers are never blocked by writers, and writers are never blocked by readers. Readers can view a consistent snapshot of the data without waiting for the transactions that modify it to commit or roll back.
3. Undo Management and Read Consistency:
— The Undo Tablespace plays a critical role in Oracle’s MVCC system. It stores the undo records that represent the original state of the data before modifications were made. These undo records are crucial for both rollback operations (in case a transaction fails or is rolled back) and providing read consistency.
— When a query accesses data that has been modified but not yet committed, Oracle uses the undo records to reconstruct the data as it appeared at the start of the query. This is done by reading the undo log entries and creating a consistent snapshot of the data.
— Oracle’s MVCC system ensures that each query sees a snapshot of the database as it existed at a specific point in time, which eliminates the risk of seeing inconsistent or partially modified data.
### 4. Lock Durations and Transaction Lifecycle:
— The row-level locks are held for the entire duration of the transaction, meaning they are only released when the transaction is either committed (making the changes permanent) or rolled back (undoing the changes). This ensures that no other transaction can modify the locked rows until the original transaction has completed, maintaining data integrity.
— Once a transaction commits, the log writer (LGWR) process ensures that the changes are flushed from the redo log buffer to the online redo log files, and the Database Writer (DBWn) process eventually writes the modified data to the physical data files. At this point, the locks are released, and other transactions can now access and modify the previously locked rows.
Oracle’s combination of row-level locking and multiversion concurrency control allows for highly efficient, concurrent data access while maintaining consistency and preventing conflicts, ensuring optimal performance in high-transaction environments.
TYPES OF LOCKS:
Different types of Locks are shown in below image:
Here are some views which can be used to check the locks:
DML Locks:
In Oracle, there are two primary types of DML locks: TX (Transaction Lock) and TM (Table Lock).
1. TX (Transaction Lock):
- A TX lock is acquired when a transaction makes its first change (e.g., INSERT, UPDATE, DELETE). This lock is held until the transaction either commits or rolls back.
- It ensures transaction isolation and acts as a queuing mechanism, meaning other sessions must wait for the current transaction to complete before they can modify the same data.
2. TM (Table Lock):
- A TM lock is placed on a table when a DML operation is performed to ensure that the structure of the table (such as schema or constraints) is not altered while the transaction is modifying the data.
- This lock prevents structural changes, like adding or dropping columns, while data is being modified, ensuring data integrity during concurrent operations.
DDL Locks:
Oracle automatically places **DDL locks** on objects during a DDL operation to prevent other sessions from altering those objects. There are three main types of DDL locks:
1. Exclusive DDL Locks:
— **Exclusive DDL locks** prevent other sessions from acquiring any DDL or **TM (table modification)** locks on the same object. While this lock is in place, other sessions can **query** the object but cannot **modify** it.
— Typically, the object remains locked until the DDL operation completes. However, in certain cases, the **ONLINE** option can be used, which places a low-level lock, allowing DML operations (e.g., INSERT, UPDATE) to continue while still blocking other DDL changes.
2. Share DDL Locks:
— **Share DDL locks** protect the structure of an object (e.g., a table) from modifications by other sessions, while still allowing changes to its **data**.
— This means users can modify the table’s contents but are restricted from altering its schema (e.g., adding columns or changing constraints) until the DDL operation completes.
3. Breakable Parse Locks:
— **Breakable parse locks** register dependencies between cached SQL statements and the objects they reference (e.g., tables, views). If a DDL operation modifies or drops a referenced object, Oracle invalidates (or flushes) the cached query plans that rely on that object.
— These locks are “breakable” because they don’t prevent the DDL from executing; instead, they ensure that any dependent cached statements are recompiled when the object structure changes.
— Parse locks are created when a session parses a SQL statement and are maintained to ensure consistency with the objects referenced. You can use specific SQL queries to track any parse locks on objects like views, procedures, and grants.
Latches & Enqueue:
Latches and Enqueues are two types of locking mechanisms used by Oracle to protect different resources within the system, primarily for memory structures and database objects.
Latches:
- Latches are lightweight, low-level serialization mechanisms designed to protect in-memory data structures within the System Global Area (SGA). They are held for very short durations, typically just long enough to perform operations on memory structures.
- Latches are commonly used to safeguard areas like the buffer cache (where database blocks are stored) and the library cache (where parsed SQL statements and PL/SQL code are stored).
- Since latches are fast and designed for short-term protection, they do not support queuing. If a process fails to obtain a latch, it may retry until it succeeds. However, this means that contention for latches can degrade performance in highly concurrent systems.
- Importantly, latches do not protect database objects like tables or data files — they are solely focused on protecting critical in-memory operations.
Enqueues:
- Enqueues are more advanced locking mechanisms used for database objects such as rows, tables, or other resources that require longer-term serialization. When a session needs to modify a row in a table, an enqueue lock is placed to ensure the modification occurs safely without interference from other sessions.
- Unlike latches, enqueues support queuing. If a session requests access to a resource that is currently locked by another session, it will queue and wait for the lock to be released, rather than continuously retrying. This serialized access makes enqueues slower than latches but necessary for operations that need more careful coordination.
- Enqueues are typically used for row-level locking and other database-level operations, ensuring transaction integrity and preventing conflicts.
Manual Locking:
- Oracle also provides options for manual locking. This can be done through the FOR UPDATE clause in a SELECT statement, which explicitly locks selected rows, or through the LOCK TABLE statement, which locks an entire table.
- Additionally, developers can create custom locks using the DBMS_LOCK package, which allows them to define and manage locks for specific application needs outside of the standard locking mechanisms provided by Oracle.
Deadlocks
A deadlock occurs when two sessions block each other, each waiting for a resource that the other is holding. Oracle automatically detects and resolves deadlocks by rolling back one of the transactions, allowing the other to proceed. However, avoiding deadlocks is crucial for maintaining high-performance concurrency.
Multi-Versioning
Oracle uses a **multi-version read-consistent concurrency model** to ensure consistency without blocking:
- Read-consistent queries: Oracle provides queries that produce results consistent with a single point in time using **rollback segments** to reconstruct the data as it was at the start of the query.
- Non-blocking queries: Unlike other databases, Oracle does not block readers during data modifications. If rows are changed during a query, Oracle uses the **undo** information to provide a consistent snapshot of the data without locking the table, even for large reads. This allows multiple users to work concurrently without contention.
Transaction and Row Locks
**Row-level locks** protect specific rows within a data block during transactions. When a transaction modifies a row (via **INSERT**, **DELETE**, **UPDATE**, or **SELECT FOR UPDATE**), it acquires an **enqueue** and an **exclusive lock** on the row. These locks are held until the transaction commits or rolls back. Locks are stored in the data block itself, making them accessible across the database.
- Transaction Enqueues: These are used to queue processes waiting for a locked object.
- System Monitor (SMON) acquires locks in exclusive mode when recovering (undoing) failed transactions.
Transaction Slots and Interested Transaction Lists (ITL)
Each data block (except for temporary and rollback segments) has predefined **transaction slots**, which track active transactions. These slots are referred to as **Interested Transaction Lists (ITLs)**, and their number is controlled by the **INITRANS** parameter (default is 2 for tables, 3 for indexes). The **MAXTRANS** parameter limits the number of transaction slots, but a block can only use up to 50% of its space for transaction slots.
Each ITL entry occupies 24 bytes and contains:
- **Transaction ID (XID)**: Points to the transaction in the rollback segment.
- **Undo Byte Address (UBA)**: Points to undo information for rollback.
- **Flags**: Indicate transaction status.
- **Lock Count (Lck)**: Tracks the number of rows locked within the block.
- **System Change Number (SCN)**: Indicates the SCN when the transaction updated the block.
Fast Commit and Delayed Block Cleanout
Oracle uses a **fast commit** mechanism, where upon commit, only the transaction table in the rollback segment is updated, leaving the ITL in the data block pointing to the rollback segment. This allows other transactions to access the block, but any uncommitted changes are rolled back using the undo information.
- Delayed Block Cleanout: After a transaction commits, Oracle delays clearing out the locks (such as the lock byte in the row header) until a subsequent DML operation on the block. This reduces immediate overhead and delays the full cleanup of the block, but generates **redo** information when it eventually updates the block with the latest **SCN**.
Oracle optimizes commits by using this delayed cleanout approach to minimize contention during the transaction lifecycle, but cleanout operations are eventually triggered to ensure data consistency and reduce redo generation.
Monitoring Locks and Transactions
- **X$KTUXE**: This view can provide information on rows affected by a transaction.
- **V$TRANSACTION**: This view offers details on active transactions, including status, undo, and locking behavior.
This mechanism allows Oracle to balance transaction integrity, system performance, and efficient memory management.
Here are some useful queries which can help you find locks in a database:
Identify locks and Transaction ID’s:
select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1, to_number(‘ffff’, ‘xxxx’))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = ‘TX’
and v$lock.sid = v$session.sid
and v$session.username = USER;
Identify who is blocking whom
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;
