SQL server deadlock between INSERT and SELECT statement

Question

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!

EDIT: TABLES CREATION:

CREATE TABLE [dbo].[Table2] (
    [Table2_id] [int] IDENTITY (1, 1) NOT NULL ,
    [f1] [int] NULL ,
    [Table1_id] [int] NOT NULL ,
    [f2] [int] NOT NULL ,
)

ALTER TABLE [dbo].[Table2] ADD 
    CONSTRAINT [FK_Table2_Table1] FOREIGN KEY 
    (
        [Table1_id]
    ) REFERENCES [dbo].[Table1] (
        [Table1_id]
    )


CREATE TABLE [dbo].[Table1] (
    [Table1_id] [int] IDENTITY (1, 1) NOT NULL ,
)

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD 
    CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED 
    (
        [Table1_id]
    ) 

Answer 1

At a punt, I'd say that DatabaseName.dbo.prcTable2_Insert is executing inside a transaction or explicitly opens one, and it (or the connection with the open transaction) has already done an insert into Table1 beforehand.

Answer 2

I means 'intent' lock, and they are always associated with hierarchies. Because the lock manager does not understand physical structure, it is impossible for him to honor hierarchical locks, so the hierarchy is re-created in intent locks.

In your case the INSERT has an intent lock on a page. This implies it has also obtained an X lock on a row in the page, which is the normal behavior. It now tries to obtain a new IX lock, so it probably needs to insert a row in a different page. This would be normal behavior of an insert into a table with multiple indexes: first IX is on one of the indexes (possible the clustered) and the second IX is on a non-clustered index.

The SELECT you say returns in 12 seconds, so its a long query, on a large data set, and the plan probably has chosen a high lock granularity, page locks. The SELECT has an S lock on the page which INSERT wants the IX lock, and wants another S lock on the page that INSERT has the IX lock.

This is a trivial deadlock and should be very easy to fix it: make sure your SELECT does not need those page S-locks. This is no INSERT fault here. Not knowing what the SELECt does I can't say for sure whether is optimal or not. In my experience, almost always a SELECT like this has plenty, plenty and plenty more room for improvement (eitehr the SELECT itself or the schema underneath it).

But accepting that the SELECT is optimal, your easiest get-out-of-jail card is to turn on row versioning:

ALTER DATABASE <dbname> SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON;

Update:

Actually on second read is evident that the INSERT has locks on different tables (unless you modfied the XML, which looks hand edited here and there), so your explanation about how the insert acts must be erroneous. The INSERT is part of a transaction than did at least two writes, one on Table1 and one on Table2. But this doesn't change much the problem nor the solution. Is true that you have the avenue of splitting the two writes in the transaction into separate transactions, but that obviously is the worst avenue.