How to check which locks are held on a table

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.sysprocesseswhere 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.

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.

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

USE yourdatabase;GOSELECT * 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.