CODEWITHSUNDEEP
sqlsql-server
yeerk
yeerk

Reputation: 2697

Single Table Stored Procedure Deadlocks

I am trying to increment a value in MSSQL (ex, to increment a user's balance after buying a giftcard).

My stored procedure and table look like:

CREATE TABLE Test_Table ([intCount] [int] NOT NULL)

ALTER PROCEDURE Test_Proc AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN
        UPDATE Test_Table
        SET intCount = intCount + 1 
    COMMIT TRAN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    RETURN(0)
END

I only have one row in the table for simplicity and I just increment all rows.

I spawn 10 threads in C# and call the stored procedure 10 times in each thread. However I get deadlocks in most of the threads. My code that calls this stored procedure looks like:

for (int thread = 0; thread < threads; thread++)
{
    new Thread(() =>
    {
        try
        {
            for (int ix = 0; ix < count; ix++)
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("Test_Proc", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception e)
        {
            errors++;
        }
    }).Start();
}

I tried WITH (UPDLOCK, HOLDLOCK) but that didn't seem to reduce the frequency of deadlocks.

Is there anyway I can change the stored procedure to prevent these deadlocks? I am really looking for a SQL answer as opposed to just serializing all stored procedure calls in C#.

(This is similar to Deadlock with single stored procedure and multiple threads, however that question is explicitly asking for how to deadlock, while I just want to avoid a deadlock).

EDIT: I modified the code to put the contents of the stored procedure in a transaction, but it still deadlocks.

EDIT: The error message looks like Transaction (Process ID 124) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

EDIT: I updated the stored procedure per feedback, but it still deadlocks.

EDIT: It looks like the explanation is in: https://stackoverflow.com/a/36831413/1117119 . SERIALIZABLE gets a shared lock, and then converts it to a exclusive lock when it is going to write. This results in a deadlock.

Also it appears that any further deadlocks occur because I simply have too many threads: SQL Server 2008: Getting deadlocks... without any locks .

Upvotes: 1

Views: 1329

Answers (2)

yeerk
yeerk

Reputation: 2697

Dean's answer is the most correct, but in the case anyone else wants to know how to make a separate SELECT and UPDATE work correctly, this is the code I ended up with:

BEGIN TRAN

    DECLARE @x int

    select @x = intCount
    from Test WITH (TABLOCKX, UPDLOCK)
    WHERE id = 1    

    UPDATE Test
    SET intCount = @x + 1
    WHERE id = 1    

COMMIT TRAN

This resolves the issue of the first select not taking an exclusive lock (https://stackoverflow.com/a/36831413/1117119).

Also it looks like any code that acquires locks will deadlock if too many threads are created (SQL Server 2008: Getting deadlocks... without any locks).

Upvotes: 1

dean
dean

Reputation: 10098

I understand that what you've showed us is just a simlified version of the problem, but anyway consider whether instead of separate SELECT and UPDATE you're able to do it as a single UPDATE, ie:

UPDATE Test_Table SET intCount += 1

If this is not possible, then enclose both statement into a single transaction.

EDIT: To avoid the convert lock deadlocks, add with(xlock, tablock) hint to the SELECT statement.

Upvotes: 2

Related Questions