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. Knows a thing or two about Pokémon. Knows too much about SQL Agent. 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