Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Thursday, August 14, 2008 7:59 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# 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.
8/18/2008 9:07 AM | Mark Cooper
Gravatar

# 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
8/20/2008 6:16 AM | Joe Webb
Gravatar

# re: SQL Server locks: a sleeping process still holds them

hi

i'm jamel from tunisia and thnk for porgram
9/8/2008 7:31 AM | jamel
Gravatar

# 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.
10/16/2010 3:57 PM | columbia jackets
Gravatar

# 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
10/19/2010 4:28 AM | fur boots
Gravatar

# 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
10/26/2010 1:34 AM | hanly
Gravatar

# 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!
7/28/2011 1:55 AM | http://www.yslshoesvip.com
Gravatar

# 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.
7/28/2011 2:12 AM | edhardysalelive
Gravatar

# re: SQL Server locks: a sleeping process still holds them

Much informative article,Thank you
8/2/2011 12:40 AM | registryspeeder
Gravatar

# re: SQL Server locks: a sleeping process still holds them

ohhh yeah!
7/3/2012 6:31 AM | video
Gravatar

# 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.
9/21/2012 12:27 PM | Richard
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET