SQL Server and Oracle Locking Scripts

By: Pablo Echeverria | Updated: 2023-05-24 | Comments (1) | Related: > SQL Server and Oracle Comparison

Problem

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.

Solution

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.
Request_mode: 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.

Conclusion

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.

Next Steps

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:

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-05-24

Query Lock Information for SQL Server and Oracle