Query Lock Information for SQL Server and Oracle
Apart from what has previously been discussed in Active Session Queries, another important detail is which locks are currently being held in the database and where they come from. You can combine this information with the information from the active session queries and determine the action to take. We will take a look at how to get lock information for both Oracle and SQL Server.
This tip highlights the queries you need to run to determine the current lock status of your database. Interpreting those results may be challenging, but if you have identified problematic active session queries, these results can get you closer to resolving the problem.
Oracle Query to Find Current Locks
The view that provides the required information is v$locked_object, and the query is provided below:
SELECT L.SESSION_ID "Session", L.ORACLE_USERNAME "DbUser", L.OS_USER_NAME "OsUser", S.USERNAME "User", S.BLOCKING_SESSION "BlockingSession", DECODE(L.LOCKED_MODE, 0, 'None', 1, NULL, 2, 'Row share', 3, 'Row exclusive', 4, 'Table share', 5, 'Row share table exclusive', 6, 'Table exclusive') "Mode", O.OBJECT_TYPE "ObjectType", O.OWNER||'.'||O.OBJECT_NAME "Object", O.SUBOBJECT_NAME "Detail", O.SHARDED, S.STATUS||' '||S.LAST_CALL_ET||'s' "Status", DECODE(S.WAIT_TIME, 0, S.SECONDS_IN_WAIT, 0) "WaitTimeSec", S.EVENT "Event", S.WAIT_CLASS "Wait", S.STATE "State", 'ALTER SYSTEM KILL '''||S.SID||','||S.SERIAL#||',@'||S.INST_ID||''' IMMEDIATE;' "Kill", DECODE(S.SQL_ID,NULL,'','SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('''||S.SQL_ID||''','||S.SQL_CHILD_NUMBER||',''ALL +PEEKED_BINDS''));') "ViewPlan" FROM GV$LOCKED_OBJECT L INNER JOIN DBA_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID INNER JOIN GV$SESSION S ON S.SID = L.SESSION_ID AND S.INST_ID = L.INST_ID ORDER BY (CASE WHEN S.BLOCKING_SESSION IS NOT NULL THEN 1 ELSE 0 END) DESC, L.LOCKED_MODE DESC, DECODE(S.WAIT_TIME,0,S.SECONDS_IN_WAIT,0) DESC;
A sample output is below:
The columns are as follows:
- Session: Session ID (SID) locking the object.
- DbUser: User executing the statement.
- OsUser: Operating system (OS) user connected to the database to determine the source of the lock.
- BlockingSession: Session ID (SID) blocking this session ID (first column), which helps you identify blocking leaders; in the example above, session 2663 is blocking 4422.
- Mode: Mode of
the lock, which, most of the time, should be no lock or just the row is locked.
- None: no lock.
- Row share: no lock.
- Row exclusive: just the row is locked.
- Table share: no lock.
- Row share table exclusive: Table locked only for other whole-table operations.
- Table exclusive: Table locked.
- ObjectType: Type of the locked object.
- Object: Owner and name of the locked object.
- Detail: Detail of the locked object, i.e., the partition name.
- Sharded: If the locked object is sharded (Y) or not (N).
- Status: Session status (active or inactive) and how many seconds the session is in the current state.
- WaitTimeSec: Number of seconds the session has been waiting.
- Event: Wait event for the current session. You can search for it to determine the action to take: Descriptions of Wait Events.
- Wait: Wait class for the current session. You can search for it to determine the action to take: Classes of Wait Events.
- State: Indicates if the session is waiting or not.
- Kill: Statement to kill the session (should not be used unless absolutely needed).
- ViewPlan: Statement to view the execution plan of the statement. With this SQL_ID, you can also run the SQL Tuning Advisor to find ways to improve it: How Can we Run SQL Tuning Advisor For A SQL ID In Oracle Database.
Note: The results are ordered in the following way:
- at the top appear sessions that are being blocked by another session
- by the lock mode from most restrictive to the less restrictive
- and lastly by the time the session has been waiting.
SQL Server Query to Find Current Locks
The system management views that provide the required information are sys.dm_tran_locks and sys.dm_exec_requests. The query is below:
SELECT [r].[wait_resource], [r].[session_id], [r].[blocking_session_id], [r].[status], [r].[start_time], [r].[command], [r].[wait_type], [r].[wait_time], [r].[cpu_time], [r].[total_elapsed_time], [r].[reads], [r].[writes], [r].[dop], DB_NAME([l].[resource_database_id]) [database], [l].[resource_type], (CASE WHEN [l].[resource_type] = 'DATABASE' THEN '' WHEN [l].[resource_type] = 'FILE' THEN FILE_NAME([l].[resource_description]) WHEN [l].[resource_type] = 'OBJECT' THEN OBJECT_NAME([l].[resource_associated_entity_id], [l].[resource_database_id]) WHEN [l].[resource_type] IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN ( SELECT OBJECT_NAME([pt].[object_id]) FROM [sys].[partitions] [pt] WHERE [pt].[hobt_id] = [l].[resource_associated_entity_id]) WHEN [l].[resource_type] = 'EXTENT' THEN '' WHEN [l].[resource_type] = 'APPLICATION' THEN '' WHEN [l].[resource_type] = 'METADATA' THEN '' WHEN [l].[resource_type] = 'ALLOCATION_UNIT' THEN ( SELECT [au].[type_desc] FROM [sys].[allocation_units] [au] WHERE [au].[allocation_unit_id] = [l].[resource_associated_entity_id]) END) [requested_object_name], [l].[resource_subtype], [l].[resource_description], [l].[resource_lock_partition], [l].[request_mode], [l].[request_type], [l].[request_status], [l].[request_reference_count], [l].[request_lifetime], [l].[request_exec_context_id], [l].[request_request_id], [l].[request_owner_type], [l].[request_owner_id], [l].[request_owner_guid] [DT_UOW], [l].[request_owner_lockspace_id], [l].[lock_owner_address] FROM [sys].[dm_tran_locks] [l] LEFT JOIN [sys].[dm_exec_requests] [r] ON [r].[session_id] = [l].[request_session_id] ORDER BY (CASE WHEN [r].[blocking_session_id] > 0 THEN 1 ELSE 0 END) DESC, [r].[wait_time] DESC;
A sample output is below:
It shows the following columns:
- Wait_resource: ID of the resource being waited for.
- Session_id: A unique identifier for the session in case you need to kill it.
- Blocking_session_id: Session ID blocking this one (second column), which helps you identify blocking leaders; in the example above, session 209 is blocking 287, which is blocking 311, which is blocking 247.
- Status: Status of the request: background, rollback, running, runnable, sleeping, or suspended.
- Start_time: Timestamp when the request arrived.
- Command: Type of command: SELECT, INSERT, UPDATE, DELETE, BACKUP LOG, BACKUP DATABASE, DBCC, FOR, LOCK MONITOR, CHECKPOINTLAZY or WRITER.
- Wait_type: Type
of wait as described in
Types of waits; in the example above there are:
- LCK_M_U: Waiting to acquire an update lock.
- BROKER_RECEIVE_WAITFOR: A lock contention is blocking the receipt of queue messages.
- CXCONSUMER: It is a parallel query where the parent is waiting for the children to send rows.
- ASYNC_NETWORK_IO: Waiting for the client to acknowledge it has processed the data already sent.
- Wait_time: Milliseconds waiting.
- Cpu_time: Milliseconds using CPU.
- Total_elapsed_time: Total milliseconds elapsed.
- Reads: Number of reads.
- Writes: Number of writes.
- Dop: Degree of parallelism.
- Database: Name of the database.
- Resource_type: Type of the locked object: database, file, object, page, key, row id, heap or binary tree, extent, application, metadata, or allocation unit.
- Requested_object_name: Name of the locked object, i.e., the partition name.
- Resource_subtype: Subtype of the locked object.
- Resource_description: Additional information regarding the locked object, i.e., the partition name.
- Resource_lock_partition: ID of the lock partition.
Mode of the request as described in the official documentation of
sys.dm_tran_locks; in the example above, there are:
- IX: Intent to put an exclusive lock.
- S: Shared (no lock).
- IS: Intent to put a shared lock.
- Request_type: It is always LOCK.
- Request_status: Status of the lock request: GRANTED, CONVERT, WAIT, LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT, or ABORT_BLOCKERS.
- Request_reference_count: Approximate number of times the same requestor has requested this resource; high numbers are always bad.
- Request_lifetime: Internal use only.
- Request_exec_context_id: Execution context ID of the process currently owns this request.
- Request_request_id: Request or batch ID of the process that owns this request.
- Request_owner_type: Entity type that owns this request: TRANSACTION, CURSOR, SESSION, SHARED_TRANSACTION_WORKSPACE, EXCLUSIVE_TRANSACTION_WORKSPACE or NOTIFICATION_OBJECT.
- Request_owner_id: Owner or transaction ID of the request.
- DT_UOW: Distributed transaction GUID of the request.
- Request_owner_lockspace_id: Lockspace ID of the request.
- Lock_owner_address: Memory address tracking the request can be joined to sys.dm_os_waiting_tasks.resource_address.
Note: The results are ordered in the following way:
- at the top appear the sessions being blocked by another session
- and then by the time the session has been waiting.
The database lock information can be quite helpful in identifying if there is slowness in the database and what is causing it. In Oracle, a single view gives you all the necessary information, and the columns are clear enough. In SQL Server, only a few columns provide information; the others are poorly documented and need to be analyzed by Microsoft support. But having this information ordered gives hints about the correct course of action to take.
- The official documentation for Oracle: 8.44 V$LOCKED_OBJECT
- The official documentation for SQL Server: sys.dm_tran_locks (Transact-SQL) and sys.dm_exec_requests (Transact-SQL)
- Additional tips for SQL Server:
- Understanding SQL Server Locking
- Different techniques to identify blocking in SQL Server
- Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
- Understanding SQL Server Blocking
- Understanding and using SQL Server sys.dm_exec_requests
- Identify the cause of SQL Server blocking
- How to kill a blocking negative SPID in SQL Server
- Does updating SQL Server statistics cause blocking
- Troubleshoot distributed transaction performance problems in SQL Server
- Lesson on SQL Server Deadlocks and how to solve
About the author
View all my tips
Article Last Updated: 2023-05-24