Transaction was deadlocked on lock resources

Viewed 45

I have this class which allow to send update request to the database through Dapper in Serializable transaction:

    public async Task<int> UpdateTable(UpdateModel model)
    {
        await using var connection = createConnection();
        await connection.OpenAsync();
        await using var tx = connection.BeginTransaction(IsolationLevel.Serializable);
        var rowsCount = await connection.ExecuteAsync(@$"

            UPDATE Table WITH (UPDLOCK, SERIALIZABLE)
            SET
                Date = @Date,
                Attempts = Attempts + 1
            WHERE
                Id = @Id
                AND DATEDIFF(MINUTE, Date, GETDATE()) < 60",
            new
            {
                model.Id,
                model.Date
            }, commandTimeout: connection.CommandTimeout, transaction: tx);
        tx.Commit();
        return rowsCount;
    }

I send 10 requests async and print result like this:

var count = 10;

while (true)
{
    var tasks = new List<Task>();
    var providerId = Guid.NewGuid();
    for (var i = 0; i < count; i++)
        tasks.Add(upsert(providerId));

    var result = Task.WhenAll(tasks.ToArray());

    result.Wait();
    Thread.Sleep(5000);
    Console.WriteLine("------------------------------------------------------------------");
}

async Task upsert(Guid id)
{
    var response = await client.Upsert(new UpsertModel
    {
        Id = id,
        Date = DateTime.Now
    });

    Console.WriteLine($"RowsCount: {response.Content}, Id: {id}");
}

When I sent 10 async requests I receive deadlocks for 9 request from 10. The message looks look like this: Transaction (Process ID 67) was deadlocked on lock resources with anot her process and has been chosen as the deadlock victim. Rerun the transaction.

Why I recieve deadlocks? And what I need to do that I can avoid deadlock?

1 Answers

In my answer, I will assume you are using SQL Server, since UPDLOCK is a SQL Server specific keyword.

Let's refer to the table hints docs

There is a big red warning:

Attention

Because the SQL Server Query Optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

UPDLOCK will put an exclusive lock the whole table if it has any good reason to do so. Since you build your query to update different rows, you may use the ROWLOCK hint, provided you do not update ten of thousands of rows with a single UPDATE instruction.

The simpler way to deal with your problem, is, as the Micorsoft page puts it, to not use table hints.

Related