how to find a record lock in a table

Joseph Kurian

In a database file sometimes the table record is locked. A typical program will control this lock and unlock and manage it. But sometimes due to system crash or someother malfunction a lock may not be removed by the program and the system will prevent other users from using that record which is locked by the crashed system. How to find which user locked a table record? Is there anyway to find which user placed a lock on a table record?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mohammad Hussain3y ago
Try This..

Use sp_lock to get lock information
```
EXEC sp_lock @spid = @@spid;
```
This will show all locks held by the current session. You can filter by the DBID and ObjId to find table locks.

or try this
```
SELECT * FROM sys.dm_tran_locks
WHERE resource_type = 'KEY'
AND resource_database_id = DB_ID('AdventureWorks')
AND resource_associated_entity_id = OBJECT_ID('Sales.SalesOrderDetail');
```
This will show key/row locks on a specific table.

Tahir Ansari3y ago

This query can help you identify blocking sessions, but it might not provide the exact information about which user initiated the lock on a specific record

SELECT
    s.session_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    t.text AS [SQL_Text]
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;