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

This article will explain in detail about the deadlock process of the company's warehouse database server and what the solution is. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

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.

Warehouse picking stuck, checked many places in the database, did not have a clue, and finally checked the SQL Server error log, and finally found clues.

EXEC xp_readerrorlog 0 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 Orderxxx 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 Orderxxx 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

At first glance, we can find the deadlock caused by two identical statements, but it is impossible for such a short statement to hold an exclusive lock for too long.

After a careful analysis of the error log, we found that they were all deadlocked on the same nonclustered index. I asked the developer again, and the developer said that this statement is in a big transaction, and this transaction will do seven or eight things.

Index attribute

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

The SQL statement goes 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')

The following figure shows the execution plan generated by the statement.

The situation at that time was that a large number of SQL statements were blocked, and the blocking statement was 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 method

There are several symptoms above.

1. The update statement is in a large transaction, which causes other session to wait longer for exclusive locks.

2. Everyone is using the same nonclustered index and scanning the PricingExpressProductCode field.

3. There are many duplicate values in the index.

Judging from the above symptoms, this nonclustered index is useless and can be disabled.

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

After it was disabled, the deadlock disappeared, the problem was solved, and so did the grievances in the warehouse.

This time, the troubleshooting process takes a long time, but it is easy to locate. The SQL Server error log gives enough information to locate the deadlock problem, so when you encounter problems, you must analyze the log clearly.

About the company warehouse database server deadlock process and what is the solution to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.