Search code examples
sql-serverdatabase-deadlocksdbcc

Why do dbcc checkdb on system databases and sp_executesql on a user database cause a deadlock?

I have a daily job running a dbcc checkdb statement without additional parameters on each of the system databases. This job runs during off-peak hours and usually takes 5 seconds or less to run.

The last run however, took only 1 second and failed because of a deadlock. I've got an alert that saves me an xml graph of the deadlock, which I'm including for more detailed info.

My main questions are: why does such a deadlock actually occur and is it avoidable?

    <TextData>
      <deadlock-list>
     <deadlock victim="process290fd861088">
      <process-list>
       <process id="process290fd861088" taskpriority="0" logused="0" waitresource="OBJECT: 2:5:0 " ownerId="1250115008" transactionname="CheckDb" lasttranstarted="2017-03-20T01:00:01.427" XDES="0x2b277040bd8" lockMode="S" schedulerid="7" kpid="12760" status="suspended" spid="78" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-03-20T01:00:00.060" lastbatchcompleted="2017-03-20T01:00:00.060" lastattention="1900-01-01T00:00:00.060" clientapp="SQLAgent - TSQL JobStep (Job 0xB425122DD6C28D4BBE42D7F0AF76FC40 : Step 1)" hostname="0000-DB-0000" hostpid="8040" loginname="0000\0000" isolationlevel="read committed (2)" xactid="1250115008" currentdb="2" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
         <frame procname="0000_Local.server.CheckSystemDatabases" line="19" stmtstart="740" stmtend="776" sqlhandle="0x030006006a934a11b3ebcd0023a7000001000000000000000000000000000000000000000000000000000000">
    dbcc checkdb(@dbId     </frame>
         <frame procname="adhoc" line="1" stmtend="70" sqlhandle="0x010006006688101b405fcfceb602000000000000000000000000000000000000000000000000000000000000">
    exec [server].[CheckSystemDatabases     </frame>
        </executionStack>
        <inputbuf>
    exec [server].[CheckSystemDatabases];    </inputbuf>
       </process>
       <process id="process2b59a715468" taskpriority="0" logused="952" waitresource="OBJECT: 2:3:0 " ownerId="1250114957" transactionname="droptemp" lasttranstarted="2017-03-20T01:00:01.423" XDES="0x29b8755ce58" lockMode="IX" schedulerid="8" kpid="9440" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2017-03-20T01:00:01.410" lastbatchcompleted="2017-03-20T01:00:01.410" lastattention="1900-01-01T00:00:00.410" clientapp="0000-API-0000" hostname="0000-0000-WEB-0000" hostpid="42180" loginname="0000\0000" isolationlevel="read committed (2)" xactid="0" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
         <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
    sp_executesql     </frame>
         <frame procname="0000.dbo.SomeProcName" line="93" stmtstart="8320" stmtend="8496" sqlhandle="0x030009002ac137082fa8b20029a7000001000000000000000000000000000000000000000000000000000000">
    exec sp_executesql @selectSql, N'@rowcount int output', @rowcount = @TotalRowCount outpu     </frame>
        </executionStack>
        <inputbuf>
    Proc [Database Id = 9 Object Id = 137871658]    </inputbuf>
       </process>
      </process-list>
      <resource-list>
       <objectlock lockPartition="0" objid="5" subresource="FULL" dbid="2" objectname="tempdb.sys.sysrowsets" id="lock2b4103b8380" mode="IX" associatedObjectId="5">
        <owner-list>
         <owner id="process2b59a715468" mode="IX" />
        </owner-list>
        <waiter-list>
         <waiter id="process290fd861088" mode="S" requestType="wait" />
        </waiter-list>
       </objectlock>
       <objectlock lockPartition="0" objid="3" subresource="FULL" dbid="2" objectname="tempdb.sys.sysrscols" id="lock291f3d8a900" mode="S" associatedObjectId="3">
        <owner-list>
         <owner id="process290fd861088" mode="S" />
        </owner-list>
        <waiter-list>
         <waiter id="process2b59a715468" mode="IX" requestType="wait" />
        </waiter-list>
       </objectlock>
      </resource-list>
     </deadlock>
    </deadlock-list></TextData>

Solution i have implemented to priortize user transactions over Daily checkDB for TempDB: 

set nocount on;  
set deadlock_priority low;  
 declare @dbId int;  
declare loopCheckDB cursor fast_forward   
for select [d].[database_id] from [sys].[databases] as [d] where   [d].[database_id] < 5  
 order by [d].[name]  
 open [loopCheckDB]   
fetch next from [loopCheckDB] into @dbId;   
while @@FETCH_STATUS = 0  
 begin dbcc checkdb(@dbId); 
fetch next from [loopCheckDB] into @dbId; 
end 
close [loopCheckDB];
 deallocate [loopCheckDB];
Solution

  • Why do dbcc checkdb on system databases and sp_executesql on a user database cause a deadlock?

    DBCC checkDB acquired Intent Exclusive lock on tempdb.sys.sysrowsets and is waiting for a sharedlock on tempdb.sys.sysrscols..

    your user proc,is also accessing TEMPDB resources.This user proc acquired IX lock on tempdb.sys.sysrscols and is waiting for a shared lock on tempdb.sys.sysrowsets..

    Thus Deadlock occurred and this is a simple case of deadlock

    Normally,DBCC CheckDB takes snapshot of the database before analysis and it works on this snapshot to avoid locking,blocking..

    In this case,as per this post..Snapshot is not possible with TEMPDB and so in your case,there are incompatible locks acquired by both the transactions ,which is the reason for the deadlock you saw.