Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

SQL Server locks: a sleeping process still holds them

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 ;

 

locking1-2008-08-14

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 ;

locking2-2008-08-14

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.

locking3-2008-08-14

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


Mark Cooper
2008-08-18
re: SQL Server locks: a sleeping process still holds them
We already know about sleeping locks. Does anyone have any thoughts on releasing those locks from outside of the process holding them? Of course, doing this without killing the process.

Joe Webb
2008-08-20
re: SQL Server locks: a sleeping process still holds them
I'm not aware of a way to force the process to release the locks without killing the process itself. I'll do some digging to see if I can find something.

Maybe someone else knows of a way?

Joe

jamel
2008-09-08
re: SQL Server locks: a sleeping process still holds them
hi

i'm jamel from tunisia and thnk for porgram

columbia jackets
2010-10-16
re: SQL Server locks: a sleeping process still holds them
Next let's start a transaction and insert a new row into our newly created table.

fur boots
2010-10-19
re: SQL Server locks: a sleeping process still holds them
Let's consider a very simple table in tempdb. Use SQL Server Management Studio to execute the following query:

snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup

hanly
2010-10-26
re: SQL Server locks: a sleeping process still holds them
This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under "format" option choose the output video formats

http://www.yslshoesvip.com
2011-07-28
re: SQL Server locks: a sleeping process still holds them
thanks! very love it "We already know about sleeping locks. Does anyone have any thoughts on releasing those locks from outside of the process holding them? Of course, doing this without killing the process. ....” very amazing, I love it, thanks!

edhardysalelive
2011-07-28
re: SQL Server locks: a sleeping process still holds them
This is a really good look for me, must admit that you are the best blog I've ever seen one. Thanks for posting this informative article.

registryspeeder
2011-08-02
re: SQL Server locks: a sleeping process still holds them
Much informative article,Thank you

video
2012-07-03
re: SQL Server locks: a sleeping process still holds them
ohhh yeah!

Richard
2012-09-21
re: SQL Server locks: a sleeping process still holds them
What if i closed the query window without commiting the tran,then how to remove the sleeping lock from tempdb.