Операторы обновления SQL-сервера вызывают взаимоблокировки
У меня есть приложение, которое выполняет несколько SQL-операторов одновременно в разных потоках, вызывая различные взаимоблокировки, которые, кажется, все приходят из одной таблицы. Например, два оператора обновления ниже:
ОБНОВЛЕНИЕ WF SET QUEUETIME='2011-02-18 13:06:53.578', STATE = 'исходящий', USER = '', TIME = ноль WHERE PID = 'MessageProcessing' AND ACTIVITYID = 'Удалить' AND ITEMID = '120' AND TRANID = 'Created' AND STATE = 'ready' AND USER = ''
ОБНОВЛЕНИЕ WF SET QUEUETIME='2011-02-18 13:06:53.625', STATE = 'ready', USER = '', TIME = null PID = 'standardOutbound' И ACTIVITYID = 'Node1' AND ITEMID = '121' AND TRANID = 'toNode1' И STATE = '' AND USER = ''
создать следующий тупик:
<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>
Я понимаю, что некоторое количество взаимоблокировок неизбежно, и приложение должно их обрабатывать (что оно и делает), но я не понимаю, почему они должны происходить в этом случае. В моем упрощенном представлении эти два оператора должны блокировать разные строки, и даже если они обновляют одну и ту же строку, нужно просто подождать другую.
Может кто-нибудь объяснить, почему они вызывают тупики, или дать какие-либо предложения относительно того, как их предотвратить?
У нас есть 3 некластеризованных индекса в таблице (PID, ACTIVITYID, ITEMID, TRANID), (ITEMID) и (PID, ACTIVITYID). (PID, ACTIVITYID, ITEMID, TRANID) образуют первичный ключ. Я пытался (несколько слепо) играть с индексами, но, похоже, безрезультатно.
Приложение работает на weblogic и sql server 2005, и я воспроизвел взаимоблокировки на websphere и sql server 2008. Кажется, что при использовании базы данных oracle этого не происходит, но, к сожалению, это не вариант для нашего клиента!
Большое спасибо всем, кто может предложить помощь или понимание этого.
2 ответа
Проблема, как правило, не в обновлениях, а в комбинации выбора и обновлений. Рассмотрим сценарий, в котором транзакция выбирает некоторую строку и затем обновляет ее. Если две такие транзакции выполняются параллельно, возникает тупик. Самое простое решение - использовать подсказку UPDLOCK (и, возможно, ROWLOCK) в операторах выбора; Конечно, только для записей, которые обновляются после - в противном случае вы можете закончить медленное приложение.
Есть ли кластерный индекс? Есть индексированные представления? Любые другие индексы? Обновляемые столбцы не отображаются ни в каких индексах. Блокировка на уровне строк должна быть в порядке, но что-то должно вызывать эскалацию. Наборы кажутся непересекающимися, но, возможно, страницы перекрываются (отсюда мой вопрос о выборе кластерного индекса).