Home>

According to the definition in the operating system:Deadlock refers to the fact that each process in a group of processes occupies resources that will not be released.However, it is in a permanent waiting state due to mutual application for resources that will not be released by other processes.Below we share the deadlock process and solution of the company's warehouse database server.

Four requirements for deadlock:

Mutual exclusion:resources cannot be shared,Can only be used by one process.

Hold and wait conditions:Processes that have obtained resources can apply for new resources again.

No pre-emption:Resources that have been allocated cannot be forcibly deprived from the corresponding process.

Circular wait condition:several processes in the system form a loop,Each process in the loop is waiting for resources that are being used by neighboring processes.

Warehouse picking stuck,Investigated many places in the database,No clue,Finally, check it in the sql server error log.Finally found clues

exec xp_readerrorlog 0,1, null, null, "2015-09-21", "2015-10-10", "desc"
   waiter id=process5c30e08 mode=u requesttype=wait
  waiter-list
   owner id=process5c26988 mode=x
  owner-list
  keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.orderxxx indexname=ix_pricingexpressproductcode_state id=lock17fa96980 mode=x associatedobjectid=72057597785604096
   waiter id=process5c26988 mode=u requesttype=wait
  waiter-list
   owner id=process5c30e08 mode=x
  owner-list
  keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.orderxxx indexname=ix_pricingexpressproductcode_state id=lock87d69e780 mode=x associatedobjectid=72057597785604096
 resource-list
(@operatestate money, @handledbynewwms bit, @state int, @orderout int)
update [orderxx] set [operatestate][email protected], [handledbynewwms][email protected] where (([orderxxx]. [state][email protected]) and ([orderxxx]. [orderout][email protected]) and ([orderxxx] . [pricingexpressproductcode] in ("uknir")))
  inputbuf
unknown
   frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
update [orderxxx] set [operatestate][email protected], [handledbynewwms][email protected] where (([orderxxx]. [state][email protected]) and ([orderxxx]. [orderout][email protected]) and ([orderxxx] . [pricingexpressproductcode] in ("uknir")))
   frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
  executionstack
  process id=process5c30e08 taskpriority=0 logused=10320 waitresource=key:33:72057597785604096 (112399c2054a) waittime=4813 ownerid=31578743038 transactionname=user_transaction lasttranstarted=2015-09-24t10:22:58.410 xdes=0x372e95950 lockmode=u schedulerid=17 kpid=8496 status=suspended spid=153 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24t10:22:58.540 lastbatchcompleted=2015-09-24t10:22:58.540 clientapp=.net sqlclient data provider hostname=ck1-win-web02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31578743038 currentdb=33 locktimeout=4294967295 clientoption1=671088672 clientoption2=128056
(@operatestate money,@handledbynewwms bit,@state int,@orderout int) update [orderxxx] set [operatestate][email protected], [handledbynewwms][email protected] where (([orderxxx]. [state][email protected]) and ([orderxxx]. [orderout][email protected]) and ([orderxxx]. [pricingexpressproductcode] in ("uknir")))
  inputbuf
unknown
   frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
update [orderxxx] set [operatestate][email protected], [handledbynewwms][email protected] where (([orderxxx]. [state][email protected]) and ([orderxxx]. [orderout][email protected]) and ([orderxxx] . [pricingexpressproductcode] in ("uknir")))
   frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
  executionstack
  process id=process5c26988 taskpriority=0 logused=9892 waitresource=key:33:72057597785604096 (70f5b089bb2b) waittime=4813 ownerid=31579268946 transactionname=user_transaction lasttranstarted=2015-09-24t10:27:01.357 xdes=0x98312f950 lockmode=u schedulerid=16 kd=9184 status=suspended spid=454 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24t10:27:01.490 lastbatchcompleted=2015-09-24t10:27:01.487 clientapp=.net sqlclient data provider hostname=ck1-win-web02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31579268946 currentdb=33 locktimeout=4294967295 clientoption1=671088672 clientoption2=128056
 process-list
 deadlock victim=process5c26988
deadlock-list

Take a look at the error message above,You can find the deadlock caused by two identical statements,But such a short sentence cannot hold an exclusive lock for too long

Take a closer look at the error log,Found that they are deadlocked on the same non-clustered index,Asked a bit about development,Development side said,This statement is in a big transaction,This business will do 7 or 8 things

Index attribute

And the data in the index,Found many duplicate values

The sql statement is like this

(@operatestate money, @handledbynewwms bit, @state int, @orderout int)
@ handledbynewwms=(1) @operatestate=($1.0000)@orderout=(4055484)@state=(3)
update [orderxxx] set [operatestate]=$1.0000, [handledbynewwms]=1
where (([orderxxx]. [state]=3) and ([orderxxx]. [orderout]=4055484) and ([orderxxx]. [pricingexpressproductcode] in ("ukrrm", "ukrle")))
</pre>
</div>
<p>
The following figure shows the execution plan generated by the statement
</p>
<p>
At that time, a large number of SQL statements were blocked.
And the blocking statement is exactly the following statement
</p>
<div>
<pre>
update [orderxxx] set [operatestate]=$1.0000, [handledbynewwms]=1
where (([orderxxx]. [state]=3) and ([orderxxx]. [orderout]=4055484) and ([orderxxx]. [pricingexpressproductcode] in ("ukrrm", "ukrle")))

Solution

A few symptoms above

1. The update statement is in a big transaction.Too many transactions cause other sessions to wait for exclusive locks for longer

2.Everyone is using the same non-clustered index.And scan pricingexpressproductcode field

3, there are many duplicate values ​​in the index

It can be basically judged from the above symptoms,This non-clustered index is useless,Can be disabled

alter index [ix_pricingexpressproductcode_state] on [dbo]. [orderxxx] disable

Once disabled,Deadlock disappears,problem solved,The grievances in the warehouse also disappeared

This time the troubleshooting process is a bit long.But well positioned,The sql server error log gives enough information to locate the deadlock problem,So when you encounter problems, you must analyze the log clearly

  • Previous Explain the use of throw and throws clauses in Java programming
  • Next Write a small number guessing game in Java