Understanding SQL Server Locking

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_id, SP.[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_name, DTL.request_mode, DTL.request_status,  
   DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.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_handle) AS 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:

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

Learn about other methods for identifying current activity in your databases at MSSQLTips.com

Resources

Community

Subscribe