Remember a company warehouse database server deadlock process and its solution _mssql

Four necessary conditions for deadlocks:

Mutex conditions (Mutual exclusion): Resources cannot be shared and can only be used by one process.

Request and hold conditions (Hold and wait): processes that have been given resources can request new resources again.

Non-deprivation (no pre-emption): Resources that have been allocated cannot be forcibly deprived from the corresponding process.

Loop wait condition (circular): Several processes in the system form a loop in which each process waits for resources that are being occupied by adjacent processes.

Warehouse pick up the card died, the database of many places, no clue, and finally to the SQL Server error log to view, and 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. 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 Ho btid=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] = @OperateState, [handledbynewwms] = @HandledByNewWms WHERE ( [Orderxxx]. [state] = @State) and ([orderxxx].[ Orderout] = @OrderOut) and ([orderxxx].[ Pricingexpressproductcode] In (' Uknir ')) Inputbuf unknown frame procName=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000 UPDATE [Orderxxx] SET [OperateState = @OperateState, [handledbynewwms] = @HandledByNewWms WHERE ([orderxxx].[ State] = @State) and ([orderxxx].[ Orderout] = @OrderOut) and ([orderxxx].[ Pricingexpressproductcode] In (' Uknir ')) frame Procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e 7ea51289d8caa2fb4de65c976389 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 153 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24t10:22:58.540 : 22:58.540 clientapp=.net SqlClient Data Provider hostname=ck1-win-web02 hostpid=37992 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] = @OperateState, [handledbynewwms] = @ Handledbynewwms WHERE ([orderxxx].[ State] = @State) and ([orderxxx].[ Orderout] = @OrderOut) and ([orderxxx].[ Pricingexpressproductcode] In (' Uknir ')) Inputbuf unknown frame Procname=unknown line=1 sqlhandle=0x00000000000 0000000000000000000000000000000000000 UPDATE [orderxxx] SET [operatestate] = @OperateState, [handledbynewwms] = @ Handledbynewwms WHERE ([orderxxx].[ State] = @State) and ([orderxxx].[ Orderout] = @OrderOut) and ([orderxxx].[ Pricingexpressproductcode] In (' Uknir ')) frame Procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e 7ea51289d8caa2fb4de65c976389 executionstack process id=process5c26988 taskpriority=0 logused=9892 waitresource=key:3 3:72057597785604096 (70f5b089bb2b) waittime=4813 ownerid=31579268946 TransactionnamE=user_transaction lasttranstarted=2015-09-24t10:27:01.357 xdes=0x98312f950 lockmode=u schedulerid=16 kpid=9184 status=suspended spid=454 sbid=0 ecid=0 priority=0 trancount=2 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 clientoption1=671088672 clientoption2=128056 process-list Deadlock victim=process5c26988 deadlock-list

Look at the error message above, you can find two identical statements caused by the deadlock, but such a short statement can not hold exclusive locks too long

After careful analysis of the error log, found to be locked on the same nonclustered index, and then asked the development, development side said, this statement is in a big business, this transaction will do 7, 8 things

Indexed properties

And the data in the index, we found a lot of 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 '))

The following illustration is a statement-generated execution plan

The situation at the time was that a large number of SQL statements were blocked, and the blocked statement was the following statement

UPDATE [orderxxx] SET [operatestate] = $1.0000,[handledbynewwms] = 1
WHERE ([orderxxx].[ State] = 3) and ([orderxxx].[ Orderout] = 4055484) and ([orderxxx].[ Pricingexpressproductcode] In (' Ukrrm ', ' Ukrle '))

Solving method

We got a few symptoms.

1, the UPDATE statement is in a large transaction, the transaction is too large to allow other sessions to wait for exclusive lock time to become longer

2, everyone is using the same nonclustered index, and scan the Pricingexpressproductcode field

3, the index of repeated values are many

From the above symptoms can be basically judged, this nonclustered index is useless, you can disable the

ALTER INDEX [ix_pricingexpressproductcode_state] on [dbo]. [Orderxxx] DISABLE

After disabling, the deadlock disappears, the problem is solved, and the grievances of the warehouse disappear.

This time the troubleshooting process is a bit long, but very good positioning, SQL Server error log gives enough information to locate the deadlock problem, so when the problem must be analyzed clearly log