How to Identify Current Locks in Your SQL Server Database

Introduction
T-SQL to identify current locks within the application databases
T-SQL description
Current locks example
Conclusion

Introduction

In the realm of database management, ensuring data integrity and concurrency is crucial. One aspect of this is managing locks within your database. Locks prevent multiple processes from simultaneously modifying the same data, thus maintaining consistency. In this blog, we'll delve into a SQL query designed to provide detailed insights into the current locks within your application database.

T-SQL to identify current locks within the application databases

This SQL query retrieves information from dynamic management views, providing details on current locks within the application databases

select
    dtl.request_session_id,
    db_name(dtl.resource_database_id) as database_name,
    dtl.resource_type,
    case
    when dtl.resource_type in ('DATABASE', 'FILE', 'METADATA') then
        dtl.resource_type
    when dtl.resource_type = 'OBJECT' then
        object_name(dtl.resource_associated_entity_id, dtl.resource_database_id)
    when dtl.resource_type in ('KEY', 'PAGE', 'RID') then
        (select object_name(object_id) from sys.partitions where sys.partitions.hobt_id = dtl.resource_associated_entity_id)
    else
        'Unidentified'
    end as requested_object_name,
    dtl.request_mode,
    dtl.request_status,
    der.blocking_session_id,
    des.login_name,
    dec.client_net_address,
    dtl.resource_description,
    case dtl.request_lifetime
    when 0 then
        dest_r.text
    else
        dest_c.text
    end as statement
from
    sys.dm_tran_locks dtl
    left outer join
    sys.dm_exec_requests der
        on dtl.request_session_id = der.session_id
    inner join
    sys.dm_exec_sessions des
        on dtl.request_session_id = des.session_id
    inner join
    sys.dm_exec_connections dec
        on dtl.request_session_id = dec.most_recent_session_id
    outer apply
    sys.dm_exec_sql_text(dec.most_recent_sql_handle) as dest_c
    outer apply
    sys.dm_exec_sql_text(der.sql_handle) as dest_r
where
    dtl.resource_type <> 'DATABASE'
and
    db_name(dtl.resource_database_id) = 'dbdocs' -- Replace this with your DBNAME
order by
    dtl.request_session_id;

T-SQL description

Now, let's break down each component and understand its significance

Column                | Description
--------------------- | -------------------------------------------------------
request_session_id    | Session ID that currently owns this request.
database_name         | The database name where the lock is being held.
resource_type         | Request type.
requested_object_name | The object name of the resource being locked.
request_mode          | Request mode for the granted or requested lock.
request_status        | Status of the request. Granted, Convert, or Wait.
blocking_session_id   | ID of the session that is blocking this request. If NULL, not blocked.
login_name            | Login of the user who owns this request.
client_net_address    | IP address of the connection making the request.
resource_description  | Description of the resource being consumed.
statement             | Query making the request.

By executing this query, you gain valuable insights into the current locks within your application database, including details such as the session ID, lock type, and associated SQL statements. This information is invaluable for diagnosing performance issues, identifying potential deadlocks, and ensuring optimal database concurrency.

Current lock example

--Create a table
CREATE TABLE dbdocs_Users (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

--Insert few rows into the table
INSERT INTO dbdocs_Users (ID, Name) VALUES (1, 'John');
INSERT INTO dbdocs_Users (ID, Name) VALUES (2, 'Smith');
INSERT INTO dbdocs_Users (ID, Name) VALUES (3, 'David');
INSERT INTO dbdocs_Users (ID, Name) VALUES (4, 'Thomas');
INSERT INTO dbdocs_Users (ID, Name) VALUES (5, 'Joe');
INSERT INTO dbdocs_Users (ID, Name) VALUES (6, 'Mark');
INSERT INTO dbdocs_Users (ID, Name) VALUES (7, 'Brad');
INSERT INTO dbdocs_Users (ID, Name) VALUES (8, 'Trump');
INSERT INTO dbdocs_Users (ID, Name) VALUES (9, 'Claire');
INSERT INTO dbdocs_Users (ID, Name) VALUES (10, 'Nick');

--In frist session 
BEGIN TRANSACTION;
UPDATE dbdocs_Users SET Name = 'Ken' WHERE ID = 2;

--In second session
BEGIN TRANSACTION;
UPDATE dbdocs_Users SET Name = 'Hudson' WHERE ID = 2;

Now run the SQL T-SQL to identify current locks within the application databases

Conclusion

In conclusion, understanding locks in your application database is essential for maintaining data integrity and optimizing performance. With the insights provided by this SQL query, you can effectively manage locks and ensure smooth operation of your database environment.

Rate Your Experience

: 89 : 0