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?

Im 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.

Thanks

Answers


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

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

Need Your Help

Regular expression for 1 or more

regex

What is the a regular expression to only allow whole numbers from 1 through 99? (including both 1 and 99)

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.