A user reported today that they were trying to query a table with 181 records and the output stopped at 178 and just hung there. I tried to replicate this and the same thing happened – also couldn’t run a Select (*) on the table. So I pulled up the Activity Monitor for the server and observed that there was only one query in process – the hung query. It listed the process as being suspended with a Wait Type of
LCK_M_S. Some quick searching told me that the task was waiting to acquire a shared lock. However, it was not obvious what was locking the table. What to do?
After some more searching, I found two more posts that gave advice on what to do in the given scenario:
- Sql Server Locks, a Sleeping Process Still Holds Them
- SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution
Based on these, I did the following:
Exec sp_who2through Sql Management Studio. This gave me a list of all current processes. There was only one open process on this database at the time, so it was the likely culprit.
- Next, ran the code in the Sql Server Locks article above to show the process that was locking my table. Bingo – process 52.
USE DBNAME; GO; DECLARE @dbid INT ; --what's the dbid for DBNAME? SET @dbid = DB_ID() ; DECLARE @objectid INT ; --what's objectid for our demo table? SET @objectid = OBJECT_ID(N'TABLENAME') ; --look at locking in the DB SELECT resource_type ,resource_database_id ,resource_associated_entity_id ,request_mode ,request_type ,request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = @dbid ; --limit the results to only the offending table SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = @dbid AND resource_associated_entity_id = @objectid ;
After I had confirmed the identity of the process, I ran
Kill 52 to kill the offending process. Everything immediately started working again with the table.
(After the fact, went back through the code with my team and found the spot where someone was running
db.Connection.BeginTransation() on a data context that already had an open transaction, causing an error: “
SqlConnection does not support parallel transactions“. And to make things worse, this was done outside of the
try/catch/finally structure that ensured that any open transactions would be committed. Oy.)
As Pinal Dave writes:
As mentioned earlier, if you kill something important on your production server, there’s a great possibility that you’ll face some serious integrity issues, so I there’s no way I advise use this method. As the title goes, this is a dirty solution so you must utilize this only if you are confident.
It is important to keep that in mind. But that said, this technique for identifying and killing a hung transaction can definitely come in handy. (And be sure to backup your DB and transaction logs regularly!)