SQL server deadlock between INSERT and SELECT statement
- by dtroy
Hi!
I've got a problem with multiple deadlocks on SQL server 2005.
This one is between an INSERT and a SELECT statement.
There are two tables. Table 1 and Table2. Table2 has Table1's PK (table1_id) as foreign key.
Index on table1_id is clustered.
The INSERT inserts a single row into table2 at a time.
The SELCET joins the 2 tables. (it's a long query which might take up to 12 secs to run)
According to my understanding (and experiments) the INSERT should acquire an IS lock on table1 to check referential integrity (which should not cause a deadlock). But, in this case it acquired an IX page lock
The deadlock report:
<deadlock-list>
<deadlock victim="process968898">
<process-list>
<process id="process8db1f8" taskpriority="0" logused="2424" waitresource="OBJECT: 5:789577851:0 " waittime="12390" ownerId="61831512" transactionname="user_transaction" lasttranstarted="2010-04-16T07:10:13.347" XDES="0x222a8250" lockMode="IX" schedulerid="1" kpid="3764" status="suspended" spid="52" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:13.350" lastbatchcompleted="2010-04-16T07:10:13.347" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read uncommitted (1)" xactid="61831512" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="DatabaseName.dbo.prcTable2_Insert" line="18" stmtstart="576" stmtend="1148" sqlhandle="0x0300050079e62d06e9307f000b9d00000100000000000000">
INSERT INTO dbo.Table2
(
f1,
table1_id,
f2
)
VALUES
(
@p1,
@p_DocumentVersionID,
@p1
) </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 103671417] </inputbuf>
</process>
<process id="process968898" taskpriority="0" logused="0" waitresource="PAGE: 5:1:46510" waittime="7625" ownerId="61831406" transactionname="INSERT" lasttranstarted="2010-04-16T07:10:12.717" XDES="0x418ec00" lockMode="S" schedulerid="2" kpid="1724" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:12.713" lastbatchcompleted="2010-04-16T07:10:12.713" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read committed (2)" xactid="61831406" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="DatabaseName.dbo.prcGetList" line="64" stmtstart="3548" stmtend="11570" sqlhandle="0x03000500dbcec17e8d267f000b9d00000100000000000000">
<!-- XXXXXXXXXXXXXX...SELECT STATEMENT WITH Multiple joins including both Table2 table 1 and .... XXXXXXXXXXXXXXX -->
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 2126630619] </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="46510" dbid="5" objectname="DatabaseName.dbo.table1" id="lock6236bc0" mode="IX" associatedObjectId="72057594042908672">
<owner-list>
<owner id="process8db1f8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process968898" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<objectlock lockPartition="0" objid="789577851" subresource="FULL" dbid="5" objectname="DatabaseName.dbo.Table2" id="lock970a240" mode="S" associatedObjectId="789577851">
<owner-list>
<owner id="process968898" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process8db1f8" mode="IX" requestType="wait"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
Can anyone explain why the INSERT gets the IX page lock ?
Am I not reading the deadlock report properly?
BTW, I have not managed to reproduce this issue.
Thanks!
