CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdeadlock
derekmw
derekmw

Reputation: 375

Getting deadlocks on MS SQL stored procedure performing a read/update (put code to handle deadlocks)

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

Upvotes: 0

Views: 926

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 2

TomTom
TomTom

Reputation: 62093

Well, yur problem is 2that you have 2 statements - a select and an update. if those run concurrent, then the select will..... make a read lock and the update will demand a write lock. At the same time 2 machins deadlock.

Simple solution is to make the initial select demand an uddate lock (WITH (ROWLOCK, UPDLOCK) as hint). That may or may not work (depends on what else goes on) but it is a good start.

Second step - if that fails - is to use an application elvel lock (sp_getapplock) that makes sure a critical system always has only one owner and htus only exeutes transactions serially.

Upvotes: 1

Related Questions