Finding Locks on Tables

Recently I needed to find a stored procedure that was creating a lock on a table and updating the table regularly. So my starting point was the table name, but I needed to find where the commad was coming from that was locking the table, and what stored procedure was doing the update, if any. As I could not be sure that it was a sproc, I wanted to make sure that I also got the program name.
A simple version of the query would look like this:

 --simple

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

But this does not get full info. But by joining on the dmv exec_sessions and exec_requests on the corresponding session id columns, and by cross applying on the exec_sql_text on the exec_request sql_handle column I can get the full text of the command. I can also narrow down the lock requests types to only the update lock types:

--verbose

SELECT sesh.program_name, sesh.host_name, st.text, sesh.login_name, lock.request_mode, lock.request_session_id
  FROM sys.dm_tran_locks lock
  INNER JOIN sys.dm_exec_sessions sesh ON sesh.session_id = lock.request_session_id
  INNER JOIN sys.dm_exec_requests req ON req.session_id = lock.request_session_id
  CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st
  WHERE lock.resource_database_id = DB_ID()
  AND lock.resource_type = 'OBJECT'
  AND lock.resource_associated_entity_id = OBJECT_ID(N'CciDemo')
  AND lock.request_mode IN ('X', 'U', 'IX')

The results of the queries whilst I was running a long running update on a table is below.

2015-04-29 21_37_02-PHBXSQL01 on PHBX - Virtual Machine Connection

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s