Blocking, deadlock và thử lại giao dịch

SELECT r.session_id, r.blocking_session_id, r.wait_type,
       r.wait_time AS wait_ms, r.wait_resource,
       SUBSTRING(t.text, r.statement_start_offset / 2 + 1,
           (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                 ELSE r.statement_end_offset END
            - r.statement_start_offset) / 2 + 1) AS cau_dang_chay
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.status,
       s.open_transaction_count, s.last_request_end_time, ib.event_info AS lenh_cuoi
FROM sys.dm_exec_sessions AS s
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE EXISTS (SELECT 1 FROM sys.dm_exec_requests AS r
              WHERE r.blocking_session_id = s.session_id)
  AND NOT EXISTS (SELECT 1 FROM sys.dm_exec_requests AS r
                  WHERE r.session_id = s.session_id AND r.blocking_session_id > 0);

SELECT st.session_id, at.name AS transaction_name, at.transaction_begin_time,
       DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS so_phut_mo,
       dt.database_transaction_log_record_count AS so_log_record,
       dt.database_transaction_log_bytes_used / 1024 AS log_kb
FROM sys.dm_tran_session_transactions AS st
INNER JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_tran_database_transactions AS dt
    ON dt.transaction_id = st.transaction_id AND dt.database_id = DB_ID(N'BanHang')
ORDER BY at.transaction_begin_time;

<deadlock>
  <victim-list><victimProcess id="process2b7c" /></victim-list>
  <process-list>
    <process id="process2b7c" spid="71" lockMode="U" isolationlevel="read committed (2)"
             waitresource="KEY: 7:72057594047823872 (8194443284a0)" transactionname="user_transaction" />
    <process id="process1f2a" spid="64" lockMode="U" isolationlevel="read committed (2)"
             waitresource="KEY: 7:72057594048086016 (61a06abd401c)" transactionname="user_transaction" />
  </process-list>
  <resource-list>
    <keylock objectname="BanHang.dbo.DonHang" indexname="PK_DonHang" mode="X">
      <owner-list><owner id="process1f2a" mode="X" /></owner-list>
      <waiter-list><waiter id="process2b7c" mode="U" requestType="wait" /></waiter-list>
    </keylock>
    <keylock objectname="BanHang.dbo.SanPham" indexname="PK_SanPham" mode="X">
      <owner-list><owner id="process2b7c" mode="X" /></owner-list>
      <waiter-list><waiter id="process1f2a" mode="U" requestType="wait" /></waiter-list>
    </keylock>
  </resource-list>
</deadlock>

using System;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

public static class DonHangService
{
    private const int SoLanThuToiDa = 4;

    // 1205: nạn nhân deadlock. 3960: xung đột cập nhật dưới SNAPSHOT.
    // Với cả hai lỗi, SQL Server đã rollback toàn bộ giao dịch trước khi báo về.
    private static bool NenThuLai(SqlException ex) => ex.Number is 1205 or 3960;

    // dong: DataTable với các cột DongSo (short), SanPhamId (int), SoLuong (int),
    // đúng thứ tự cột của dbo.DongDonHang.
    public static async Task TaoDonHangAsync(string chuoiKetNoi, long donHangId, DateTime ngayTao,
        int khachHangId, DataTable dong, CancellationToken ct = default)
    {
        for (int lan = 1; ; lan++)
        {
            try
            {
                using var ketNoi = new SqlConnection(chuoiKetNoi);
                await ketNoi.OpenAsync(ct);

                using var lenh = new SqlCommand("dbo.usp_DonHang_Tao", ketNoi)
                {
                    CommandType = CommandType.StoredProcedure,
                    CommandTimeout = 30
                };
                lenh.Parameters.Add("@DonHangId", SqlDbType.BigInt).Value = donHangId;
                lenh.Parameters.Add("@NgayTao", SqlDbType.DateTime2).Value = ngayTao;
                lenh.Parameters.Add("@KhachHangId", SqlDbType.Int).Value = khachHangId;
                SqlParameter tvp = lenh.Parameters.Add("@Dong", SqlDbType.Structured);
                tvp.TypeName = "dbo.DongDonHang";
                tvp.Value = dong;

                await lenh.ExecuteNonQueryAsync(ct);
                return;
            }
            catch (SqlException ex) when (NenThuLai(ex) && lan < SoLanThuToiDa)
            {
                // Chờ ngẫu nhiên, tăng dần, để hai giao dịch không va lại đúng nhịp cũ.
                await Task.Delay(Random.Shared.Next(50, 250) * lan, ct);
            }
        }
    }
}

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.12
#:property PublishAot=false
// Deadlock lúc 16:40 và xung đột SNAPSHOT, chạy qua execution strategy của EF Core.
// Chạy: dotnet run Deadlock.cs   (BANHANG_DB: chuỗi kết nối tới database có dbo.SanPham và dbo.DonHang)
using System.Data;
using System.Diagnostics;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

string cs = Environment.GetEnvironmentVariable("BANHANG_DB")
    ?? @"Server=(localdb)\MSSQLLocalDB;Database=BanHang_Thu;Integrated Security=true;TrustServerCertificate=true";

await using (var db = new BanHangDb(cs, thuLai: false))
{
    await db.Database.ExecuteSqlRawAsync("ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON");
    await db.Database.ExecuteSqlAsync($"""
        UPDATE dbo.SanPham SET TonKho = 7 WHERE SanPhamId = 42;
        UPDATE dbo.DonHang SET TongTien = 1750000.00
        WHERE DonHangId = 10042 AND NgayTao = '2026-10-02T11:58:00';
        """);
}

// 0. Giao dịch tự mở ngoài execution strategy
try
{
    await using var db = new BanHangDb(cs, thuLai: true);
    await using var tx = await db.Database.BeginTransactionAsync();
    await db.Database.SqlQuery<int>($"SELECT TonKho AS Value FROM dbo.SanPham WHERE SanPhamId = 42").SingleAsync();
}
catch (InvalidOperationException ex) { Console.WriteLine($"0. {ex.Message.Split(". ")[0]}."); }

// 1. Phiên A trả hàng (đơn rồi kho), phiên B thêm hàng (kho rồi đơn): thứ tự ngược nhau.
FormattableString giamDon = $"UPDATE dbo.DonHang SET TongTien = TongTien - 250000.00 WHERE DonHangId = 10042 AND NgayTao = '2026-10-02T11:58:00'";
FormattableString tangDon = $"UPDATE dbo.DonHang SET TongTien = TongTien + 250000.00 WHERE DonHangId = 10042 AND NgayTao = '2026-10-02T11:58:00'";
FormattableString congKho = $"UPDATE dbo.SanPham SET TonKho = TonKho + 1 WHERE SanPhamId = 42";
FormattableString truKho = $"UPDATE dbo.SanPham SET TonKho = TonKho - 1 WHERE SanPhamId = 42";
foreach (bool thuLai in new[] { false, true })
{
    var gap = new Gap(2);
    var dongHo = Stopwatch.StartNew();
    var kq = await Task.WhenAll(
        Task.Run(() => ChayGiaoDich("A", giamDon, congKho, thuLai, gap, dongHo)),
        Task.Run(() => ChayGiaoDich("B", truKho, tangDon, thuLai, gap, dongHo)));
    Console.WriteLine($"1. {(thuLai ? "EnableRetryOnFailure" : "không thử lại")}: {string.Join(" | ", kq)}");
}

// 2. A đọc tồn dưới SNAPSHOT, B trừ kho và commit, rồi A trừ kho: lỗi 3960.
var aDaDoc = new TaskCompletionSource();
var bDaGhi = new TaskCompletionSource();
int lanA = 0;
Console.Write("2. SNAPSHOT, EnableRetryOnFailure mặc định: A ");
var phienA = Task.Run(async () =>
{
    await using var db = new BanHangDb(cs, thuLai: true);
    await db.Database.CreateExecutionStrategy().ExecuteAsync(async () =>
    {
        lanA++;
        await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.Snapshot);
        int ton = await db.Database.SqlQuery<int>($"SELECT TonKho AS Value FROM dbo.SanPham WHERE SanPhamId = 42").SingleAsync();
        Console.Write($"lần {lanA} đọc {ton}");
        if (lanA == 1) { aDaDoc.SetResult(); await bDaGhi.Task; }
        try { await db.Database.ExecuteSqlAsync($"UPDATE dbo.SanPham SET TonKho = TonKho - 1 WHERE SanPhamId = 42 AND TonKho >= 1"); }
        catch (SqlException ex) { Console.Write($", lỗi {ex.Number}; "); throw; }
        await tx.CommitAsync();
        Console.WriteLine(", commit");
    });
});
await aDaDoc.Task;
await using (var db = new BanHangDb(cs, thuLai: false))
    await db.Database.ExecuteSqlAsync(truKho);
bDaGhi.SetResult();
await phienA;

async Task<string> ChayGiaoDich(string ten, FormattableString cau1, FormattableString cau2, bool thuLai, Gap gap, Stopwatch dongHo)
{
    int lan = 0;
    await using var db = new BanHangDb(cs, thuLai);
    try
    {
        await db.Database.CreateExecutionStrategy().ExecuteAsync(async () =>
        {
            lan++;
            await using var tx = await db.Database.BeginTransactionAsync();
            await db.Database.ExecuteSqlAsync(cau1);
            if (lan == 1) await gap.DenAsync();     // cả hai đã giữ khóa đầu tiên
            await db.Database.ExecuteSqlAsync(cau2);
            await tx.CommitAsync();
        });
        return $"{ten} commit sau {lan} lần chạy, {dongHo.ElapsedMilliseconds} ms";
    }
    catch (Exception ex) when ((ex as SqlException ?? ex.InnerException as SqlException) is { } sql)
    {
        return $"{ten} nhận {ex.GetType().Name} (lỗi {sql.Number}) sau {lan} lần chạy, {dongHo.ElapsedMilliseconds} ms";
    }
}

public class BanHangDb(string cs, bool thuLai) : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder o) =>
        o.UseSqlServer(cs, sql => { if (thuLai) sql.EnableRetryOnFailure(); });
}

sealed class Gap(int soBen)
{
    int _con = soBen;
    readonly TaskCompletionSource _du = new(TaskCreationOptions.RunContinuationsAsynchronously);
    public Task DenAsync() { if (Interlocked.Decrement(ref _con) == 0) _du.SetResult(); return _du.Task; }
}

0. The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions.
1. không thử lại: A commit sau 1 lần chạy, 1948 ms | B nhận InvalidOperationException (lỗi 1205) sau 1 lần chạy, 1920 ms
1. EnableRetryOnFailure: A commit sau 1 lần chạy, 4968 ms | B commit sau 2 lần chạy, 4976 ms
2. SNAPSHOT, EnableRetryOnFailure mặc định: A lần 1 đọc 8, lỗi 3960; lần 2 đọc 7, commit