CODEWITHSUNDEEP
t-sqldatabase-deadlocks
Andrew Bullock
Andrew Bullock

Reputation: 37398

How to prevent deadlocks occurring in this SQL

Firstly, I don't need to 100% prevent the deadlocks, but anything I can do to reduce them would be nice.

I have two tables Source and Dest. Source has a load of unique values in them, I need to request a new value from Source and in doing so, move it into Dest.

I have the following SQL:

begin tran
    declare @value
    select top 1 @value = [value] from [source]
    delete from [Source] where [value]=@value
    insert into [Dest] ([Value]) values (@value)
    select @value
commit tran

This occasionally throws deadlocks when multiple users get the same value row. How can I prevent/reduce this?

I'm using SQL Server 2008.

As an aside, there are other columns in Source and Dest that I am reading from/writing to. This is a simplification for brevity.

Upvotes: 2

Views: 1213

Answers (2)

Matt Faus
Matt Faus

Reputation: 6691

You could grab an XLOCK with the SELECT statement

begin tran
    declare @value
    select top 1 @value = [value] from [source] with (XLOCK)
    delete from [Source] where [value]=@value
    insert into [Dest] ([Value]) values (@value)
    select @value
commit tran

Upvotes: 1

Gerardo Lima
Gerardo Lima

Reputation: 6712

You can avoid this race condition by using the OUTPUT clause your the DELETE command, since it will delete the value from source and return it in a single atomic operation. I made the following script to demonstrate the concept:

-- dummy data
CREATE TABLE #source (mycolumn INT);
CREATE TABLE #destination (mycolumn INT);

INSERT #source VALUES (1);
INSERT #source VALUES (2);
INSERT #source VALUES (3);
GO

-- stored procedure to demonstrate concept
CREATE PROCEDURE move
AS BEGIN
    BEGIN TRANSACTION;

    DECLARE @tmp TABLE (mycolumn INT);
    DELETE TOP(1) #source OUTPUT DELETED.mycolumn INTO @tmp(mycolumn);

    INSERT #destination (mycolumn) OUTPUT INSERTED.mycolumn
    SELECT mycolumn
    FROM @tmp;

    COMMIT;
END
GO

-- testing
EXEC move;
GO -- remove from 1 from #source, insert 1 into #destination, returns 1

EXEC move;
GO -- remove from 2 from #source, insert 2 into #destination, returns 2

EXEC move;
GO -- remove from 3 from #source, insert 3 into #destination, returns 3

Upvotes: 3

Related Questions