SQL Server INSERT 和 SELECT 语句之间出现死锁

Question

我在 SQL Server 2005 上遇到了多个死锁的问题。该语句位于 INSERT 和 SELECT 语句之间。

有两个表。表1和表2。 Table2 将 Table1 的 PK (table1_id) 作为外键。
table1_id 上的索引是聚集的。

INSERT 一次将一行插入到 table2 中。
SELCET 连接两个表。 (这是一个很长的查询，可能需要长达 12 秒才能运行)

根据我的理解(和实验)，INSERT 应该获取 table1 上的 IS 锁来检查引用完整性(这不应导致死锁)。但是，在这种情况下，它获取了 IX 页面锁

死锁报告:

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

任何人都可以解释为什么 INSERT 会获得 IX 页面锁定吗？
我是否没有正确阅读死锁报告？
顺便说一句，我还没有成功重现这个问题。

谢谢!

编辑:表创建:

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

I 表示“意图”锁，它们始终与层次结构相关联。由于锁管理器不了解物理结构，因此他不可能遵守分层锁，因此在意向锁中重新创建了分层结构。

在您的情况下，INSERT 在页面上有一个意向锁。这意味着它还获得了页面中某行的 X 锁，这是正常行为。它现在尝试获取新的 IX 锁，因此它可能需要在不同的页面中插入一行。这是插入具有多个索引的表的正常行为:第一个 IX 位于其中一个索引(可能是聚集索引)上，第二个 IX 位于非聚集索引上。

你说的SELECT在12秒内返回，所以它是一个长查询，在一个大数据集上，并且该计划可能选择了高锁定粒度，页锁。 SELECT 在 INSERT 想要 IX 锁的页面上有一个 S 锁，并且在 INSERT 有 IX 锁的页面上想要另一个 S 锁。

这是一个微不足道的死锁，应该很容易修复它:确保您的 SELECT 不需要这些页面 S 锁。这不是 INSERT 错误。不知道 SELECTt 的作用我无法确定是否是最佳的。根据我的经验，像这样的 SELECT 几乎总是有很多很多的改进空间(无论是 SELECT 本身还是它下面的架构)。

但是承认 SELECT 是最佳选择，最简单的出狱卡就是打开 row versioning :

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

更新:

实际上，在第二次读取时，很明显 INSERT 已锁定不同的表(除非您修改了 XML，它看起来到处都是手工编辑的)，因此您对插入行为的解释必须错误。 INSERT 是至少两次写入的事务的一部分，一次写入表 1，一次写入表 2。但这并没有改变问题或解决方案。确实，您可以将事务中的两个写入拆分为单独的事务，但这显然是最糟糕的途径。