Record the deadlock process and solution of the company's warehouse database server, and the warehouse database

Record the deadlock process and solution of the company's warehouse database server, and the warehouse database

Four Conditions for deadlock:

Mutual exclusion: resources cannot be shared and can only be used by one process.

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

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 resources occupied by adjacent processes.

Warehouse pick-up is stuck. I checked many places in the database and found no clue. Finally, I checked the SQL Server Error Log and finally found the 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] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR')))    inputbufunknown      frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000UPDATE [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=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] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR')))    inputbufunknown      frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000UPDATE [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=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 kpid=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=process5c26988deadlock-list

Looking at the above error message, we can find the deadlock caused by two identical statements, but such a short statement cannot hold the exclusive lock for too long.

After carefully analyzing the error log, we found that all the deadlocks were on the same non-clustered index. Then we asked about the development. The Development said that this statement was in a large transaction, this transaction will do 7 or 8 tasks

Index attributes

There are also data in the index, and many duplicate values are found

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] = 1WHERE (([Orderxxx].[State] = 3) And ([Orderxxx].[OrderOut] = 4055484) And ([Orderxxx].[PricingExpressProductCode] IN ('UKRRM','UKRLE')))

Execution Plan generated for the statement

At that time, a large number of SQL statements were blocked, and the blocking statement was exactly the following statement.

UPDATE [Orderxxx] SET [OperateState] = $1.0000,[HandledByNewWms] = 1WHERE (([Orderxxx].[State] = 3) And ([Orderxxx].[OrderOut] = 4055484) And ([Orderxxx].[PricingExpressProductCode] IN ('UKRRM','UKRLE')))

Solution

The preceding symptoms are as follows:

1. The update statement is in a large transaction. if the transaction is too large, other sessions will wait for the exclusive lock for a longer time.

2. Everyone is using the same non-clustered index and scanning the PricingExpressProductCode Field

3. There are many duplicate values in the Index

From the above symptoms, we can basically tell that this non-clustered index is useless and can be disabled.

ALTER INDEX [IX_PricingExpressProductCode_State] ON [dbo].[Orderxxx] DISABLE

After being disabled, the deadlock disappears, the problem is solved, and the warehouse resentment disappears.

This troubleshooting process takes a long time, but it is easy to locate. the SQL Server Error Log provides enough information to locate the deadlock problem. Therefore, you must analyze the log when encountering problems.