locked
Understanding deadlocks with query notifications RRS feed

  • 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

    Monday, September 13, 2010 3:23 PM

All replies

  • Is there nobody from Microsoft to comment on this? If there is a limitation in Notification Services in this scenario it would be nice to know so that I do this differently. I'm not necessarily looking for a resolution, but I need to know how to proceed. Anyone?
    Thursday, September 16, 2010 9:38 AM