SQL Server Deadlock Between INSERT and SELECT

Question

I have a problem with multiple deadlocks on SQL Server 2005. It is between INSERT and SELECT statements.

There are two tables. Table 1 and Table 2. Table 2 has PK table 1 (table1_id) as a foreign key.
The index of table 1_id is grouped.

INSERT inserts one row into table2 at a time.
SELCET joins 2 tables. (This is a long request, which may take up to 12 seconds)

According to my understanding (and experiments), INSERT should get an IS lock on table1 to check referential integrity (which should not cause a deadlock). But in this case, he acquired a lock on page IX

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 someone explain why INSERT gets a lock on page IX?
Am I not reading the deadlock report properly?
By the way, I was not able to reproduce this problem.

Thanks!

EDIT: CREATING TABLES:

 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

In punt, I would say that DatabaseName.dbo.prcTable2_Insert is executed inside a transaction or explicitly opens it, and it (or a connection to an open transaction) has already nested in table 1 in advance.

Answer 2

I stands for intent lock, and they are always associated with hierarchies. Since the lock manager does not understand the physical structure, it is impossible for him to observe hierarchical locks, so the hierarchy is recreated into intent locks.

In your case, INSERT has an intent lock on the page. This means that he also got an X lock on the line on the page, which is normal behavior. Now he is trying to get a new IX lock, so you probably need to insert a line into another page. This would be the normal behavior of inserting into a table with several indexes: the first IX is on one of the indices (possibly clustered), and the second IX is on a non-clustered index.

The SELECT SELECT you say returns after 12 seconds, so its long query on a large dataset and plan probably chose a high degree of blocking, page blocking. SELECT has an S-lock on the page that INSERT wants to block IX, and wants the S-lock on which the INSERT has IX lock on the page.

This is a trivial dead end and is very easy to fix: make sure your SELECT does not need these S-locks. There is no INSERT error here. Not knowing what SELECt is, I cannot say for sure whether it is optimal or not. In my experience, almost always in SELECT, like this, there are many, many and many opportunities for improvement (eitehr SELECT or the circuit below it).

But, assuming SELECT is optimal, your easiest jailbreak card should include row versioning :

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

Update:

Actually, in the second reading, it is obvious that INSERT has locks in different tables (if you have not modified the XML, which looks manually edited here and there), so your explanation of how the insertion actions should be erroneous. INSERT is part of a transaction than at least two records: one of tables 1 and one of tables2. But this does not greatly change the problem or solution. It is true that you have the ability to split the two records in a transaction into separate transactions, but this is obviously the worst way.