In the DBA_DML_LOCKS view, the MODE_HELD column indicates the type of lock mode that is currently held by a session on a particular object. The distinct values of MODE_HELD in the DBA_DML_LOCKS view represent different levels of locking in Oracle. Here are the possible values and their meanings:
Distinct MODE_HELD Values in DBA_DML_LOCKS
NULL (NL)
- No lock is held.
Row-S (RS)
- Row Share, also known as Sub-S mode.
- Allows concurrent queries but prevents other sessions from locking the table for exclusive DML operations (e.g., INSERT, UPDATE, DELETE).
Row-X (RX)
- Row Exclusive, also known as Sub-X mode.
- Allows concurrent access to the table but prevents other sessions from locking the table in exclusive mode.
Share (S)
- Share mode.
- Allows concurrent queries but prevents other sessions from modifying the data.
S/Row-X (SRX)
- Share Row Exclusive mode.
- Combination of Share and Row Exclusive modes. This mode is typically used by queries that include subqueries.
Exclusive (X)
- Exclusive mode.
- Prevents other sessions from accessing the table in any mode. This is typically used for DML operations that need to ensure no other session can modify the data while the operation is in progress.
Explanation of Lock Modes
- NULL (NL): This is effectively no lock and means the session is not holding any lock on the object.
- Row Share (RS): This mode is used to lock a table in a way that allows other sessions to perform reads but not DML operations that would require exclusive access.
- Row Exclusive (RX): This mode is slightly more restrictive than Row Share, allowing for DML operations but still permitting concurrent access by other sessions.
- Share (S): This mode prevents DML operations by other sessions but allows reads.
- Share Row Exclusive (SRX): This mode combines the restrictions of Share and Row Exclusive locks, preventing DML operations but still allowing certain types of queries.
- Exclusive (X): This is the most restrictive lock mode, preventing all other sessions from accessing the object.
Example Query to List Distinct MODE_HELD Values
You can query the DBA_DML_LOCKS view to list the distinct MODE_HELD values currently in use:
SELECT DISTINCT MODE_HELD FROM DBA_DML_LOCKS;
Usage Scenario
Understanding the different lock modes is crucial for diagnosing and resolving locking issues in Oracle. For instance, if a session is holding an exclusive lock (X) on a table, other sessions attempting to modify or lock the table will be blocked until the exclusive lock is released.
Conclusion
The MODE_HELD column in the DBA_DML_LOCKS view provides valuable insights into the type of locks held by sessions in the Oracle database. By understanding the different lock modes (NULL, RS, RX, S, SRX, X), you can better manage and troubleshoot locking and concurrency issues, ensuring efficient and smooth database operations.