Understanding deadlocks with SQL Server query notifications

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_开发者_JAVA百科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

I realize this a very late reply, but just in case anyone's still interested...

I never actually resolved this issue (I don't think that's possible). But I did manage to find a workaround. What I did is use Service Broker. I created a sp that sends messages to a queue (each message takes the place of an insert - this sp is called by multiple writers) and another sp that consumes this queue. This second sp reads all pending messages (inserts) and then does a bulk insert into the sys_Events table with TABLOCK (it is called automatically by SqlSrv when there are messages to consume and runs in its own process).

So what I'm actually doing is gathering inserts and doing them all together simultaneously from a single SqlSrv process. Service Broker messages and queues are completely reliable and part of the database so there is no loss in data integrity. If anything, this approach is actually faster and implementing it is fairly simple.

ps. I still think that this behavior is a bug though and should be fixed someday!