INF: Analyzing and Avoiding Deadlocks in SQL Server

This article was previously published under Q169960
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
Microsoft SQL Server maintains transactional integrity and databaseconsistency by using locks. SQL Server version 6.5 optionally uses row-level locking for insert operations and uses page-level locking for otheroperations. As with any relational database system, locking may lead todeadlocks between users.

For example, suppose User1 (or Connection1) has a lock on data item "A" andwants a lock on data item "B." User2 has a lock on data item "B" and nowwants a lock on data item "A." In this SQL Server scenario, either User1 orUser2 will be a deadlock victim, and the other user will be granted therequested lock.

In SQL Server, the application developer can decide which connection willbe the candidate for deadlock victim by using SET DEADLOCK_PRIORITY. If thedeveloper does not designate a priority for deadlocks, SQL Server selectsthe deadlock victim by choosing the process that completes the circularchain of locks.

Database application systems may behave differently when ported from onerelational database to another, based on the implementation of therelational database system. One of the areas to look for behavioral changesis locking. This article explains how to analyze the deadlocks in SQLServer and the techniques you can use to avoid them.
MORE INFORMATION
This article emphasizes using the output of trace flag T1204 to analyzedeadlocks. When trace flag T1204 is set, SQL Server prints informationabout the deadlock when it occurs. To use this trace flag, use thefollowing command at a command prompt to start SQL Server:
   sqlservr -c -T1204				

The trace results are sent to the console window, unless you set trace flagT3605, which sends the trace output to the error log.

Deadlocks can occur when two connections update tables in opposite order.For example, one connection inserts into table "example1" first and theninto "example2," while another connection inserts into table "example2"first and then into "example1" within a transaction. An example scenario isuseful to illustrate how to avoid deadlocks.

The following are the SQL statements used to create the table used for thisexample:
   create table example1 (column1 int, column2 char(20), column3 char(50))   go   create table example2 (column1 int, column2 char(20), column3 char(50))   go   declare @lvar int   select @lvar = 0   while @lvar < 500   begin   insert into example1 values (@lvar, 'AAA', 'CCC')   insert into example2 values (@lvar, 'AAA', 'CCC')   select @lvar = @lvar + 1   end   go   create unique clustered index ex1ind1 on example1 (column1, column2)   with fill factor = 90, PAD_INDEX   go   create unique clustered index ex2ind1 on example2 (column1, column2)   with fill factor = 90, PAD_INDEX   go				

Example 1: Table Insertions in Opposite Order

In this example, two tables were inserted in opposite order and a deadlockoccurred. Deadlocks can also occur when two or more connections performupdates or deletes on tables in opposite order.
   Connection1 > BEGIN TRANSACTION   Connection2 > BEGIN TRANSACTION   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')   Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')   Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')				

At this point, Connection1 may block Connection2 because the rowConnection2 is inserting may be on the same page where Connection1 hasalready inserted a row and is holding a lock.
   Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')				

At this point, Connection2 may block Connection1, because the rowConnection1 is inserting may be on the same page where Connection2 hasalready inserted a row and is holding a lock. This causes a deadlock.

The following is the output for trace flag 1204 when the deadlock occurred:
97/04/20 11:51:57.88 spid13   *** DEADLOCK DETECTED with spid 14 ***   spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:     EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES (100,     'AAAA', 'CCC')   spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:     EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (200,   'AAAB', 'CCC')   VICTIM: spid 13, pstat 0x0000 , cputime 30				

Each line of the deadlock trace can tell users more about a deadlock.Connection1 is spid 13 and Connection2 is spid 14 (you can determine thespid associated with a connection by using the sp_who system storedprocedure).
   >> 97/04/20 11:51:57.88 spid13   *** DEADLOCK DETECTED with spid 14 ***   The deadlock was detected between spid 13 and spid 14.   >> spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:   >>   EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1   >>   pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES   (100, 'AAAA', 'CCC')				

Spid 13 was requesting EX_PAGE lock and was blocked by spid 14, whichalready has EX_PAGE lock for page 0x188 on table example2 in dbid 6. Thelock is held on the page belonging to clustered index.
      Indid Value         Description-------------------------------------         0                Data page if there is no clustered index, or the                          leaf page of a clustered index if there is one         1                Non-leaf page of the clustered index page       255                Text/image page    Any other value       Non-clustered secondary index				

The current command executed by spid 13 is an INSERT and the trace givespart of the input buffer.
   >> spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:   >>   EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1   >>   pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES   (200, 'AAAB', 'CCC')				

Spid 14 is waiting for EX_PAGE lock and is being blocked by spid 13, whichalready holds EX_PAGE lock on the same page.
   >> VICTIM: spid 13, pstat 0x0000 , cputime 30   SQL Server has chosen spid 13 as the deadlock victim.				

The following is an explanation of what the various locks mean in thetrace:

SH_INT and EX_INT
Intent locks that are taken on a higher-level item (for example, a table)before lower-level locks (for example, a page) can be taken, because thelock manager is unaware of the relationship between different types ofitems (in this case, pages and tables). If an EX_INT lock was not taken onthe table before taking EX_PAG locks on the pages, another user could takean EX_TAB lock on the same table and the lock manager would not know that aconflict existed. Currently, SQL Server has intent locks only on tables.There are two kinds of intent locks: shared (SH_INT) and exclusive (EX_INT)locks.

EX_PAGE
This is an exclusive page lock that is taken when a page is updated due toa DELETE, UPDATE, or INSERT statement with insert row-level locking (IRL)disabled.

UP_PAGE
This is an update page lock that is taken in place of a shared-page lockwhen a page is scanned and the optimizer knows that the page will beupdated (or the UPDLOCK hint is used).

PR_EXT, NX_EXT, UPD_EXT, and EX_EXT
These locks are taken when allocating or deallocating disk space. UPD_EXTis taken when allocating or deallocating a page from an existing extent andthe others are used when allocating or deallocating entire extents.

IX_PAGE and LN_PAGE
These are IRL locks. IX_PAGE is an intent-to-do-row-locking lock on a page.LN_PAGE is taken when a page on which IRL is being done needs to be split.

RLOCK and XRLOCK
These short-term locks are taken when traversing an index b-tree. There aretwo types of this kind of lock: shared (RLOCK) and exclusive (XRLOCK).Shared locks are taken during scan, while exclusive locks are taken onindex pages during an update.

EX_TAB
This is an exclusive table lock that occurs when the SQL Server optimizerdetermines that a table scan is the most efficient way to solve an updatequery (for example, when there are no indexes on a table). EX_TAB locksalso appear when you lock the table with TABLOCKX hint or when SQL Serverescalates the page locks on a table to a table lock.

SH_TAB
This is a shared table lock that is used when the optimizer assumes thatmost of the table will be scanned (or page locking escalates) or theTABLOCK hint is used.

The previous deadlock example can be avoided if the two connections updatetables in the following sequence:
   Connection1 > BEGIN TRANSACTION   Connection2 > BEGIN TRANSACTION   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')   Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')   Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')   Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')				

Example 2: Insertions to Different Parts of the Same Table

This deadlock can also occur when two connections insert into differentparts of the same table in opposite order when rows share pages. Forexample:
   Connection1 > BEGIN TRANSACTION   Connection2 > BEGIN TRANSACTION   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')   Connection1 > INSERT INTO example1 VALUES (400, 'AAAA', 'CCC')				

In this example table, there is a clustered index on the first column ofthe example1 table. Rows with the same values for the first column willtend to fall on the same page. In the example, the second row inserted byConnection1 will probably fall on the same page as the first row insertedby Connection2, because they both have a clustered index value of 400. Thiscauses Connection2 to block Connection1.
   Connection2 > INSERT INTO example1 VALUES (100, 'AAAB', 'CCC')				

Now Connection2 may also be blocked by Connection1, leading to a deadlock.The following is the deadlock trace:
   97/04/20 12:56:01.40 spid16   *** DEADLOCK DETECTED with spid 15 ***   spid 16 requesting EX_PAGE (waittype 0x8005), blocked by:     EX_PAGE: spid 15, dbid 6, page 0x2c5, table example1, indid 0     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (100,   'AAAB', 'CCC')   spid 15 waiting for EX_PAGE (waittype 0x8005), blocked by:     EX_PAGE: spid 16, dbid 6, page 0x8bd, table example1, indid 0     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (400,   'AAAA', 'CCC')   VICTIM: spid 16, pstat 0x0000 , cputime 130				

The spid 16 request for EX_PAGE lock for page 0x2c5 is blocked by spid 15,which already holds EX_PAGE lock for page 0x2c5 after it did the firstinsert. And spid 15 also got blocked by spid 16 on waiting for a EX_PAGElock for page 0x8db leading to deadlock.

This deadlock can be avoided by using the following command to enable IRLfor table example1:
   sp_tableoption 'example1', 'insert row lock', true				

Example 3: Insertions Using IRL

IRL allows two or more users to share a page when they do only insertoperations, which often results in better throughput. However, enabling IRLwill not always reduce deadlocks. In some cases, IRL may introducedeadlocks.
   Connection1 > BEGIN TRANSACTION   Connection2 > BEGIN TRANSACTION   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')   Connection2 > INSERT INTO example1 VALUES (105, 'AAAB', 'CCC')				

With IRL enabled, both connections will hold an IX_PAGE lock on the pagecontaining the two new rows. If IRL was disabled, Connection1 would haveacquired an EX_PAGE lock, and Connection2 would have been blockedimmediately.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105   and column2 = 'AAAB'				

At this point, Connection2 needs an exclusive page lock to do an UPDATEstatement, which is incompatible with Connection1's IX_PAGE lock.Therefore, Connection2 will wait.
   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 100   and column2 = 'AAAA'				

Now Connection1 may be blocked by Connection2, leading to a deadlock. Thefollowing is the deadlock trace:
   97/04/20 15:13:50.07 spid17   *** DEADLOCK DETECTED with spid 18 ***   spid 17 requesting UP_PAGE (waittype 0x8007), blocked by:     IX_PAGE: spid 18, dbid 6, page 0x2c5, table example1, indid 0     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCCA' where column1 = 100 and column2 = 'AAAA'   spid 18 waiting for UP_PAGE (waittype 0x8007), blocked by:     IX_PAGE: spid 17, dbid 6, page 0x2c5, table example1, indid 0     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCCB' where column1 = 105 and column2 = 'AAAB'   VICTIM: spid 17, pstat 0x0000 , cputime 20				

Spid 17 (connection one) is waiting for an UP_PAGE lock, which is the firststep to getting an exclusive page lock. It is being blocked by spid 18,which holds IX_PAGE lock on page 0x2c5. Spid 18 is waiting for UP_PAGE lockon the same page, and is being blocked by IX_PAGE lock held by spid 17.This leads to a deadlock because IX_PAGE lock is sharable, whereas UP_LOCKis not. During the first inserts, both the spids got IX_PAGE lock on thesame page, and later they tried to upgrade the lock to UP_PAGE lock, whichis not possible because UP_PAGE lock is exclusive.

The one way to avoid the deadlock is to insert the updated value directlyinto the table instead of inserting and then updating the row in the sametransaction. If this is not possible, using the following command todisable IRL will help to avoid deadlock:
   sp_tableoption 'example1', 'insert row lock', false				

Example 4: Insertions to Rows on the Same Page

A deadlock may also result when the rows the two spids are working on aredifferent but belong to the same page.
   Connection1 > BEGIN TRANSACTION   Connection2 > BEGIN TRANSACTION   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 405   and column2 = 'AAAA'				

At this point, Connection1 may be blocked by Connection2. This situationmay occur because Connection1 wants to update a row in a page whereConnection2 has already inserted a row.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105   and column2 = 'AAAB'				

At this point, Connection2 may also be blocked by Connection1, which willlead to a deadlock. This situation may occur when Connection2 wants toupdate a row in a page where Connection1 has inserted a row. The followingis the deadlock trace:
   97/04/20 15:48:21.18 spid20   *** DEADLOCK DETECTED with spid 19 ***   spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:     EX_PAGE: spid 19, dbid 6, page 0x2c4, table example1, indid 0     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCCB' where column1 = 105 and column2 = 'AAAB'   spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:     EX_PAGE: spid 20, dbid 6, page 0xc48, table example1, indid 0     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCCA' where column1 = 405 and column2 = 'AAAA'   VICTIM: spid 20, pstat 0x0000 , cputime 60				

This deadlock can be avoided by spreading out the rows over differentpages. The one method to do this is to re-create the clustered index onthis table with a large fill factor. The following is a statement thatcreates a clustered index with a fill factor of 50 percent:
   create unique clustered index ex1ind1 on example1 (column1, column2)   with fill factor = 50, PAD_INDEX				

This statement creates the clustered index leaving half of the pages empty,including the non-leaf levels of the clustered index (because of thePAD_INDEX option). The table occupies double the actual size, and thenumber of rows per page are half of what they were.

The fill factor is not maintained on a table; the table is re-organizedwith the specified fill factor only during index creation time. Over time,the rows per page will change from the fill factor specified during indexcreation. When this occurs, it may be a good idea to re-create theclustered index with the desired fill factor.

Another solution to avoid the previous deadlock situation is to pad thetable with dummy columns (for example, dummy1 char(255)). This increasesthe size of the row and leads to fewer rows per page (as few as one row perpage). Because this type of padding is maintained over time, you do notneed to re-create the clustered index to maintain the padding (though youmay want to re-create the clustered index for other reasons). Thedisadvantage of this technique is that storage space is wasted on dummyfields.

Example 5: Padding Rows

Padding rows leads to fewer rows per page (hence fewer deadlocks), but itwill not completely eliminate deadlocks.

In this example table, example1 is padded to occupy one row per page. Thefollowing are the statements used to create the table for this example:
   create table example1 (column1 int, column2 char(20), column3 char(50),   dummy_column4 char (255), dummy_column5 char (255), dummy_column6 char   (255))   go   create unique index ex1ind5 on example1 (column3, column2, column1,   dummy_column4, dummy_column5, dummy_column6) with fill factor = 85   go   Connection1 > BEGIN TRANSACTION   Connection2 > BEGIN TRANSACTION   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC', ' ', ' ',   ' ', ' ')   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC', ' ', ' ',   ' ', ' ')   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 401   and column2 = 'AAAA'				

At this point, Connection1 is blocked by Connection2 while updating therow. Because SQL Server must maintain page-chain pointers, it locks theprevious page, the next page, and the page that is being updated. BecauseConnection2 holds a lock on the previous page, Connection1 must wait untilConnection2 commits the transaction.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 101   and column2 = 'AAAB'				

At this point, Connection2 is blocked by Connection1 because it must lockthe previous page, which is currently locked by Connection1. The result isa deadlock. The following is the deadlock trace:
   spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:     EX_PAGE: spid 19, dbid 6, page 0x12b5, table example1, indid 0     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCCB' where column1 = 101 and column2 = 'AAAB'   spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:     EX_PAGE: spid 20, dbid 6, page 0x1531, table example1, indid 0     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCCA' where column1 = 401 and column2 = 'AAAA'   VICTIM: spid 20, pstat 0x0000 , cputime 300				

This deadlock can be avoided by inserting dummy rows between the rows thatare being inserted, updated, or deleted. For example, if Connection1 works(inserts, updates, or deletes) with row pk = 1 and Connection2 works withrow pk = 5, inserting a row between these two rows (such as a rowcontaining pk = 3) will avoid deadlocks. This method also increases thesize of the table, but may be the best solution for those queue tablescritical to the application.

Example 6: Nonclustered Indexes

In some cases, non-clustered secondary indexes may introduce deadlocks. Inthis example, the maintenance of the secondary index introduces deadlock.

The following is the statement used to create the secondary index used inthis example:
   create index ex1ind2 on example1 (column3) with fill factor = 90,   PAD_INDEX   Connection1 > BEGIN TRANSACTION   Connection2 > BEGIN TRANSACTION   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCBA', ' ', '   ', ' ', ' ')   Connection2 > INSERT INTO example1 VALUES (300, 'AAAB', 'CCCZ', ' ', '   ', ' ', ' ')   Connection2 > UPDATE example1 SET column3 = 'CCBA' where column1 = 105				

At this point, Connection2 may be blocked by Connection1 becauseConnection1 may be holding a lock on the secondary non-clustered index pagewhere Connection2 needs to update.
   Connection1 > UPDATE example1 SET column3 = 'CCCZ' where column1 = 305				

At this point, Connection1 may be blocked by Connection2, resulting in adeadlock. This situation can happen when Connection1 is waiting for a lockto update the non-clustered secondary index where Connection2 has alreadyinserted and holds a lock on that page. The following is the deadlock tracefor this deadlock example:
   97/04/20 19:05:38.75 spid11   *** DEADLOCK DETECTED with spid 12 ***   spid 11 requesting EX_PAGE (waittype 0x8005), blocked by:     EX_PAGE: spid 12, dbid 6, page 0x112f, table example1, indid 0x2     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCCZ' where column1 = 305   spid 12 waiting for EX_PAGE (waittype 0x8005), blocked by:     EX_PAGE: spid 11, dbid 6, page 0x1108, table example1, indid 0x2     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =   'CCBA' where column1 = 105   VICTIM: spid 11, pstat 0x0000 , cputime 50				

This deadlock can be avoided by dropping the secondary index. It is notpossible to pad the index to contain one row per page, so this situationcan be avoided only by eliminating the non-clustered secondary index or bymodifying the application.

Deadlocks may occur with more than two connections, in which case thedeadlock trace lists the spids involved in the deadlock and also theconflicting locks. Deadlocks may occur with RLOCK and XRLOCK locks, whichare acquired during index traversing. Deadlocks may also occur because ofextent locks (PR_EXT, NX_EXT, UPD_EXT & EX_EXT).

For additional information about analyzing deadlocks, you can enable thefollowing trace flags:

T1200
Prints all of the lock request/release information when it occurs, whethera deadlock is involved or not. This is expensive in terms of performance,but it can be useful for analysis.

T1206
Prints all of the locks held by participating spids in the deadlock.

T1208
Prints the host name and program name supplied by the client. This can helpidentify a client involved in a deadlock, assuming the client specifies aunique value for each connection.
Properties

Article ID: 169960 - Last Review: 12/04/2015 17:12:24 - Revision: 3.0

Microsoft SQL Server 6.5 Standard Edition

  • kbnosurvey kbarchive kbhowto kbusage KB169960
Feedback