How to check which locks are held on a table

Question

How can we check which database locks are applied on which rows against a query batch?

Any tool that highlights table row level locking in real time?

DB: SQL Server 2005

Solution 1

To add to the other responses, sp_lock can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with sp_who2 to quickly zero in on locking problems.

There are multiple different versions of "friendlier" sp_lock procedures available online, depending on the version of SQL Server in question.

In your case, for SQL Server 2005, sp_lock is still available, but deprecated, so it's now recommended to use the sys.dm_tran_locks view for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.

Solution 2

This is not exactly showing you which rows are locked, but this may helpful to you.

You can check which statements are blocked by running this:

select cmd,* from sys.sysprocesses
where blocked > 0

It will also tell you what each block is waiting on. So you can trace that all the way up to see which statement caused the first block that caused the other blocks.

Edit to add comment from @MikeBlandford:

The blocked column indicates the spid of the blocking process. You can run kill {spid} to fix it.

Solution 3

You can find current locks on your table by following query.

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

See sys.dm_tran_locks

If multiple instances of the same request_owner_type exist, the request_owner_id column is used to distinguish each instance. For distributed transactions, the request_owner_type and the request_owner_guid columns will show the different entity information.

For example, Session S1 owns a shared lock on Table1; and transaction T1, which is running under session S1, also owns a shared lock on Table1. In this case, the resource_description column that is returned by sys.dm_tran_locks will show two instances of the same resource. The request_owner_type column will show one instance as a session and the other as a transaction. Also, the resource_owner_id column will have different values.

Solution 4

I use a Dynamic Management View (DMV) to capture locks as well as the object_id or partition_id of the item that is locked.

(MUST switch to the Database you want to observe to get object_id)

SELECT 
     TL.resource_type,
     TL.resource_database_id,
     TL.resource_associated_entity_id,
     TL.request_mode,
     TL.request_session_id,
     WT.blocking_session_id,
     O.name AS [object name],
     O.type_desc AS [object descr],
     P.partition_id AS [partition id],
     P.rows AS [partition/page rows],
     AU.type_desc AS [index descr],
     AU.container_id AS [index/page container_id]
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_os_waiting_tasks AS WT 
 ON TL.lock_owner_address = WT.resource_address
LEFT OUTER JOIN sys.objects AS O 
 ON O.object_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions AS P 
 ON P.hobt_id = TL.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units AS AU 
 ON AU.allocation_unit_id = TL.resource_associated_entity_id;

Solution 5

You can also use the built-in sp_who2 stored procedure to get current blocked and blocking processes on a SQL Server instance. Typically you'd run this alongside a SQL Profiler instance to find a blocking process and look at the most recent command that spid issued in profiler.

View more solutions

Comments

[Mike Blandford]

The blocked column indicates the spid of the blocking process. You can run kill {spid} to fix it.

[a_horse_with_no_name]

I'm trying to use this statement to find out the objects a process is waiting for. I can clearly see one session waiting for another using sp_who2 and in sys.dm_os_waiting_task (both trying to update the same table). But your statement does not return any rows. Any ideas?

[Vlad]

This is a good script to run if you are not a DBA and don't have access to 'master'.

[user6788933]

This shows locks that are not visible in processes!!