在存储过程中执行多个插入时发生Sql Server死锁错误

Question

在sql server 2008中调用存储过程时，我遇到了死锁问题。xml字符串由biztalk传递到存储过程中，并且可以快速连续调用多次。我遇到的问题是，如果连续快速调用该过程5次，则前4次调用将回滚，最后一次调用将由于死锁而提交到数据库。以下是该过程的代码-它使用OPENXML解析xml字符串并插入表A。然后，我从表A中获取新的唯一标识符，并将多个子记录插入表B。如能提供有关如何解决此问题的任何指导，将不胜感激。

错误消息：System.Data.SqlClient.SqlException（0x80131904）：事务（进程ID XX）在与另一个进程的锁资源上死锁，已被选为死锁牺牲品。重新运行事务。

表格详细信息
表A
-Id int identity（1,1）主键，
-第A列varchar（15）不为null，
-第B列varchar（20）不为null，
-AddedDateTime日期时间默认值（getdate（））

表B
-Id int identity（1,1）主键，
-TableAId int不为null，（FK）
-第C列varchar（30）不为空

XML

<Transactions>
<Transaction>
    <ColumnA>Column A Value</ColumnA>
    <ColumnB>Column B Value</ColumnB>
    <ChildItems>
        <ChildItem>
            <ColumnC>Column C Value</ColumnC>
        </ChildItem>
        <ChildItem>
            <ColumnC>Another Column C Value</ColumnC>
        </ChildItem>
        <ChildItem>
            <ColumnC>Yet Another Column C Value</ColumnC>
        </ChildItem>
    </ChildItems>

存储过程

 CREATE PROCEDURE [dbo].[proc_ProcessXml] 
    (
        @ResponseXml varchar(max)
    )
    WITH EXECUTE AS CALLER  
    AS
    BEGIN TRANSACTION
    DECLARE @xmlHandle int
    declare @tableAId int
    EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @ResponseXml, 
    INSERT INTO TableA 
    (
        ColumnA,
        ColumnB             
   ) 
   SELECT columnA, columnB
    FROM OPENXML(@xmlHandle, '/Transactions/Transaction', 1)
    WITH(
        columnA varchar(15) 'ColumnA',      
        columnB varchar(20) 'ColumnB'
    )
    select @tableAId = SCOPE_IDENTITY()
    INSERT INTO TableB 
    (
        TableAId,
        ColumnC             
    )  
    SELECT @tableAId, columnC
    FROM OPENXML(@xmlHandle, '/Transactions/Transaction/ChildItems/ChildItem', 1) 
    WITH(       
        columnC varchar(30) 'ColumnC',
    )
    EXEC sp_xml_removedocument @xmlHandle
    IF @@ERROR <> 0
        BEGIN                   
            ROLLBACK
        END
    ELSE
        BEGIN           
            COMMIT
        END

Answer 1

这种行为可能有很多不同的原因，因此您需要了解更多事实。你需要知道隔离级别和他们为之奋斗的锁。我会这么做：

1. 在Mgmt Studio中准备好四个窗口来调用您的proc
2. 在调用proc（dbcc-useroptions）之前，请检查每个调用方的隔离级别
3. 识别四个呼叫者中的spid（@@spid）
4. 在从第五个会话开始测试之前，先拍下所有锁的快照：

select
     object_name(P.object_id) as TableName, L.*
into
    #preTestLocks
from     
    sys.dm_tran_locks L
    join sys.partitions P on L.resource_associated_entity_id = p.hobt_id
where
     object_name(P.object_id) in ('TableA','TableB')

1. 在TableA插入后的进程中添加一个等待（WAITFOR DELAY"00:00:30"），这样您就可以查看运动中的事物
2. 在每个会话中开始运行proc，但在每次启动后，从第五个窗口获取锁的快照：

select
     object_name(P.object_id) as TableName, L.*
into
    #lock1  --<<CHANGE AFTER EACH RUN (#lock2, #lock3 etc.)
from     
    sys.dm_tran_locks L
    join sys.partitions P on L.resource_associated_entity_id = p.hobt_id
where
    resource_session_id in (1,2,3,4) --<<YOUR SPID'S

分析结果，看看是什么资源导致了死锁情况。您可能会遇到将行级锁升级为页面级、扩展级甚至表级锁的锁升级问题。请阅读此处了解锁定模式的说明。

最后一个观察：

通过在proc内启动事务而不指定SET XACT_ABORT ON（有关详细信息，请参阅此处），您可能是在玩火。我怀疑这是否会导致你目前的行为，除非你的客户的超时时间非常短，但我强烈建议你补充这一点。