Shulou(Shulou.com)05/31 Report--

How to troubleshoot deadlocks in sql server? in view of this problem, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Four necessary conditions for deadlocks:

Mutual exclusion condition (Mutual exclusion): resources cannot be shared and can only be used by one process. Request and retention conditions (Hold and wait): processes that have already received resources can reapply for new resources. Non-deprivation condition (No pre-emption): allocated resources cannot be forcibly deprived from the corresponding process. Loop wait condition (Circular wait): several processes in the system form a loop in which each process is waiting for the resources being consumed by neighboring processes.

Here are two common ways to view deadlocks:

The first is graphical monitoring:

Sqlserver-- > tools-- > after logging in to sqlserver profiler, select the following in the tracking properties:

The deadlock pattern heard by the monitor is as follows

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

The above deadlock is due to the occurrence of PAG range locks in batch updates, and both processes are on the same partition index resource. ID96348 requests that you want to acquire an update lock (U), and the exclusive lock (x) is not released until the lock times out.

The second is to use log tracking (errorlog)

Opens the specified trace tag globally

DBCC TRACEON (1222Mai 1)

DBCC TRACEON (1204 Maxim 1)

Use EXEC master..xp_readerrorlog to view the log. Due to the record of too much deadlock information, post several key points (bold in red)

Deadlock encountered.... Printing deadlock informationWait-for graphNULLNode:1 PAGE: 7 0x0000000575C7E970SPID 1V 6229275 CleanCnt:2 Mode:IX Flags: 0x3Grant List 3:Owner:0x00000004E99B7880 Mode:IX Flg:0x40 Ref:1 Life:02000000 SPID:219 ECID:0 XactLockInfo: 0x0000000575C7E970SPID: 219 ECID:0 Statement Type: UPDATE Line #: 84Input Buf: Language Event: exec proc_PUB_StockDataImportRequested by: ResType:LockOwner Stype:'OR'Xdes:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy: (0x0000000E440AAFE0) Value:0x8d160240 Cost: (0max 0) NULLNode:2 PAGE: CleanCnt:2 Mode:U Flags: 0x3Grant List 3:Owner:0x0000000D12099B80 Mode:U Flg:0x40 Ref:0 Life:00000001 SPID:64 ECID:0 XactLockInfo: 0x000000136B4758F0SPID: 64 ECID:0 Statement Type: UPDATE Line #: 108Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 907150277]

-several key messages displayed in the node:1 section:

PAGE 7vl PAGE 6229275 (database ID 7, 1 partition, number of 6229275 rows)

Mode: pattern intention exclusive lock of IX lock

SPID: 219 process ID

Event: name of the stored procedure executed by exec proc_PUB_StockDataImport

-several key messages displayed in the node:2 section

PAGE 7vl PAGE 5692366 (database ID 7, 1 partition, number of 5692366 rows)

Mode update lock for Mode:U 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: (0Unip 0) deadlock-listdeadlock victim=process956f4c8process-listprocess id=process956f4c8 taskpriority=0 logused=0 waitresource=PAGE: 7Gan1 Value:0x8d160240 Cost 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=128056executionStackframe procname=Test.dbo.proc_CnofStock line=108 stmtstart=9068 stmtend=9336 sqlhandle=0x03000700c503123601ba25019ca800000100000000000000update dbo.pub_stockset UpdateTime=GETDATE () from pub_stock ajoin PUB_PlatfromStocktemp b on a.GUID=b.StockGuid

From the information above, you can see that what kill drops is the process. Id is process956f4c8.

Process spid=64

LockMode=U acquires update lock

Isolationlevel=read committed

Heap information executed by executionStack:

Storage name procname=Test.dbo.proc_CnofStock

The statement update dbo.pub_stock set UpdateTime=GETDATE ()..

The source of the event initiated by clientapp

Finally, the solutions to avoid deadlock are summarized.

Access objects in the same order.

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

Avoid user interaction in transactions.

Use isolation levels based on row versioning.

Change the committed read of the transaction default isolation level to a snapshot

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Use nolock to remove the shared lock, but if the deadlock occurs on the u or x lock, nolock does not work

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

This is the answer to the question about how to troubleshoot deadlocks in sql server. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.