Identifying Locked Rows in SQL Server Tables

Introduction
T-SQL description
T-SQL to identify locked rows
Row lock example
Conclusion

Introduction

If you've ever encountered a situation where you can't access certain rows in your database because they're locked, don't worry! In this blog, we'll walk you through a simple process to identify and retrieve locked rows in your SQL database.

T-SQL description

  • Check for Locked Tables: First, we need to figure out which tables in your database currently have locked rows. You can do this by using the T-SQL script provided. This script will help you determine the tables that are causing the lock.

  • Edit the following T-SQL: Once you know which tables have locked rows, it's time to edit the following T-SQL by replacing my_table_name with the name of the table where you suspect locked rows.
    For example, if your table is called dbdocs_users, you'll replace my_table_name with dbdocs_users.

T-SQL to identify locked rows

Now that you've edited the script, it's time to run it. This query will give you a list of rows from the specified table that are currently locked. You'll be able to see which rows are affected by the lock, allowing you to take appropriate action. 

select
    t.*
from
    my_table_name (nolock) as t
    join
    sys.dm_tran_locks as dtl
        on t.%%lockres%% = dtl.resource_description;

Row 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 locked rows

Row Lock

Conclusion

In conclusion, by following this simple process, you'll be able to identify and retrieve locked rows in your SQL database, ensuring smooth operation and efficient data access. Don't let locked rows slow you down – take control of your database with these easy steps!


Rate Your Experience

: 89 : 0

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Navigation Panel

All Rights Reserved @2023 | www.dbdocs.net | Privacy Policy | Disclaimer| Cookie Policy