sql - 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

Answer 1

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.