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
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!
Related content