remember that when the client used the software, there was an occasional deadlock problem, because the time was uncertain and it was not good to reproduce the problem. It was a bit difficult to solve the problem at that time. The four necessary conditions for


  • mutual exclusion conditions (Mutual exclusion): resources can not be shared and can only be used by a single process.
  • request and hold condition (Hold and wait): the process that has already got resources can apply for new resources again.
  • non deprivation (No pre-emption): the allocated resources cannot be forcibly deprived from the corresponding process. The
  • loop waiting condition (Circular wait): a number of processes in the system make up a loop, each of which is waiting for the resources that are being occupied by the adjacent process.

now summarizes the two common ways to view deadlocks:

first is a graphical listener:

sqlserver. -align: center ">

" here is described roughly: there are two processes, one process ID is 96, the other 348. of the system automatically dropped the process, retained the process ID:348's transaction Commit. The deadlock on

is due to the PAG range lock due to batch updates, and the two processes index resources on the same partition. ID96348 all requests to get the update lock (U), and each x is not released until the lock timeout.

is the use of log tracking (errorlog)

to open the specified tracking markup

DBCC. Check the log.

 Deadlock encountered... Printing deadlock information Wait-for graph NULL Node:1 PAGE:. 000000 SPID:219 ECID:0 XactLockInfo: 0x0000000575C7E970 SPID: 219 ECID: 0 Statement Type: UPDATE Line Lue:0x8d160240 Cost: (0/0) NULL Node:2 PAGE: 7:1:5692366 CleanCnt:2 Mode:U Flags: 0x3 Grant Id = 7 Object Id = 907150277]

-------- node:1 part of the key information:

PAGE 7:1:6229275 ID (database ID 7, 1 partitions, 6229275 rows) A few key information displayed in the

-------node:2 part of the stored procedure named port

PAGE 7:1:5692366 (where the database ID 7, the 1 partition, the 5692366 rows) Urce Owner: ResType:LockOwner Stype:'OR'Xdes:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy: (0x0000000E440AAFE0) Value:0x8d160240 Ittime=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 Entapp=.Net SqlClient Data Provider hostname=VMSERVER76 hostpid=16328 isolationlevel=read committed (2) xactid=2988267079 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 Ndle=0x03000700c503123601ba25019ca800000100000000000000 update dbo.pub_stock set UpdateTime=GETDATE () from pub_stock a join PUB_PlatfromStocktemp B on. Code>lockMode=U access to update lock,

, isolationlevel=read committed

, executionStack execution of heap information update dbo.pub_stock set UpdateTime=GETDATE ()..

, clientapp

optimizes index, avoids full table scan, reduces the number of locks application,.

avoids user interaction in transactions.

uses a row version control based isolation level.





. Degree (page lock, table lock), in order to block and replace the deadlock

summary of

is all the content of this article, I hope that the content of this article for everyone's learning or work has a certain reference value, if there is a doubt you can leave a message, thank you. Thank you for your support for the home of the script.

that you may be interested in:

This paper fixed link: | Script Home | +Copy Link

Article reprint please specify:Full process sharing of deadlock investigation in SQL Server | Script Home

You may also be interested in these articles!