Understanding deadlocks with SQL Server query notifications

Question

Hi

I'm running into trouble with Query Notifications on SQL Server 2008 sp1. I have a table _sys_Events that multiple writers write entries into, and multiple readers perform SELECT statements with Query Notification to get latest entries immediately (this is done through .Net System.Data.SqlClient.SqlDependency class). Our database is running with READ_COMMITTED_SNAPSHOT ON. We have also installed the Cumulative update package 9 that claims to contain a fix for this issue (kb/975090). What we are getting is deadlocks between a reader and a writer.

A typical deadlock follows:

<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 statements and INSERT statements are both done in default READ_COMMITED isolation level, which in our case uses row-versioning. As you can see, the lock occurs on the same index object. My guess is that row-versioning allows that to happen?

Is there anything I can do to resolve this issue? Maybe a different isolation level on SELECT statements? Should I not be using Notification Services for my scenario?

Thanks in advance

Answer 1

Hi Mitch, thanks for helping out. I'm afraid I don't follow; how are statistics responsible for what I'm seeing? As far as I understand, deadlocks are occuring on a system-generated object created by Sql Server to facilitate query notifications. What statistics should I try to update?