SQL Server and Oracle Locking Scripts

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:

v$locked_object results

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:

  1. at the top appear sessions that are being blocked by another session
  2. by the lock mode from most restrictive to the less restrictive
  3. 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:

sys.dm_tran_locks results

It shows the following columns:

  • 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
  • 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:

    1. at the top appear the sessions being blocked by another session
    2. 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

    Related Posts

    One comment

    1. Thanks, Pablo! This is very informative! I especially like the detail that you put into describing the output.

    Leave a Reply

    Your email address will not be published. Required fields are marked *