SqlException: Transaction (Process ID xx) was deadlocked on resources...

await using var context1 = new AppDbContext();
await using var context2 = new AppDbContext();

var order1 = await context1.Orders.FindAsync(1);
var order2 = await context2.Orders.FindAsync(1);

order1.Status = "Paid";
order2.Status = "Shipped";

await context1.SaveChangesAsync(); // Блокирует строку
await context2.SaveChangesAsync(); // Возможен deadlock

await using var tx = await context.Database.BeginTransactionAsync();
var order = await context.Orders.FindAsync(orderId);
order.Status = "Paid";
await context.SaveChangesAsync();
await tx.CommitAsync();

await using var tx = await context.Database.BeginTransactionAsync(
    IsolationLevel.ReadCommitted);

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString, sql =>
        sql.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(5),
            errorNumbersToAdd: null)));

public class Order
{
    public int Id { get; set; }
    public string Status { get; set; }
    
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

public async Task<bool> SaveWithRetryAsync(DbContext context, int maxRetries = 3)
{
    for (int attempt = 0; attempt < maxRetries; attempt++)
    {
        try
        {
            await context.SaveChangesAsync();
            return true;
        }
        catch (DbUpdateException ex) when (IsDeadlock(ex))
        {
            if (attempt == maxRetries - 1)
                throw;
                
            // Экспоненциальная задержка: 100ms, 200ms, 400ms
            var delay = TimeSpan.FromMilliseconds(100 * Math.Pow(2, attempt));
            await Task.Delay(delay);
            
            Console.WriteLine($"Deadlock detected, retry {attempt + 1}/{maxRetries}");
        }
    }
    
    return false;
}

bool IsDeadlock(Exception ex) =>
    ex.InnerException is SqlException sqlEx && 
    (sqlEx.Number == 1205 || // Deadlock victim
     ex.InnerException.Message.Contains("deadlocked"));

public class OrderProcessor
{
    private readonly SemaphoreSlim _semaphore = new SemaphoreSlim(5); // Макс 5 одновременно
    
    public async Task ProcessOrdersAsync(List<int> orderIds)
    {
        var tasks = orderIds.Select(id => ProcessWithSemaphoreAsync(id));
        await Task.WhenAll(tasks);
    }
    
    private async Task ProcessWithSemaphoreAsync(int orderId)
    {
        await _semaphore.WaitAsync();
        try
        {
            await using var context = new AppDbContext();
            var order = await context.Orders.FindAsync(orderId);
            order.Status = "Processed";
            await SaveWithRetryAsync(context);
        }
        finally
        {
            _semaphore.Release();
        }
    }
}

catch (DbUpdateException ex) when (IsDeadlock(ex))
{
    _logger.LogWarning(ex, 
        "Deadlock on order {OrderId}, attempt {Attempt}/{MaxAttempts}", 
        orderId, attempt + 1, maxRetries);
    
    // Отправьте метрику в мониторинг
    _telemetry.TrackMetric("Deadlocks", 1, new Dictionary<string, string>
    {
        ["Entity"] = "Order",
        ["Operation"] = "Update"
    });
}

-- Extended Events для захвата deadlock
CREATE EVENT SESSION [DeadlockMonitoring] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename = N'C:\Logs\Deadlocks.xel')
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);

ALTER EVENT SESSION [DeadlockMonitoring] ON SERVER STATE = START;