Verständnis von Deadlocks mit SQL server-Abfragebenachrichtigungen

Ich laufe in Schwierigkeiten mit Query Notifications auf SQL server 2008 sp1. Ich habe eine Tabelle _sys_Events, dass mehrere Autoren Einträge eingeben, und mehrere Leser führen SELECT-statementen mit Query Notification, um die neuesten Einträge sofort (dies erfolgt durch .Net System.Data.SqlClient.SqlDependency class). Unsere database läuft mit READ_COMMITTED_SNAPSHOT ON. Wir haben auch das kumulative Updatepaket 9 installiert, das behauptet, ein Update für dieses Problem zu enthalten (kb / 975090). Was wir bekommen, ist Deadlocks zwischen einem Leser und einem Schriftsteller.

Ein typischer Deadlock folgt:

<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-statementen und INSERT-statementen werden beide in der standardmäßigen READ_COMMITED-Isolationsebene durchgeführt, die in unserem Fall eine Zeilenversionierung verwendet. Wie Sie sehen können, tritt die Sperre auf demselben Indexobjekt auf. Meine Vermutung ist, dass die Zeilen-Versionierung das erlaubt?

Gibt es etwas, das ich tun kann, um dieses Problem zu lösen? Vielleicht eine andere Isolationsstufe bei SELECT-statementen? Sollte ich keine Benachrichtigungsdienste für mein Szenario verwenden?

Danke im Voraus

Ich erkenne das eine sehr späte Antwort, aber nur für den Fall, dass sich noch jemand interessiert …

Ich habe dieses Problem niemals getriggers (ich glaube nicht, dass das möglich ist). Aber ich habe es geschafft, einen Workaround zu finden. Was ich tat, ist Gebrauch Service Broker. Ich habe ein sp erstellt, das Nachrichten an eine Warteschlange sendet (jede Nachricht nimmt an die Stelle eines Insertes – das sp wird von mehreren Schriftstellern genannt) und ein anderes sp, das diese Warteschlange verbraucht. Diese zweite Sp liest alle anstehenden Nachrichten (Einfügungen) und macht dann einen Bulk-Insert in die sys_Events-Tabelle mit TABLOCK (es wird automatisch von SqlSrv aufgerufen, wenn es Nachrichten zu konsumieren und läuft in seinem eigenen process).

Also, was ich eigentlich tue, sammelt Einsätze und macht sie alle zusammen aus einem einzigen SqlSrv-process. Service Broker Nachrichten und Warteschlangen sind absolut zuverlässig und Teil der database, so dass es keinen Verlust an dataintegrität gibt. Wenn überhaupt, ist dieser Ansatz tatsächlich schneller und implementiert ist es ziemlich einfach.

ps Ich denke immer noch, dass dieses Verhalten ein Bug ist und sollte irgendwann behoben werden!