Read committed snapshot:Finding lock on table

Question

IF update command is run on a table and commit is pending then another user cannot be able to update the table.

Is there any query to get the user or PC which caused lock on the table.

Answer 1

It is possible to list the locks using the DMV sys.dm_tran_locks (sp_lock is deprecated).

The DMV query can be hard to code against and the results can be difficult to interpret. Consider using the free sp_WhoIsActive tool from Adam Machanic instead. He explains how to use it to analyse chains of blocking locks in this blog post (which also contains a download link).

Answer 2

Sys.dm_tran_locks  will list all current locks which are granted or pending. You can join it on session_id with  sys.dm_exec_sessions  will list all current sessions which includes client host name and login name.

You can even use the GUI - activity monitor to watch for activities going on on the server instance. (You can even profile using PROFILER and then use the T-SQL to monitor your server instance).