Checking for Blocking Locks in Oracle

The DBA_LOCKS view is available in Oracle Database from version 7 onwards. This view provides information about all locks in the database, including locks held by sessions and those requested by sessions. It is a crucial tool for database administrators to monitor and manage locking and concurrency issues.

Description of DBA_LOCKS View

The DBA_LOCKS view contains one row for each lock or latch held or being requested by a session.

Columns in DBA_LOCKS View

Some of the important columns in the DBA_LOCKS view include:

• SESSION_ID: The ID of the session holding or requesting the lock.
• LOCK_TYPE: The type of lock (e.g., DML, DDL, internal).
• MODE_HELD: The mode in which the lock is held (e.g., exclusive, share).
• MODE_REQUESTED: The mode in which the lock is requested.
• LOCK_ID1 and LOCK_ID2: Identifiers for the lock.
• LAST_CONVERT: The time when the lock was last converted.
• BLOCKING_OTHERS: Indicates whether this lock is blocking other sessions.

Example Usage

Here’s how you can query the DBA_LOCKS view to see the current locks in the database:

SELECT SESSION_ID, LOCK_TYPE, MODE_HELD, MODE_REQUESTED, LOCK_ID1, LOCK_ID2, LAST_CONVERT, BLOCKING_OTHERS FROM DBA_LOCKS;

Checking for Blocking Locks

To identify sessions that are blocking others, you can use the following query:

SELECT l1.SESSION_ID AS blocking_session, l1.LOCK_TYPE AS blocking_lock_type, l1.MODE_HELD AS blocking_mode, l2.SESSION_ID AS waiting_session, l2.LOCK_TYPE AS waiting_lock_type, l2.MODE_REQUESTED AS waiting_mode FROM DBA_LOCKS l1 JOIN DBA_LOCKS l2 ON l1.LOCK_ID1 = l2.LOCK_ID1 AND l1.LOCK_ID2 = l2.LOCK_ID2 AND l1.BLOCKING_OTHERS = 'Blocking' AND l2.BLOCKING_OTHERS = 'Not Blocking';

Oracle Enterprise Manager (OEM) for Lock Management

Oracle Enterprise Manager (OEM) provides a graphical interface to monitor and manage locks:

1. Log in to OEM.
2. Navigate to the Performance Page: Select the database instance you want to monitor.
3. Locks: Use the performance page to view and manage locks, including identifying blocking sessions and resolving lock conflicts.

Conclusion

The DBA_LOCKS view is a powerful tool available from Oracle Database version 7 onwards. It provides detailed information about locks, which is essential for diagnosing and resolving locking and concurrency issues in the database.