Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
<deadlock-list>
  <deadlock victim="process14012e08">
    <process-list>
      <process id="process14012e08" taskpriority="0" logused="148" 
               waitresource="PAGE: 28:1:762341" waittime="6583" 
               ownerId="54542271281" transactionname="DELETE" 
               lasttranstarted="2013-06-22T01:04:20.450" XDES="0x64de49950" 
               lockMode="S" schedulerid="26" kpid="13336" status="suspended" spid="1239" 
               sbid="0" ecid="0" priority="0" trancount="2" 
               lastbatchstarted="2013-06-22T01:00:19.120" 
               lastbatchcompleted="2013-06-22T01:00:19.117" hostpid="15675" 
               isolationlevel="read committed (2)" xactid="54542271281" currentdb="28" 
               lockTimeout="4294967295" clientoption1="671350816" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="3" stmtstart="134" stmtend="332" 
                 sqlhandle="0x02000000e5eb1a38144fa5c164b3c876cc2c8da059bf3bb1">
            delete from grid_col where grid_col_id not in (select grid_col_id from grid)
          </frame>
        </executionStack>
        <inputbuf>
          set rowcount 10000
          while 1 = 1
          begin delete from grid_col where grid_col_id not in (select grid_col_id from grid)
          if @@rowcount = 0
          break
          end
          set rowcount 0
        </inputbuf>
      </process>
      <process id="process10031288" taskpriority="0" logused="912" 
               waitresource="OBJECT: 28:731149650:16 " waittime="40270" 
               ownerId="54542276360" transactionname="insertRows" 
               lasttranstarted="2013-06-22T01:04:23.753" XDES="0x1641ef9400" 
               lockMode="IS" schedulerid="17" kpid="29104" status="suspended" spid="351" 
               sbid="0" ecid="0" priority="0" trancount="2" 
               lastbatchstarted="2013-06-22T01:04:23.753" 
               lastbatchcompleted="2013-06-22T01:04:23.660" 
               isolationlevel="read committed (2)" xactid="54542276360" 
               lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="check_grid_col" line="1" stmtstart="226" stmtend="376" 
                 sqlhandle="0x03001c00fe2706376157a70026a000000000000000000000">
            SELECT @retval = COUNT(*) from grid_col where grid_col_id = @grid_col
          </frame>
          <frame procname="adhoc" line="1" stmtstart="34376" stmtend="35270" 
                 sqlhandle="0x02000000434dff26c45f91c6fdb640bbbf3a4a276f6ac605">
            INSERT INTO grid (grid_id,grid_col_id,value) VALUES ( @P2 , @grid_col_id,@P3)
          </frame>
        </executionStack>
        <inputbuf>
          (@P0 bigint,@P1 int,@P2 char(300))
        </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <pagelock fileid="1" pageid="762341" dbid="28" objectname="grid" 
                id="lock1cb9187500" mode="IX" associatedObjectId="72057594070695936">
        <owner-list>
          <owner id="process10031288" mode="IX" />
        </owner-list>
        <waiter-list>
          <waiter id="process14012e08" mode="S" requestType="wait" />
        </waiter-list>
      </pagelock>
      <objectlock lockPartition="16" objid="731149650" subresource="FULL" 
                  dbid="28" objectname="grid_col" id="lock12e6c78f00" mode="X" 
                  associatedObjectId="731149650">
        <owner-list>
          <owner id="process14012e08" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process10031288" mode="IS" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
  </deadlock>
</deadlock-list>
share|improve this question
1  
Your deadlock is not on a single resource. Both grid_col and grid are involved. –  Martin Smith Jul 4 '13 at 11:02
    
@ypercube, he edited the post –  Paul Jul 4 '13 at 11:08
add comment

2 Answers 2

An example of how it can happen:

Two processes, each one has a shared lock on the same page, then an exclusive lock is needed by each process. Each process can't exclusively lock the page because of the shared row locks.

So, a deadlock happens on the same page.

More examples here from @AlexK

share|improve this answer
add comment

Page 28:1:762341 is owned by INSERT in IX mode and wanted by DELETE in S mode. Object 731149650 (grid_col) is owned by DELETE in X mode and wanted by INSERT in IS mode (for the CHECK SELECT).

You have a SELECT COUNT(*) from grid_col as check condition on a column in grid. And a deprecated ROWCOUNT restricted anti join DELETE, which appears to do scan to boot. In other words you are begging for deadlocks.

Fix your schema and data design.

  • Do not use check conditions that select cross table. Use foreign key constraints, change the data model design to accommodate a foreign key constraint.
  • Use TOP clause to restrict the DELETE.
  • Very likely the indexes on the grid and grid_col are missing or incorrect.
share|improve this answer
    
>>>> Use foreign key constraints, change the data model design to accommodate a foreign key constraint. But the problem is my schema for grid_col with columns as (col_id,f_id,f_value) has (col_id,f_id) as primary key. So I cant add a foreign key constraint. But If I do select grid_col_id into ##temp from grid. then do delete from grid_col where grid_col_id not in (select top 1000 grid_col_id from ##temp) delete top 1000 ##temp –  Dumper Jul 4 '13 at 12:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.