Fellow SQL Server enthusiast and online friend of mine, Jonathan Kehayias is very active in the MSDN Forums. He frequently provides good, insightful information to those seeking advice.

In response to a recent question in the Database Engine Forum, Jonathan provided a good example how a process marked as sleeping can still hold locks on a SQL Server resource. I'd like to share a slightly modified version of it with you; you can read his original postings here.

Let's consider a very simple table in tempdb. Use SQL Server Management Studio to execute the following query:

USE tempdb ;
GO

--create a table for the demo
CREATE TABLE demo
        (
         column1 INT NOT NULL
                     IDENTITY
        ,column2 VARCHAR(10)
        ) ;

Next let's start a transaction and insert a new row into our newly created table.

--start a transaction
BEGIN TRANSACTION ;

--insert a row into the transaction
INSERT
        demo ( column2 )
VALUES  ( 'something' ) ;

For this demo, make sure you don't commit the transaction. Now, open a new query window in Management Studio so we can examine the locks.

USE tempdb ;
GO

--what's the dbid for tempdb?
DECLARE @dbid INT ;
SET @dbid = DB_ID() ;

--what's objectid for our demo table?
DECLARE @objectid INT ;
SET @objectid = OBJECT_ID(N'tempdb.dbo.demo') ;

--look at locking in the tempdb
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 demo table
SELECT
        *
FROM
        sys.dm_tran_locks
WHERE
        resource_database_id = @dbid AND
        resource_associated_entity_id = @objectid ;

The first resultset shows that there are four locks in the tempdb database. Obviously, I'm creating this demonstration on a non-production server. The second resultset limits the output on only the demo table.

And now, let's look at the information for the offending process.

--examine the
EXEC sp_who 58 ;

From this resultset, you can see that our process is considered 'sleeping' and 'awaiting command', yet it maintains its locks on the table.

When you give this some thought it makes sense. The process isn't actively doing anything at the moment, however it has an active transaction in progress. Until that transaction is either committed or rolled back, the locks will remain.

Go back to the original query window and commit the transaction.

--commit the transaction
COMMIT TRANSACTION ;

Re-running the queries from the second query window shows that the locks have been released.

Cheers!

Joe