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.
The deadlock patterns monitored are as follows
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
EXEC 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
The heap information executed by executionStack:
update dbo.pub_stock set UpdateTime=GETDATE() ..
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
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.