Sharing the whole process of deadlock checking in SQL Server

Time:2019-4-7

Preface

I remember that in the past, customers occasionally encountered deadlock problems when using software, because the time of occurrence is uncertain, and it is difficult to reproduce the problem. At that time, it was a bit difficult to solve the problem.

Four necessary conditions for deadlock:

  • Mutual exclusion: Resources cannot be shared and can only be used by one process.
  • Hold and wait: Processes that have received resources can apply for new resources again.
  • No pre-emption: The allocated resources cannot be forcibly deprived from the corresponding process.
  • Circular wait condition: Several processes in the system form a loop in which each process is waiting for resources being occupied by adjacent processes.

Here are two common ways to view deadlocks:

The first is graphical monitoring.

After login, select the following figure in the trace property:sql server – > tool – > sql server profiler:

Sharing the whole process of deadlock checking in SQL Server  

The deadlock patterns monitored are as follows

Sharing the whole process of deadlock checking in SQL Server

Sharing the whole process of deadlock checking in SQL Server   

The description here is roughly as follows: there are two processes, one process ID is 96, the other ID is 348. The system automatically kills the process ID: 96 and retains the transaction Commit of the process ID: 348.

The above deadlock is due to PAG range locks in batch updates, and both processes are indexed on the same partition resource. ID96, 348 all request to acquire the update lock (U), each occupying exclusive lock (x) is not released until the lock timeout.

The second is using errorlog

Open the specified trace tag globally

        DBCC TRACEON(1222,-1)

       DBCC TRACEON(1204,-1)

UseEXEC master..xp_readerrorlogView the log. Because there is too much deadlock information recorded, put a few highlights (red bold)


Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1 
PAGE: 7:1:6229275 CleanCnt:2 Mode:IX Flags: 0x3
Grant List 3:
Owner:0x00000004E99B7880 Mode: IX Flg:0x40 Ref:1 Life:02000000 SPID:219 ECID:0 XactLockInfo: 0x0000000575C7E970
SPID: 219 ECID: 0 Statement Type: UPDATE Line #: 84
Input Buf: Language Event: exec proc_PUB_StockDataImport
Requested by: 
ResType:LockOwner Stype:'OR'Xdes:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy:(0x0000000E440AAFE0) Value:0x8d160240 Cost:(0/0)
NULL

Node:2 
PAGE: 7:1:5692366 CleanCnt:2 Mode:U Flags: 0x3
Grant List 3:
Owner:0x0000000D12099B80 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:64 ECID:0 XactLockInfo: 0x000000136B4758F0
SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 108
Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 907150277]

Some key information shown in the node:1 section:

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

Mode: IX Lock Mode Intentional Exclusive Lock

SPID: 219 Process ID

Event: The name of the stored procedure executed by exec proc_PUB_StockDataImport

Some key information shown in 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:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy:(0x0000000E440AAFE0) Value:0x8d160240 Cost:(0/0)
deadlock-list
deadlock victim=process956f4c8
process-list
process id=process956f4c8 taskpriority=0 logused=0 waitresource=PAGE: 7:1:6229275 waittime=2034 ownerId=2988267079 transactionname=UPDATE 
lasttranstarted=2018-04-19T13:54:00.360 XDES=0xc7a905d30 lockMode=U schedulerid=24 kpid=1308 status=suspended spid=64 sbid=0 ecid=59 priority=0 trancount=0 
lastbatchstarted=2018-04-19T13:53:58.033 lastbatchcompleted=2018-04-19T13:53:58.033 clientapp=.Net SqlClient Data Provider hostname=VMSERVER76 hostpid=16328 
isolationlevel=read committed (2) xactid=2988267079 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=Test.dbo.proc_CnofStock line=108 stmtstart=9068 stmtend=9336 sqlhandle=0x03000700c503123601ba25019ca800000100000000000000
update dbo.pub_stock
set UpdateTime=GETDATE()
from pub_stock a
join PUB_PlatfromStocktemp b on a.GUID=b.StockGuid

From the above information, we can see that the process ID is process956f4c8.

Process SPID = 64

    lockMode=UGet the update lock

    isolationlevel=read committed

The heap information executed by executionStack:

Storage nameprocname=Test.dbo.proc_CnofStock

Statementupdate dbo.pub_stock set UpdateTime=GETDATE()   ..

Clientapp

Finally, we summarize the solutions to avoid deadlock.

Access objects in the same order.

Optimize index to avoid full table scanning and reduce the number of lock applications.

Avoid user interaction in transactions.

Use line version-based isolation levels.

Change committed read to snapshot at transaction default isolation level

         SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Use nolock to remove shared locks, but when deadlocks occur on u locks or X locks, nolock does not work

Upgrade lock granularity (page lock, table lock) to block instead of deadlock

summary

Above is the whole content of this article. I hope that the content of this article has a certain reference learning value for everyone’s study or work. If you have any questions, you can leave a message to exchange. Thank you for your support to developpaer.