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?