Some time your table may locked by some session and you want to know that session and why i can not access my table?
This mean your begain transaction is completed and insert/update/delete is runing but Comit transacion is yet be run
OR you can create the situation by running the below query:
BEGIN TRANSACTION
DELETE * FROM Table1
Below script will allow you to find which session is Locking your table:
SELECT
OBJECT_NAME(P.object_id) AS TableName,
Resource_type,
request_session_id
FROM
sys.dm_tran_locks L
join sys.partitions P
ON L.resource_associated_entity_id = p.hobt_id
WHERE OBJECT_NAME(P.object_id) = 'Table1'
To release the Loking you can kill that session using below script:
KILL 54
If you want to know in detail why, when and which session is locking then below script is useful:
SELECT
blocking_session_id AS BlockingSessionID,
session_id AS VictimSessionID,
(SELECT [text] FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE spid = blocking_session_id) AS BlockingQuery,
[text] AS VictimQuery,
wait_time/1000 AS WaitDurationSecond,
wait_type AS WaitType,
percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0
Filed under: Find Table Locked Session | Tagged: Advance SQL tips and tricks, blocking, Find Table Locked Session, locking, Microsoft SQL server, Session Lock, SQL Server, SQL server tips |
Leave a comment