All geek questions in one place

SQL Server update statements cause deadlocks

I have an application that simultaneously runs several sql statements in different threads, causing various deadlocks that seem to come from the same table. For example, two update statements below:

UPDATE WF SET QUEUETIME = '2011-02-18 13: 06: 53.578', STATE = 'outbound', USER = '', TIME = null WHERE PID = 'MessageProcessing' AND ACTIVITYID = 'Delete' AND ITEMID = '120' AND TRANID = 'Created' AND STATE = 'ready' AND USER = ''

UPDATE WF SET QUEUETIME = '2011-02-18 13: 06: 53.625', STATE = 'ready', USER = '', TIME = null WHERE PID = 'standardOutbound' AND ACTIVITYID = 'Node1' AND ITEMID = '121' AND TRANID = 'toNode1' AND STATE = '' AND USER = ''

produces the following deadlock:

<deadlock-list> <deadlock victim="process6d8e38"> <process-list> <process id="process6d8e38" taskpriority="0" logused="272" waitresource="RID: 7:1:564:14" waittime="625" ownerId="430343" transactionname="implicit_transaction" lasttranstarted="2011-02-18T13:06:53.640" XDES="0xb44a258" lockMode="U" schedulerid="1" kpid="2632" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-02-18T13:06:53.640" lastbatchcompleted="2011-02-18T13:06:53.640" clientapp="jTDS" hostname="INTERWOV-FP1" hostpid="123" loginname="database1" isolationlevel="read committed (2)" xactid="430343" currentdb="7" TIMEout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="adhoc" line="1" stmtstart="336" sqlhandle="0x0200000077e2b21749c20d3ca2ca8d4d89ea5ea29336e03e"> UPDATE WF SET QUEUETIME = @P0 , STATE = @P1 , USER = @P2 , TIME = @P3 WHERE PID = @P4 AND ACTIVITYID = @P5 AND ITEMID = @P6 AND TRANID = @P7 AND STATE = @P8 AND USER = @P9 </frame> </executionStack> <inputbuf> (@P0 datetime,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 int,@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000))UPDATE WF SET QUEUETIME = @P0 , STATE = @P1 , USER = @P2 , TIME = @P3 WHERE PID = @P4 AND ACTIVITYID = @P5 AND ITEMID = @P6 AND TRANID = @P7 AND STATE = @P8 AND USER = @P9 </inputbuf> </process> <process id="process8ccb68" taskpriority="0" logused="900" waitresource="RID: 7:1:564:12" waittime="625" ownerId="430341" transactionname="implicit_transaction" lasttranstarted="2011-02-18T13:06:53.623" XDES="0xaeccf48" lockMode="U" schedulerid="2" kpid="312" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-02-18T13:06:53.640" lastbatchcompleted="2011-02-18T13:06:53.623" clientapp="jTDS" hostname="INTERWOV-FP1" hostpid="123" loginname="database1" isolationlevel="read committed (2)" xactid="430341" currentdb="7" TIMEout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="adhoc" line="1" stmtstart="336" sqlhandle="0x0200000077e2b21749c20d3ca2ca8d4d89ea5ea29336e03e"> UPDATE WF SET QUEUETIME = @P0 , STATE = @P1 , USER = @P2 , TIME = @P3 WHERE PID = @P4 AND ACTIVITYID = @P5 AND ITEMID = @P6 AND TRANID = @P7 AND STATE = @P8 AND USER = @P9 </frame> </executionStack> <inputbuf> (@P0 datetime,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 int,@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000))UPDATE WF SET QUEUETIME = @P0 , STATE = @P1 , USER = @P2 , TIME = @P3 WHERE PID = @P4 AND ACTIVITYID = @P5 AND ITEMID = @P6 AND TRANID = @P7 AND STATE = @P8 AND USER = @P9 </inputbuf> </process> </process-list> <resource-list> <ridlock fileid="1" pageid="564" dbid="7" objectname="database1.dbo.WF" id="lock3a63dc0" mode="X" associatedObjectId="72057594077577216"> <owner-list> <owner id="process6d8e38" mode="X"/> </owner-list> <waiter-list> <waiter id="process8ccb68" mode="U" requestType="wait"/> </waiter-list> </ridlock> <ridlock fileid="1" pageid="564" dbid="7" objectname="database1.dbo.WF" id="lock3a65f40" mode="X" associatedObjectId="72057594077577216"> <owner-list> <owner id="process8ccb68" mode="X"/> </owner-list> <waiter-list> <waiter id="process6d8e38" mode="U" requestType="wait"/> </waiter-list> </ridlock> </resource-list> </deadlock> </deadlock-list>

I understand that some locks are inevitable, and the application must handle them (what it does), but I do not understand why they should occur in this case. In my simplified mind, these two statements should block different lines, and even if they update the same line, you just have to wait for the other.

Can someone explain why they cause deadlocks, or give any suggestions on how to prevent them?

We have 3 non-clustered indexes in the table (PID, ACTIVITYID, ITEMID, TRANID), (ITEMID) and (PID, ACTIVITYID). (PID, ACTIVITYID, ITEMID, TRANID) form the primary key. I tried (somewhat blindly) to play with indexes, but seemingly to no avail.

The application runs on weblogic and sql server 2005, and I reproduced the deadlocks on websphere and sql server 2008. It seems that this does not happen when using the oracle database, but this, unfortunately, is not an option for our client!

Many thanks to everyone who can help or understand this.

+4
source share
2 answers

The problem is usually not with updates, but with a combination of choices and updates. Consider a scenario in which a transaction selects a row and then updates it. If two such transactions are executed in parallel, a deadlock occurs. The simplest solution is to use the UPDLOCK (and optionally ROWLOCK) prompts in some statements; Of course, only for records updated after - otherwise you may end the slow application.

+1
source

Is there any clustered index? Any indexed views? Any other indexes? Updated columns are not displayed in any index. Line level locking should be good, but something should cause escalation. The sets seem to be disjoint, but maybe the pages are overlapping (hence my question is about choosing a clustered index).

http://msdn.microsoft.com/en-us/library/ms184286.aspx

+1
source

Source: https://habr.com/ru/post/1340770/

More articles:


All Articles