Clearing out a Mysterious Table Lock (LCK_M_S) in Sql Server 2008

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: Based on these, I did the following:
    1. Ran Exec sp_who2 through 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.
    2. 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!)
Tagged , . Bookmark the permalink.

Leave a Reply