A troubleshooting procedure for SQL Server 2005+SP3 deadlock

There are puber in SQL Server Edition of a post server2005+sp3 deadlock problem, due to the recent study of SQL Server some of the problems, out of interest decided to try to help the pubber.

Problem Description:

My program is multithreaded and has a maximum of 20 threads.

20 threads insert into a table at the same time, but the records between the threads cannot be duplicated (the primary key is certainly different), but the same program does not even work with Oracle.

The high man to point out a Ming road ah.

Problem Analysis:

Because only these simple introductions make it difficult to locate and diagnose the problem, I started by recommending that you enable his 1204 and 1222 tracking flags to be listed in detail by logging the deadlock description, or by using SQL Server Profiler custom event tracking.

He responded to SQL Server Profiler's custom event tracking deadlock graph to get a description of the health lock

The following is a deadlock diagram:

The following is SQL Server log information:

Here is the information in the log viewer, which should correspond to the used log 1160 and log 1084.

----------------------------------------------------------------------------

Date 2011-1-14 11:15:11

Log SQL Server (current-2011-1-14 11:15:00)

SOURCE Spid21s

News

Process Id=processb793d8 taskpriority=0 logused=1084 waitresource=key:9:72057594058113024 (C601CDD76F20) waittime=49 ownerid=944254 Transactionname=insert lasttranstarted=2011-01-14t11:15:06.500 XDES=0x8ee8a88 LockMode=S schedulerid=2 kpid=7384 status=suspended spid=69 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2011-01-14T11 : 15:06.500 lastbatchcompleted=2011-01-14t11:15:06.497 clientapp=jtds hostname=win7-pc hostpid=123 LOGINNAME=IPCC ISO Lationlevel=read committed (2) xactid=944254 currentdb=9 locktimeout=4294967295 clientoption1=671219744 clientoption2 =128056

-----------------------------------------------------------------------------------

Date 2011-1-14 11:15:11

Log SQL Server (current-2011-1-14 11:15:00)

SOURCE Spid21s

News

Process id=processaea988 taskpriority=0 logused=1160 waitresource=key:9:72057594058113024 (3002597d4e10) waittime= 4984 ownerid=944255 transactionname=insert lasttranstarted=2011-01-14t11:15:06.500 XDES=0x7e3ea88 LockMode=S Schedulerid=1 kpid=4208 status=suspended spid=79 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2011-01-14T11 : 15:06.500 lastbatchcompleted=2011-01-14t11:15:06.497 clientapp=jtds hostname=win7-pc hostpid=123 LOGINNAME=IPCC ISO Lationlevel=read committed (2) xactid=944255 currentdb=9 locktimeout=4294967295 clientoption1=671219744 clientoption2 =128056

Preliminary analysis of the above logs and graphics:

The surface hint is that a shared and exclusive lock conflicts with each other, i.e. read-write and read-write conflicts

Session1 Session2

Shared locks

Exclusive lock

Exclusive lock

Shared locks

After questioning and analysis, it really happened on the primary key, I suspect the trigger problem, the trigger in the execution time is too long, or in the program in the process of primary key generation, and repeatedly read from the table. So you want to learn more about table structure, trigger scripts, and scripts generated by primary keys.

Problem solving:

After a few rounds of tossing, he finally found the problem, "it was someone else who set up the trigger in this table, I don't know, and the trigger also accesses the table." ", remove the trigger, and the problem disappears.

Deadlock common scene is read-write conflict, circular reference will cause mutual resource rejection, and resources are not released, the proposed solution is to shorten the transaction time, reduce the circular reference and so on.