SQL Server Deadlock Troubleshooting

Original: SQL Server deadlock troubleshooting

Remember the previous customer in the use of software, there are occasional deadlock problems, because the time is uncertain, not good to reproduce the problem, at that time to solve the problem is a bit tricky.

Here is a summary of the two common ways to look at deadlocks:

The first is the graphical monitoring of:

SQL Server Profiler--and tools--when you log on in the trace properties, select such as:

The deadlock pattern heard by the supervisor

The description here is roughly: There are two processes one process ID is 96 and the other ID is 348. The system automatically killed the process id:96, preserving the transaction commit of the process id:348.

The above deadlock is due to the fact that the bulk update appears in the Pag range lock, with both processes on the same partition index resource. id96,348 requests that you want to get the update lock (U), each of the exclusive locks (x) is not released until the lock expires.

The second is the use of log tracking (errorlog)

To open the specified trace flag in a global fashion

DBCC TRACEON (1222,-1)

DBCC TRACEON (1204,-1)

Use EXEC Master. Xp_readerrorlog View the log. Because of the record deadlock information too much, put out a few key words (red Bold representation)

Deadlock encountered .... Printing Deadlock informationwait- forGraphNULLNode:1page:7:1:6229275CLEANCNT:2Mode:ix Flags:0x3GrantList3: Owner:0x00000004e99b7880 Mode:ixFLG:0x40REF:1Life:02000000 spid:219ECID:0xactLockInfo:0x0000000575c7e970SPID:219ECID:0Statement Type:UPDATELine #: -Input buf:language Event: exec Proc_pub_stockdataimportrequested by: Restype:lockowner stype:'OR'Xdes:0x0000000c7a905d30Mode:u SPID: -BatchId:0ECID: -TaskProxy: (0x0000000e440aafe0) Value:0x8d160240Cost: (0/0)NULLNode:2page:7:1:5692366CLEANCNT:2 Mode:uFlags:0x3GrantList3: Owner:0x0000000d12099b80Mode:u FLG:0x40REF:0Life:00000001SPID: -ECID:0xactLockInfo:0x000000136b4758f0spid:64ECID:0Statement Type:UPDATELine #:108 Input buf:rpc Event:Proc [Database id = 7 Object id = 907150277]

--------The Node:1 section shows several key messages:

PAGE 7:1:6229275 (Database ID 7, 1 partition, 6229275 rows)

Mode:ix Lock mode Intent exclusive lock

spid:219 Process ID

Event:exec proc_pub_stockdataimport Execution of the stored procedure name

Some key information-------the Node:2 section

PAGE 7:1:5692366 (Database ID 7, 1 partition, 5692366 rows)

Mode:u Lock Mode Update lock

RPC Event:proc Remote Call

SPID:64 Process ID

victim Resource Owner:ResType:LockOwner stype:'OR'Xdes:0x0000000c7a905d30Mode:uspid:64BatchId:0ECID: -TaskProxy: (0x0000000e440aafe0) Value:0x8d160240Cost: (0/0) Deadlock-listdeadlock victim = process956f4c8Process-listprocess ID =process956f4c8Taskpriority=0Logused=0Waitresource=PAGE:7:1:6229275Waittime=2034ownerID=2988267079Transactionname=UPDATElasttranstarted=2018-Geneva-19T13: Wu:00.360Xdes=0xc7a905d30Lockmode=U Schedulerid= -Kpid=1308Status=Suspended SPID= -Sbid=0Ecid= -Priority=0Trancount=0lastbatchstarted=2018-Geneva-19T13: -:58.033lastbatchcompleted=2018-Geneva-19T13: -:58.033 ClientApp=. Net SqlClient Data Provider hostname=VMSERVER76 Hostpid=16328Isolationlevel=read committed (2)Xactid=2988267079CurrentDb=7LockTimeout=4294967295Clientoption1=671088672Clientoption2=128056executionstackframe procname =Test.dbo.proc_CnofStock Line=108Stmtstart=9068Stmtend=9336Sqlhandle=0x03000700c503123601ba25019ca800000100000000000000UpdateDbo.pub_stockSetUpdateTime=GETDATE() fromPub_stock aJoinPub_platfromstocktemp b onA.guid=B.stockguid

From the above information can see Kill is the process ID is PROCESS956F4C8,

Process spid=64

Lockmode=u Get update lock

Isolationlevel=read committed

Executionstack Execution of heap information:

Storage Name Procname=test.dbo.proc_cnofstock

Statement Update Dbo.pub_stock Set updatetime=getdate ():

ClientApp the source of the event originated

Finally, the solution to avoid deadlock is summarized.

Access the object in the same order.

Optimize indexes, avoid full-table scans, and reduce the number of lock requests.

Avoid user interaction in a transaction.

Use the isolation level based on row versioning.

Change committed read to snapshot for transaction default isolation Level

SET TRANSACTION Isolation Level SNAPSHOT

Use Nolock to remove a shared lock, but a deadlock occurs on a U or x lock, NOLOCK does not work

Upgrade lock granularity (page lock, table lock) to block and replace deadlock

SQL Server Deadlock Troubleshooting