Общие сведения о взаимоблокировках с помощью уведомлений о запросах SQL Server

У меня возникают проблемы с уведомлениями о запросах на SQL Server 2008 sp1. У меня есть таблица _sys_Events, в которую несколько авторов записывают записи, а несколько читателей выполняют операторы SELECT с уведомлением о запросе, чтобы немедленно получить последние записи (это делается с помощью класса .Net System.Data.SqlClient.SqlDependency). Наша база данных работает с READ_COMMITTED_SNAPSHOT ON. Мы также установили накопительный пакет обновления 9, в котором утверждается, что он содержит исправление для этой проблемы (kb/975090). Мы получаем тупиковые ситуации между читателем и писателем.

Типичный тупик выглядит следующим образом:

<deadlock-list>
 <deadlock victim="processb726a508">
 <process-list>
  <process id="processb726a508" taskpriority="0" logused="0" waitresource="KEY: 5:72057594588758016 (0d004e5bf730)" waittime="624" ownerId="3426492" transactionname="CQueryScan::BeginNotifXact" lasttranstarted="2010-09-13T14:26:57.267" XDES="0x8079ce90" lockMode="RangeS-U" schedulerid="1" kpid="3260" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2010-09-13T14:26:57.267" lastbatchcompleted="2010-09-13T14:26:57.267" clientapp=".Net SqlClient Data Provider" hostname="INETC809" hostpid="1532" loginname="bbuser" isolationlevel="read committed (2)" xactid="3426491" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  <executionStack>
   <frame procname="adhoc" line="1" sqlhandle="0x020000005659040700b3257e8e06defba3179cc01ffb7ca2">
(@1 int)SELECT [Id],[EventData],[LogDate] FROM [dbo].[_sysEvents] WHERE [Id]&gt;@1 ORDER BY [Id] ASC   </frame>
   <frame procname="adhoc" line="1" sqlhandle="0x02000000acf8f33257911a0ea68aae02722e15daa9a60023">
SELECT Id, EventData, LogDate FROM dbo._sysEvents WHERE Id &gt; 1425265 ORDER BY Id   </frame>
  </executionStack>
  <inputbuf>
SELECT Id, EventData, LogDate FROM dbo._sysEvents WHERE Id &gt; 1425265 ORDER BY Id  </inputbuf>
  </process>
  <process id="process8db45b88" taskpriority="0" logused="8348" waitresource="KEY: 5:72057594588758016 (1200caf8f72f)" waittime="623" ownerId="3424785" transactionname="user_transaction" lasttranstarted="2010-09-13T14:26:47.157" XDES="0xf8906dc0" lockMode="RangeS-U" schedulerid="1" kpid="3656" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-09-13T14:26:57.530" lastbatchcompleted="2010-09-13T14:26:57.530" clientapp=".Net SqlClient Data Provider" hostname="INETC1012" hostpid="3584" loginname="bbuser" isolationlevel="read committed (2)" xactid="3424785" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  <executionStack>
   <frame procname="adhoc" line="1" stmtstart="66" stmtend="218" sqlhandle="0x02000000d60d99067d5177738e10de6507ecd187d217792e">
INSERT INTO [dbo].[_sysEvents]([EventData], [LogDate])
VALUES (@p0, @p1)   </frame>
   <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown   </frame>
  </executionStack>
  <inputbuf>
(@p0 varbinary(640),@p1 datetime)INSERT INTO [dbo].[_sysEvents]([EventData], [LogDate])
VALUES (@p0, @p1)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]  </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594588758016" dbid="5" objectname="BBBets.sys.query_notification_734885935" indexname="cidx" id="lockc6f66d00" mode="RangeX-X" associatedObjectId="72057594588758016">
  <owner-list>
   <owner id="process8db45b88" mode="RangeX-X"/>
  </owner-list>
  <waiter-list>
   <waiter id="processb726a508" mode="RangeS-U" requestType="wait"/>
  </waiter-list>
  </keylock>
  <keylock hobtid="72057594588758016" dbid="5" objectname="BBBets.sys.query_notification_734885935" indexname="cidx" id="lockc7b29380" mode="RangeS-U" associatedObjectId="72057594588758016">
  <owner-list>
   <owner id="processb726a508" mode="RangeS-U"/>
  </owner-list>
  <waiter-list>
   <waiter id="process8db45b88" mode="RangeS-U" requestType="wait"/>
  </waiter-list>
  </keylock>
 </resource-list>
 </deadlock>
</deadlock-list>

Операторы SELECT и операторы INSERT выполняются на уровне изоляции READ_COMMITED по умолчанию, который в нашем случае использует управление версиями строк. Как видите, блокировка происходит на одном и том же объекте индекса. Я предполагаю, что управление версиями строк позволяет этому случиться?

Могу ли я что-нибудь сделать, чтобы решить эту проблему? Может быть, другой уровень изоляции в операторах SELECT? Не следует ли мне использовать службы Notification Services для моего сценария?

заранее спасибо


notifications sql-server service deadlock
person Panagiotis Poulos    schedule 14.09.2010    source источник
comment
актуальна ли ваша статистика?   -  person Mitch Wheat    schedule 14.09.2010

Ответы (1)


arrow_upward
2
arrow_downward

Я понимаю, что это очень поздний ответ, но на всякий случай кому-то все еще интересно...

Я никогда не решал эту проблему (я не думаю, что это возможно). Но мне удалось найти обходной путь. Что я сделал, так это использовал Service Broker. Я создал sp, который отправляет сообщения в очередь (каждое сообщение занимает место вставки — этот sp вызывается несколькими авторами), и еще один sp, который использует эту очередь. Этот второй sp считывает все ожидающие сообщения (вставки), а затем выполняет массовую вставку в таблицу sys_Events с помощью TABLOCK (он автоматически вызывается SqlSrv, когда есть сообщения для потребления, и выполняется в своем собственном процессе).

Итак, что я на самом деле делаю, так это собираю вставки и делаю их все вместе одновременно из одного процесса SqlSrv. Сообщения и очереди Service Broker полностью надежны и являются частью базы данных, поэтому целостность данных не нарушается. Во всяком случае, этот подход на самом деле быстрее, и его реализация довольно проста.

пс. Я все еще думаю, что это поведение является ошибкой и должно быть исправлено когда-нибудь!

person Panagiotis Poulos    schedule 16.01.2011