主页 / dba / 问题 / 282786
Accepted
Kin Shah
Asked: 2021-01-08 14:45:30 +0800 CST

具有可重复读隔离级别的死锁

  • 772

尝试了解在调用存储过程时使用可重复读取隔离级别发生的死锁。

这是死锁 xml：

    <TextData>
  <deadlock-list>
 <deadlock victim="process6fac029848">
  <process-list>
   <process id="process6fac029848" taskpriority="0" logused="0" waitresource="KEY: 8:72078600230076416 (d3c590b351cd)" waittime="1171" 
            ownerId="26116633497" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.317" XDES="0x12553cbe3b0" 
            lockMode="U" schedulerid="3" kpid="19776" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" 
            lastbatchstarted="2021-01-07T10:35:38.310" lastbatchcompleted="2021-01-07T10:35:38.310" lastattention="1900-01-01T00:00:00.310" 
            clientapp=".Net SqlClient Data Provider" hostname="web01" hostpid="34852" loginname="someuser" 
            isolationlevel="repeatable read (3)" xactid="26116633497" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" 
            clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" 
            sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
   <process id="process6fab017088" taskpriority="0" logused="284" waitresource="KEY: 8:72064801531101184 (4b819525e255)" waittime="1283" 
   ownerId="26116632474" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.290" XDES="0x44037ec3b0" 
   lockMode="X" schedulerid="25" kpid="25152" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" 
   lastbatchstarted="2021-01-07T10:35:38.287" lastbatchcompleted="2021-01-07T10:35:38.283" lastattention="1900-01-01T00:00:00.283" 
   clientapp=".Net SqlClient Data Provider" hostname="web02" hostpid="57552" loginname="someuser" 
   isolationlevel="repeatable read (3)" xactid="26116632474" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72078600230076416" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_Id_Status_userid_UpdateTime" id="lock287c93a100" mode="U" associatedObjectId="72078600230076416">
    <owner-list>
     <owner id="process6fab017088" mode="U" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fac029848" mode="U" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72064801531101184" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_SomeId_Id" id="lock55fc471880" mode="S" associatedObjectId="72064801531101184">
    <owner-list>
     <owner id="process6fac029848" mode="S" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fab017088" mode="X" requestType="convert" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
</TextData>

具有相关索引的创建表：

CREATE TABLE [schema].[TableName](
    [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [SomeId] [int] NOT NULL,
    [Userid] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,
    [RecordStatus] [tinyint] NOT NULL,
 CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--- 2 indexes that are part of deadlock 
IX_TableName_SomeId_Id = filtered Index - SomeId, Id (include status) where status = 0
IX_TableName_Id_Status_userid_UpdateTime - non clustered index - Id include(status, userid, updatetime)

SP调用是：

  • 隔离级别可重复读
  • 开始交易
  • 从基表中进行选择并将受影响的行插入到临时变量中。
  • 执行另一个 SP
  • 更新基表。<<--- 这是发生死锁的地方
  • 将数据与一些业务逻辑合并
  • 如果没有错误，则提交事务

基表具有对审计表进行插入和更新以跟踪更改的触发器。

我只是想弄清楚为什么我会遇到太多死锁。

更新 ：

declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid

存储过程流控制：

variables 
    @id 
    @someid 
    
begin 
    set nocount on
    set transaction isolation level repeatable read
    set xact_abort on

    begin tran delete
    begin try

    declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid 

    some SP runs

    -- update statememt that causes deadlock 
    -- it basically sets status = 1 -- deleted (soft delete)

    -- merge data into tables using CTE and MERGE 
    END TRY

    begin catch 
        -- error handling 
        -- roll back tran 
    end catch

    if @@trancount > 0
    begin
        commit tran delete
    END

END
sql-server sql-server-2014

1 个回答

  1. Best Answer

    这是我的假设：

    让我们调用 proc P1和P2的死锁调用。

    P1和P2都使用过滤后的索引来定位行。因为这是没有其他提示的可重复读取，所以它们使用 S 锁，并且不释放它们。它们碰巧包括一个共同的行。我们将在过滤索引Row A中调用它的位置。

    此时 P1 和 P2 在A 行都有 S 锁。

    现在，更新开始。

    P1从另一个索引中读取以定位需要更新的行。它在读取时使用 U 锁，并在Row B上放置一个 U 锁，它实际上表示与 A 相同的行，只是在不同的索引中。

    现在P1在 A 行有一个 S 锁，在B行有一个 U 锁。

    P1看到它有一行要修改，并开始该过程。它还必须从过滤索引中删除该行，这将需要 X 锁。

    P1尝试将其 S 锁转换为 X 锁，但被P2的 S 锁阻止。

    P2开始更新。它试图获取B 行上的 U 锁，但被P1的 U 锁阻止。死锁。

    我相信在初始选择上使用 UPDLOCK 提示应该可以避免死锁。

    • 5

相关问题