.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Locks, blocks, and deadlocks in SQL Server

Posted By:Manning       Posted Date: February 12, 2011    Points: 75    Category: DataBase    URL: http://www.dotnetspark.com  

The author describes the difference and relationships among locks, blocks, and deadlocks. He explains how, if two queries are holding locks on resources that the other query wants, resulting in circular blocking, this is detected by SQL Server as a deadlock.

This article is taken from the book SQL Server DMVs in Action. The author describes the difference and relationships among locks, blocks, and deadlocks. He explains how, if two queries are holding locks on resources that the other query wants, resulting in circular blocking, this is detected by SQL Server as a deadlock.

40% off any version of SQL Server DMVs in Action with the checkout code dnspark40. Offer is only valid through www.manning.com.

The terms locking, blocking, and deadlocks are often used incorrectly. While the terms are related, they each have a specific meaning. When data is being queried, locks are taken out on the underlying data to ensure data consistency. For example, you wouldn't want two people updating the same data at the same time. If a user issues an update query, the data related to the query will be protected by locks. This may make another user wait until the first query has finished doing its work. The second user's query will be blocked by the first user's query. If two queries are holding locks on resources that the other query wants, resulting in circular blocking, this will be detected by SQL Server as a deadlock. There is no nice way out of a deadlock, so SQL Server terminates one of the queries and rolls back its transaction.
We can see that there is a link between locks, blocking, and deadlocks. We will now examine these in greater

detail, starting with locks.


Locks in SQL Server are normal and to be expected. Locks can be taken out at various levels of the database structural hierarchy, affecting the scope of the locks. At the top level is the database itself; at the bottom level is an individual row of data. A brief overview of the structures that can be locked are given in table 1.

Table 1 Database structure hierarchy

Structur Description
Database This structure represents the database. Locks held against the database can be most restrictive for concurrency but optimal for data consistency.
Table Within the database, locks can be taken out on individual tables.
Heap or B-tree  Heaps are tables without a clustered index. A B-tree object typically refers to a partition.
Extent  An extent is a group of contiguous pages.
Page A page holds rows of data.
Key  A key is a row within an index.
Page A page holds rows of data.
Row  A row is a single row of data within a table.

Locks can be taken out at various levels within this structural hierarchy, and each lock can have a different impact via its severity (for example, an exclusive lock is more restrictive than a shared lock).
The different types of lock that can be taken are shown in table 2. The letter(s) given in brackets after the lock type represent how the lock type is shown in the columns of the various transaction/lock-related DMVs.

Table 2 Types of lock

Lock type  Description
Shared (S) Used for read-only queries. Other read-only queries can access the data concurrently, but modifying queries (delete, update, insert) are prevented to ensure data consistency. By default, the share lock is released after the data has been read.
Update (U)  This lock means the data is being read with the aim of modifying
the data. It applies to an UPDATE query, which can be viewed as a SELECT to get the data and then an UPDATE to modify it. Other queries that have a shared lock on this data can run concurrently.
Exclusive (X)  Exclusive access to a resource. This ensures only one query can INSERT/DELETE/UPDATE the data at any given time. Other queries cannot access the data being modified.
Intent Shared (IS)  Indicates a shared lock has been taken at a lower level in the resource hierarchy.
Intent Exclusive (IX)  Indicates an exclusive lock has been taken at a lower level in the resource hierarchy.
Shared with Intent Exclusive (SIX)  Indicates an intent exclusive lock has been taken at a lower level in the resource hierarchy.
Schema Modification (Sch-M)  Typically, this is acquired when a query wants to modify a table.
This lock prevents other queries from accessing the table.
Schema Stability (Sch-S)  This lock is typically taken when a query needs access to metadata about a table, for example, for query compilation.
Bulk Update (BU)  Relates to bulk load operations.
Key Range  Relates to holding locks across a wider range of rows than normal.

A note about the intent locks-when a lock is taken on a relatively low level object, for example, a row or a page, related intent locks are also taken in the higher level object in the database structure hierarchy. This enables SQL Server to determine quickly, using the higher level information, if any new query will cause potential locking conflicts on the lower levels.

Locking uses resources and coordination are needed to manage any conflicts. As the number of locks taken out by a query increases, this coordination can become more difficult. In such a case, SQL Server will often escalate a lock held on a low-level resource to a higher level, for example, from a row lock to a page lock. The locks that were held at the lower level are then released. This results in the use of fewer resources but also potentially more blocking (and less concurrency), since the whole page of rows are now locked. Lock escalation typically occurs when more than 5000 locks have been taken.

Now that we've seen the database structures that can be locked and the different types of locks that can be taken out, we are now in a position to examine blocks and deadlocks.


Blocking is a consequence of locking. When a query runs, locks are taken out on resources to ensure consistency of data. However, when another query wants to access the same resources it may be blocked, leading to decreased performance, and less scalability.
It should be noted some of these blocks are recorded as waits on locked resources (see the code in chapter 6 on OS based DMVs for more details on how to view these). It is possible to use these waits to determine if locking is a consistent and important problem needing attention.
Often the terms blocking and waiting are used interchangeably. Blocking is a specify type of waiting that relates to locks. Blocking is a subset of waiting.

By default, SQL Server will wait until the blocking has finished before proceeding. If the client is Windows or web client, the blocked query typically waits a given time before failing with a timeout error.

It is also possible to record blocking that occurs for longer than a given time period by setting the 'block process
threshold' within the system configuration. This enables SQL Profiler to record details of any blocks that occur over the given wait threshold.
Additionally, it is possible to record details of blocking via the SQL Server performance counters.


Deadlocks are an extreme version of blocking, where, typically, two queries each hold a lock on a resource and each then requires the resource held by the other query. It can be thought of as circular blocking. It is possible to discover what SQL queries were involved in the deadlock by turning on trace flag 1222 either as part of the SQL Server start up script or from within SSMS, using the following command: DBCC TRACEON (1222, -1). In the latter case, the trace flag is only on until SQL Server is restarted.

If the trace flag 1222 is on when a deadlock occurs, information about the deadlock is written to the SQL Server error log. The log includes details of the individual queries involved in the deadlock, together with their stack trace, details of the resources involved in the deadlock, the transaction isolation level, the sql_handle, and plan _handle (so you can get the SQL query and the cached plan from the DMVs if needed).

Since deadlocks can occur due to scheduling conflicts, for example, when two SQL batches are accessing similar resources at the same time, it is possible to capture the error in a TRY/CATCH block and resubmit the query. An example of this is given in listing 1 below. Note, in SQL Server 2008, you can enter an initial value for a variable on the same line as its declaration; you will need to change this if you're using SQL Server 2005. Also note that
dbo.SomeRoutine should be replaced with the name of your routine.

 Listing 1 Template for handling deadlock retries  

DECLARE @CurrentTry INT = 1
DECLARE @MaxRetries INT = 3
DECLARE @Complete BIT = 0

WHILE (@Complete = 0) BEGIN
EXEC dbo.SomeRoutine
SET @Complete = 1
DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorState	INT
DECLARE @ErrorSeverity INT

SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
SET @ErrorSeverity = ERROR_SEVERITY()

IF (@ErrorNum = 1205) AND (@CurrentTry < @MaxRetries) #B BEGIN

SET @CurrentTry = @CurrentTry + 1
WAITFOR DELAY '00:00:10'

, @ErrorSeverity
, @ErrorState) END
SET @Complete = 1
RAISERROR ('An error has occurred'

#A Catch error
#B Handle deadlock
#C Raise error

In the example, the routine called dbo.SomeQuery is executed. If an error is raised within the TRY block, execution is passed to the CATCH block. Here, the error code is examined. If its value is 1205, it means a deadlock has occurred. In the case of a deadlock, the query waits a given time period, and then execution is re-executed. This is attempted a given number of times (3 in the above example). If a deadlock still occurs, a message can be logged and the code exited as an error.

Please note the above example is only a template used to indicate the flow of processing. In a real-world implementation, better logging would be required.
Every 5 seconds or so, the deadlock manager runs, looking for blocking that cannot be resolved because they are deadlocks. SQL Server chooses one of the queries to terminate and roll back; this is usually the query that is easiest to roll back-for example, the one that has done the least updates. You can influence which query is rolled back when a deadlock is detected by setting a deadlock priority indicator in the query. An example of this is: SET DEADLOCK_PRIORITY LOW. Other values include NORMAL and HIGH. In SQL Server 2008, you can use a number in the range -10 to 10 to indicate a finer-grained deadlock priority. You can also obtain details relating to deadlocks via the SQL Server performance counters.


I hope you now have a better understanding of locks, blocking, and deadlocks. Reducing both blocking and deadlocking leads to queries that have better performance, greater concurrency, and less blocking.

SQL Server DMVs in Action

Ian W. Stirk
MEAP Release: February 2010
Softbound print: Winter 2010 | 375 pages
ISBN: 9781935182733

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend