Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Understanding SQL Server Locking

MSSQLTips author Tim Ford By:   |   Read Comments   |   Related Tips: More > Locking and Blocking
Problem

Help!  My SQL Server database is locking.  That can't be good!  What should I do?

Solution

Locking is integral to the successful processing of transactions in any relational database management system (RDBMS) worth its reputation.  It is important in SQL Server that transactions pass the ACID test:

  • Atomic - the transaction performs in an all-or-nothing fashion
  • Consistent
  • Isolated - transactions are properly isolated until they're finished
  • Durable - the RDBMS will maintain a record of uncompleted transactions in the event of recovery during a failure. 

Isolation is addressed by locking of objects until their associated transactions are completed.  In the case of locking (and blocking, to be discussed in a subsequent article) there is a bit of terminology we will be using:

  • Request - the command and all subsequent dependencies on the command that requires action by the RDBMS.
  • Resource - any object that is affected by the request.

Think of a simple transaction such as buying a movie ticket.  The request is your statement: "I would like two tickets to Alvin And The Chipmunks 3 - The Habitrail Massacre."  The resources involved may include the paper ticket, your debit card account, etc.  (No, every bad decision you ever made in your life to get you to the point where you would see 3 CGI rodents squeal for 90 minutes is not factored-into the transaction process.)  In SQL Server, the resource may be a row (identified by a RID or row id), a page, a database, an object, and so-forth.  We will be ignoring database locks in our results because one is issued for every connection into a database and they do not restrict access via locking or blocking by other connections attempting to have relations with your database.  Keep in mind that the locking behavior is dependent upon a variety of factors.  The behavior of how locks are issued in SQL Server is dependent upon the isolation level of the session the activity is being conducted under; Isolation Level is a topic in and of itself however and I will not be covering it here. No, what we're going to discuss here is a method for identifying currently issued locks of interest in a specific database under review.  We'll be doing this via a query against our old friend sysprocesses as well as one of the Dynamic Management Views available to us since SQL Server 2005: sys.dm_tran_locks.

The syntax for calls to sys.dm_tran_locks is consistent with other queries you'll write and also consistent with those DMV-based tips I've presented here to-date:

SELECT FROM sys.dm_tran_locks;

It is important in SQL Server that transactions pass the ACID test

Atomic - the transaction performs in an all-or-nothing fashion

Durable - the RDBMS will maintain a record of uncompleted transactions in the event of recovery during a failure.

That is truly a great deal of information if anyone wants to take the time to wade through it.  However, we're interested in just a few of those possible columns:

  • resource_type - just as you would expect, the type of resource being locked.
  • request_mode - the request_mode is actually the lock type being issued.  Check SQL Server Books Online for the full listing of possible lock types.
  • request_status - the current status of the request (Possible values are GRANTED, CONVERT, or WAIT.)
  • resource_associated_entity_id - the object_id associated with the resource being requested

With just these three columns, we will join this DMV to the sys.sysprocesses system compatibility view in order to get some form of meaningful information about the status of locking within the current database.  We will take it one step further as well.  By performing a CROSS APPLY against the sys.dm_exec_sql_text Dynamic Management Function (DMF) we can also return the command text involved in the request.  Don't worry if you're not familiar with CROSS APPLY, there was a recent tip here at MSSQLTips.com that will help you understand what that command accomplishes.  The link is included below

Enough about all this other extraneous information.  You came here to see a query for locking.  Let's look at it:

--============================================ 
--View Locking in Current Database 
--Author: Timothy Ford 
--http://thesqlagentman.com 
--============================================ 
SELECT DTL.resource_type,  
   CASE   
       WHEN DTL.resource_type IN ('DATABASE''FILE''METADATA'THEN DTL.resource_type  
       WHEN DTL.resource_type 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_idSP.[dbid])  
       WHEN DTL.resource_type IN ('KEY''PAGE''RID'THEN   
           (  
           SELECT OBJECT_NAME([object_id])  
           FROM sys.partitions  
           WHERE sys.partitions.hobt_id =   
             DTL.resource_associated_entity_id  
           )  
       ELSE 'Unidentified'  
   END AS requested_object_nameDTL.request_modeDTL.request_status,  
   DEST.TEXTSP.spidSP.blockedSP.statusSP.loginame 
FROM sys.dm_tran_locks DTL  
   INNER JOIN sys.sysprocesses SP  
       ON DTL.request_session_id SP.spid   
   --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id] 
   CROSS APPLY sys.dm_exec_sql_text(SP.sql_handleAS DEST  
WHERE SP.dbid DB_ID()  
   AND DTL.[resource_type] <> 'DATABASE' 
ORDER BY DTL.[request_session_id];

In the query, besides returning the columns from sys.dm_tran_locks I itemized earlier we also return various descriptive columns from sys.sysprocesses as well as the requested_object_name column that may need a little explanation.  This value depends upon what type of object is being requested and locked.  In the case of a resource_type of 'DATABASE', 'FILE', or 'METADATA' there is no name associated with the resource and therefore is simply the same as the resource_type.  When the resource_type is equal to 'OBJECT' then it is derived from the object name of the object_id referenced via the resource_associated_entity_id column.  When the resource_type is either 'KEY', 'PAGE', or 'RID' then the object name is derived from the join to sys.partitions via the resource_associated_entity_id column.

 In order to review the behavior of this query I'm going to fire off a simple INSERT statement to generate some activity on an otherwise static SQL Server instance (take my word for it.):

BEGIN TRAN INSERT_FOO 
   INSERT INTO dbo.Categories([CategoryName][Description]) 
   VALUES ('Foo''Description FOR Foo') 
--ROLLBACK TRAN INSERT_FOO

Notice that I've left the transaction in a running state.  This is so we can examine the locking behavior.  The results of our query against sys.dm_tran_locks would look like this as a result:

The results of our query against sys.dm_tran_locks would look like this as a result

If I was to now run the following query, prior to rolling back the INSERT_FOO transaction that I commented out in the aforementioned script our output would change...

BEGIN TRAN 
UPDATE dbo.Categories 
SET [Description] 'Foo' 
WHERE [CategoryName] 'Foo'; 
ROLLBACK TRAN

our sample queries you can see that spid 54 is being blocked by spid 52 as evidenced by the blocked column

As you'll see, not only does this script identify locking, but it does also present blocking information as well.  In the case of our sample queries you can see that spid 54 is being blocked by spid 52 as evidenced by the blocked column.  My next tip provides a more-detailed presentation of blocking so while you can use this query for blocking, I have another one that is more appropriate for that subject.

If I was to rollback my INSERT_FOO transaction you'll now see the state of locking for the active database I've been working with is greatly diminished; matter-of-fact.  There is none to report.

Next Steps


Last Update: 3/23/2010


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.