Deadlock occur in a delete insert scenario

Question

<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>

Answer 1

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

Answer 2

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.