在执行读取/更新的MS SQL存储过程上获取死锁（输入代码以处理死锁） [英] Getting deadlocks on MS SQL stored procedure performing a read/update (put code to handle deadlocks)

问题描述

我必须承认，我只是在学习如何正确处理死锁，但是根据我读到的建议，我认为这是处理死锁的正确方法。基本上，我有很多流程试图在数据库中保留一行以进行更新。因此，我首先读取可用行，然后写入。这不是正确的方法吗？如果是这样，我该如何解决此SP？

I have to admit I'm just learning about properly handling deadlocks but based on suggestions I read, I thought this was the proper way to handle it. Basically I have many processes trying to 'reserve' a row in the database for an update. So I first read for an available row, then write to it. Is this not the right way? If so, how do I need to fix this SP?

CREATE PROCEDURE [dbo].[reserveAccount] 
    -- Add the parameters for the stored procedure here
    @machineId varchar(MAX)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
    declare @id BIGINT;

    set @id = (select min(id) from Account_Data where passfail is null and reservedby is null);

    update Account_data set reservedby = @machineId where ID = @id;

    COMMIT TRANSACTION;
END

推荐答案

您可以将其写为单一声明。这样可以解决更新问题：

You can write this as a single statement. That will may fix the update problem:

update Account_data
    set reservedby = @machineId
    where ID = (select min(id) from Account_Data where passfail is null and reservedby is null);

这篇关于在执行读取/更新的MS SQL存储过程上获取死锁（输入代码以处理死锁）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！