How to find out what is locking my tables?

sql

lock-management

sql-server

database-performance

byAnton Shumikhin·Feb 17, 2025

To scrutinize table locks in SQL Server, meld the sys.dm_tran_locks view with sys.partitions and sys.dm_exec_sessions for efficient lock navigation:

SELECT 
    DB_NAME(tl.resource_database_id) AS database_name,
    OBJECT_NAME(p.object_id) AS locked_table,
    tl.request_mode AS lock_type,
    s.session_id,
    s.login_name
FROM 
    sys.dm_tran_locks tl
INNER JOIN 
    sys.partitions p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN 
    sys.dm_exec_sessions s ON s.session_id = tl.request_session_id
WHERE 
    tl.resource_type = 'OBJECT' AND tl.request_status = 'WAIT'

MySQL users, inspect InnoDB locks via information_schema tables to pinpoint active locks:

SELECT 
    trx.trx_id,
    trx.trx_state,
    l.lock_table,
    l.lock_mode,
    p.PROCESSLIST_ID,
    p.PROCESSLIST_USER,
    p.PROCESSLIST_INFO
FROM 
    information_schema.innodb_trx trx
JOIN 
    information_schema.innodb_locks l ON l.lock_trx_id = trx.trx_id
JOIN 
    information_schema.processlist p ON trx.trx_mysql_thread_id = p.PROCESSLIST_ID

Launch these queries in your SQL environment to discover the core of locking pandemonium with crucial insights on lock-holding sessions.

Street view: Spotlighting the who's who of blockers

To dig deeper into the blockers and their victims, pair master.dbo.sp_who2 with sys.dm_exec_requests for robust oversight:

EXEC sp_who2; --Behold! All active spids, their statuses, and other useful info
SELECT 
    blocking_session_id, 
    session_id 
FROM 
    sys.dm_exec_requests 
WHERE 
    blocking_session_id != 0; -- Zero in on sessions being blocked

This dynamic duo promptly enlightens you on the whereabouts of lock culprits.

Dealing with line cutters: nolock and transaction isolation

To swiftly access data, you might be swayed to use WITH (NOLOCK) or opt for the TRANSACTION ISOLATION LEVEL of READ UNCOMMITTED to dodge lock waits. But remember:

WITH (NOLOCK): This is your FastPass to data. But beware, it can serve uncommitted data. Use when you can handle a dash of inconsistency.
READ UNCOMMITTED: Cuts down on lock traffic, but at the cost of your data integrity.

Your quick solution might turn into a Shortcut to Disarray! Beware!

Killing spree: Hitman's guide to terminating processes

When patience runs thin, you might ponder assassinating a blocking SPID. It's simple:

KILL <SPID>; -- Stay calm, it's just a command!

But before you turn into SQL's terminator, remember: Interrupting transactions may leave behind half-baked updates or worse, corrupted data. Ensure the payoff is worth the risks!

Advanced lock investigation: SQL CSI

Sometimes, basic glimpses just don't cut it. Cross-apply sys.dm_exec_sql_text to gaze at the SQL text source of the drama:

SELECT 
    tl.request_session_id,
    st.text AS sql_statement,
    tl.resource_type,
    tl.request_mode
FROM 
    sys.dm_tran_locks tl
CROSS APPLY 
    sys.dm_exec_sql_text(tl.request_owner_id) AS st
WHERE 
    resource_database_id = DB_ID('YourDatabaseName'); -- P.I. Central command: replace 'YourDatabaseName' with your DB name.

Step up your game with some SQL forensics!

Proactive lockdown: Monitoring and optimizing

Regular lock pattern examination might scream for query optimization or index adjustment. Keep tabs on key resource metrics:

CPU consumption
I/O rates
Query's ticking clock

SQL Server Management Studio's Activity Monitor or alternative solutions can highlight lengthy queries and resource hogs, putting you a step ahead of impending lock scenarios.

Up your arsenal: Reference guide and troubleshooting

Leverage SQL Server upgrades for advanced features like nifty lock escalations and a revved-up Activity Monitor. Stay updated with the Microsoft documentation as evolving lock management strategies reshape the SQL engine playground.

explain-codes / Sql / How to find out what is locking my tables?

Linked

How to check which locks are held on a table



Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction



How to release possible Postgres row locks?



How to detect query which holds the lock in Postgres?



What is (are) the differences between NOLOCK and UNCOMMITTED

