Steffy Alen
more

Stored Procedure deadlocking when running Tasks inside Parallel.ForEach

I have a service that is called concurrently by clients up to 300 calls per second are possible. This service calls an insert stored procedure that takes the login information as well as other data and inserts it into the database. A separate web service not owned by me waits for this insert so that it can send back critical information to the client so this has to move as quickly as possible.

I figured the fastest way to process these calls were to make each client call run on its own separate thread which also will insert into the database on that thread. The problem I am running into is deadlocking as too many calls are trying to hit this stored procedure at one time and it is locking the database.

The error I get is

Transaction(Process ID 239) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

This method will below mimic how to run many calls per second as is happening in production:

 var results = Enumerable.Range(0, 50000)
                         .AsParallel()
                         .WithDegreeofParallelism(Environment.ProcessorCount)
                         .Select(x => InsertPayloadtoDB(xmlList))
                         .ToList()

The payload is an XML that is sent to the stored procedure for processing.

public void InsertPayloadtoDB(XmlElement xmlList)
{
    var task = Task.Run(async () => await ExecuteNonQueryAsync("MyStoredProcedure",
        new List<SqlParameter>{ new SqlParameter("@xmlList", xmlList.ToString())}));
}

This is the connection string and I tried max pooling, MARS but neither are really helping. Max pooling helps as without it I get pool exhaustion errors, but for the other params I’m not sure if they are even needed:

private static readonly string connection = "dbname;username;password;" +
    "Multisubnetfailover=true;Min Pool Size = 0; Max Pool Size=10000;Pooling=true;" +
    "MultipleActiveResultSets=true;"
    
public static Task<bool> ExecuteNonQueryAsync(string storedProcedureName,
    List<SqlParameter> parameters) 
{
    using(var connection = new SqlConnection(connection))
    {
        using(var cmd = new SqlCommand(storedProcedureName, connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 300;
            cmd.Parameters.AddRange(parameters);
            await connection.OpenAsync().ConfigureAwait(false);
            await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
            cmd.Parameters.Clear();
        }

        connection.Close();
    }
}

Finally here is the stored procedure. I find that inserting into the actual table is what is causing the deadlock. I can create the temp table and parse the xml fine but when I uncomment the actual table insert, deadlocks happen when I get to about 1000 concurrent connections:

DECLARE @xmlList = NULL

BEGIN
    DECLARE @MyTable TABLE
                     (
                         First varchar(40),
                         Last varchar(40),
                         Address varchar(50),
                         -- ... 15 more columns
                     )

    BEGIN TRANSACTION
        INSERT INTO @MyTable (First varchar(40),
                              Last varchar(40),
                              Address varchar(50),
                              --... 15 more columns
                             )
            SELECT
                N.value('(Name)[1]', 'varchar(40)') AS Name
                N.value('(Last)[1]', 'varchar(40)') AS Last
                N.value('(Address)[1]', 'varchar(50)') AS Address
            FROM
                @xmlList.nodes('//ClientInfo') AS T(N)

        // This is where the deadlock seems to happen as when 
        // I comment this out everything is smooth sailing. 
        INSERT INTO ClientInfoTable (Name, Last, Address, ...)
            SELECT
                Name, Last,
                Address,
                ...
            FROM @MyTable
       )

        COMMIT TRANSACTION
END
Back to Top