Typical method of update that causes SQL Server deadlock (reproduced)

This article is reproduced articles, the description is very good, has not been verified.

I've recently encountered a deadlock problem that seems strange and interesting to analyze. This deadlock seems to be hard to understand. In the analysis process, a lot of typical methods of parsing SQL Server deadlock are used. Documenting the entire analysis process is significant.

Steps to reproduce the problem:

After refining, the steps to reproduce the problem are very simple and can be reproduced easily on SQL 2008.

1. First, create a table with a clustered index and two non-clustered index.

CREATE TABLE TT (ID int identity primary key,a char), b char ($), D varchar (max))

Go

CREATE INDEX IX_A_BC on TT (a) include (d)

CREATE INDEX IX_B_CD on TT (b) include (d)

2. Insert 10,000 records into this table.

INSERT INTO TT Select NEWID (), ' BBB ', ' DDD '

Go 10000

3. Query a record to find the value of its a field. (This is the case of the tenth article)

SELECT * FROM TT where ID = 10

Suppose we get the value of the A field as ' Ef211985-ea72-4a40-81da-0aab076e7aa3 '. (This is a random value, and each test will be different.) ）

4. Now follow this value to update the table repeatedly. If two connections are running at the same time, a deadlock will occur. (Test 1)

While 1 =1

Update TT with (rowlock) set d= ' CD '

where a= ' Ef211985-ea72-4a40-81da-0aab076e7aa3 '

5. However, if you remove the include (d) from the two non-clustered index and then run the same UPDATE statement loop, the deadlock will not occur. (Test 2)

Drop INDEX IX_A_BC on TT

Drop INDEX IX_B_CD on TT

CREATE INDEX IX_A_BC on TT (a) include (d)

CREATE INDEX IX_B_CD on TT (b) include (d)

6. Or change the field type of the D field from varchar (max) to varchar (200) and the deadlock will not occur. (Test 3)

Drop INDEX IX_A_BC on TT

Drop INDEX IX_B_CD on TT

ALTER TABLE TT ALTER COLUMN D varchar (200)

CREATE INDEX IX_A_BC on TT (a) include (d)

CREATE INDEX IX_B_CD on TT (b) include (d)

Problem analysis Steps

Why do two identical update statements deadlock with each other? Why is it that an include option is removed from the index, or a data type is changed so that it does not deadlock? To answer this question, we must first carefully analyze how the deadlock occurred.

There are two ways to understand the immediate cause of deadlocks in SQL Server: (1) Collect SQL Trace. (2) Turn on the 1222 switch. Because problems can be reliably reproduced in a test environment, we can gather as much information as possible and use both methods.

First we use the following script to open the 1222 switch.

DBCC TRACEON (1222,-1)

Then, in the connection running the UPDATE statement, run the following script to learn about the connected SPID. We can use the filter for SQL Trace later.

SELECT @ @spid

Suppose we get the result that one is 54 and one is 60.

Now let's open the SQL Profiler, connect to SQL Server, create a new trace, tick "show All Events" and "Show all Columns" when selecting an event, then select the events below locks.

Under TSQL, choose these events.

Click on column Filters to set up a link on the SPID that only tracks the SPID to 54,60,6 and 20.

Then run the update loop in two connections to let the problem happen.

We then run the sp_readerrorlog directive. You can find the following record about the last deadlock in SQL Errorlog. The record is long, the key place I marked with a yellow background.

Deadlock-list

Deadlock victim=process5e27708

Process-list

Process id=process5e27708 taskpriority=0 logused=0 waitresource=key:8:72057594066108416 (ef8a9edf5a1e) waittime=1750 ownerid=11864845 transactionname=update lasttranstarted=2011-12-01t16:41:39.540 XDES=0xca9a7950 LockMode=X schedulerid=4 kpid=6380 status=suspended spid=60sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-12-01T16 : 41:39.407 lastbatchcompleted=2011-12-01t16:37:13.077 lastattention=2011-12-01t16:20:33.170 Clientapp=Microsoft SQL Server Management studio-query hostname=aobai hostpid=4672 Loginname=fareast\haiwxu Isolationlevel=read committed ( 2) xactid=11864845 currentdb=8 locktimeout=4294967295 clientoption1=671090784 clientoption2=390200

Executionstack

Frame Procname=adhoc line=2 stmtstart=24 stmtend=188 sqlhandle=0x020000001cf53c334179280da1f74e588b7179146497bde9

8000), @2 varchar (8000)) UPDATE [TT] with (rowlock) set [d] = @1 WHERE [a][email protected]

Frame Procname=adhoc line=2 stmtstart=24 stmtend=188 sqlhandle=0x02000000a1ec593be12cd271e013ded3a22a397943e8f879

Update TT with (rowlock) set d= ' CD '

where A= ' Ef211985-ea72-4a40-81da-0aab076e7aa3

Inputbuf

While 1 =1

Update TT with (rowlock) set d= ' CD '

where a= ' Ef211985-ea72-4a40-81da-0aab076e7aa3 '

Process Id=process5e09dc8 taskpriority=0 logused=0 waitresource=key:8:72057594065518592 (d08358b1108f) waittime=1750 ownerid=11864847 transactionname=update lasttranstarted=2011-12-01t16:41:39.540 XDES=0xcb98d3c0 LockMode=U Schedulerid=1 kpid=4200 status=suspended spid=54sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-12-01T16 : 41:37.473 lastbatchcompleted=2011-12-01t16:37:19.577 lastattention=2011-12-01t16:37:19.577 Clientapp=Microsoft SQL Server Management studio-query hostname=aobai hostpid=4672 Loginname=fareast\haiwxu Isolationlevel=read committed ( 2) xactid=11864847 currentdb=8 locktimeout=4294967295 clientoption1=671090784 clientoption2=390200

Executionstack

Frame Procname=adhoc line=2 stmtstart=24 stmtend=188 sqlhandle=0x020000001cf53c334179280da1f74e588b7179146497bde9

8000), @2 varchar (8000)) UPDATE [TT] with (rowlock) set [d] = @1 WHERE [a][email protected]

Frame Procname=adhoc line=2 stmtstart=24 stmtend=188 sqlhandle=0x02000000a1ec593be12cd271e013ded3a22a397943e8f879

Update TT with (rowlock) set d= ' CD '

where A= ' Ef211985-ea72-4a40-81da-0aab076e7aa3

Inputbuf

While 1 =1

Update TT with (rowlock) set d= ' CD '

where a= ' Ef211985-ea72-4a40-81da-0aab076e7aa3 '

Resource-list

Keylock hobtid=72057594066108416 dbid=8 objectname=perfanalysis.dbo.tt indexname=ix_a_bcid=lock5e66c00 Mode=U associatedobjectid=72057594066108416

Owner-list

Owner Id=process5e09dc8 Mode=u

Waiter-list

Waiter id=process5e27708 mode=x requesttype=wait

Keylock hobtid=72057594065518592 dbid=8 objectname=perfanalysis.dbo.tt indexname=pk__tt__3213e83f10e07f16 id= Lock6648e80 mode=x associatedobjectid=72057594065518592

Owner-list

Owner id=process5e27708 Mode=x

Waiter-list

Waiter Id=process5e09dc8 Mode=u requesttype=wait

As you can see from the Executionstack, the deadlock does occur on two update statements. Connection 1 (spid=54,process id=process5e09dc8), another connection 2 (spid=60,process id=process5e27708).

Analysis of the contents of the resource-list, you can know the direct cause of the deadlock occurs, is the connection 1 on the IX_A_BC index holds a u-key lock, to apply for the U-key lock on the index PK__TT__3213E83F10E07F16. While connection 2 has an X lock on the PK__TT__3213E83F10E07F16, an X lock is requested on the IX_A_BC index. (The deadlock graph event in SQL Trace can also tell you similar information.) But 1222 of the output may be more comprehensive. ）

Why is this happening? 1222 of the output has not been able to tell us more information. We need to analyze the lock application and release records in SQL Trace to see what actually happened.

Let's look at a successful update,sql how the server is applying and releasing the lock resource. The two event lock:acquired and lock:released can tell us. However, the output of these two event is more difficult to read, and you need to drag the fields mode, ObjectID, ObjectID2 and type to the front.

The value in the ObjectID2 field, which is the hobtid in the 1222 output. The specific value and the table, index name of the control relationship, can be checked with the following script.

Select O.name, I.name, I.type from

sys.indexes i inner join sys.objects o on i.object_id = o.object_id

INNER JOIN sys.partitions p on p.index_id = i.index_id and p.object_id = i.object_id

where p.partition_id = 72057594065518592

--72057594065518592 is one of the ObjectID2 values.

With this script above, we can know the correspondence between the ObjectID2 and the actual index appearing in SQL Trace:

72057594065518592:pk__tt__3213e83f10e07f16

72057594066108416:ix_a_bc

72057594066173952:ix_b_cd

So the order in which SQL Server locking and locks at the key level is:

Index name	Lock type	application/Release	Stage	Connection 1 (SPID 54)	Connection 2 (SPID 60)
Ix_a_bc	U	Application	1	J	J
Pk__tt__3213e83f10e07f16	U	Application	1	L	J
Pk__tt__3213e83f10e07f16	X	Application	1		J
Ix_a_bc	U	Release	1		J
Ix_a_bc	X	Application	2		L
Ix_b_cd	X	Application	2
Ix_b_cd	X	Release	2
Ix_a_bc	X	Release	2
Pk__tt__3213e83f10e07f16	X	Release	2

As you can see, the UPDATE statement appears to be executed in two steps. The first step of SQL is to update the log body (PK__TT__3213E83F10E07F16) by indexing IX_A_BC first. The second step of SQL Server is to update the data on the two non-clustered index because the two indexes have the D field that has the modification in place. Finally, release the three x locks. In the previous example of the deadlock, Connection 1 is running to the first step, while connection 2 runs to the second step. Since the connection 2 in the completion of the first step after the U lock on the IX_A_BC released, so that the connection 1 can begin its first step of the operation, the application to the IX_A_BC u lock. And the connection 2 will apply for IX_A_BC on the X lock, both sides of each other blocked, the deadlock happened.

Why remove the Include field on the non-clustered index to solve the deadlock problem (test 2)? We'll use SQL Trace to track the execution of the update after the data type modification. Information is collected and analyzed in the same way as before.

Index name	Lock type	application/Release
Ix_a_bc	U	Application
Pk__tt__3213e83f10e07f16	U	Application
Pk__tt__3213e83f10e07f16	X	Application
Ix_a_bc	U	Release
Pk__tt__3213e83f10e07f16	X	Release

What happens if you change the data type of the D field from varchar (max) to varchar (200) (Test 3)? Tracked in the same way.

Index name	Lock type	application/Release
Ix_a_bc	U	Application
Pk__tt__3213e83f10e07f16	U	Application
Pk__tt__3213e83f10e07f16	X	Application
Ix_a_bc	U	Release
Pk__tt__3213e83f10e07f16	X	Release

We can see that, regardless of the method, the update has been simplified from two steps into one step. SQL Server simply finds the body (PK__TT__3213E83F10E07F16) by index IX_A_BC and updates it. There is no need to do the second step. In this way, the deadlock will not happen.

So why is there such a change? To understand the behavior of SQL Server Gathan locks, we must parse the execution plan of the statement. Here, I use the "SET STATISTICS Profile" method in front of the UPDATE statement to get a text-type output execution plan.

Set STATISTICS PROFILE on

Go

Update TT with (rowlock) set d= ' CD '

where a= ' Ef211985-ea72-4a40-81da-0aab076e7aa3 '

Test 1: (d varchar (max), included in index, deadlock)

Test 2: (d varchar (max), no included, no deadlock in index)

Test 3: (d varchar (200), with included in index, no deadlock)

These three implementation plans are a good illustration of the problem. In the first execution plan, there are three index Update. This also explains why SQL has to apply for 3 x locks. In the second execution plan, there is only one index Update. This is because the D field is not included in the two non-clustered index, and SQL Server only needs to update clustered index. The third plan of execution is very interesting. It has only one update, but contains three object. In other words, SQL has updated the three index together in one step. So the test three is a step done, there will be no deadlock.

Analysis here, the problem has been relatively clear. We can draw the following conclusions:

1. The number of locks, and the order of SQL Server, is related to the execution plan of the statement.

2. The more indexes on a table and the more complex the data type, the more complex the execution plan is, resulting in an increased chance of encountering blocking or deadlocks.

3. To eliminate deadlocks, you can first check the execution plan of the statement, starting with tuning the execution plan, and booting SQL Server to request a lesser number of locks.

For this deadlock case itself, it is not appropriate to include varchar (max) in an index. This greatly increases the complexity of the index and also increases the maintenance cost of SQL Server. The better way to solve this index is to remove the field D from the index, or change it to a varchar type with a suitable length limit.

Typical method of update that causes SQL Server deadlock (reproduced)