Chuyển tiền giữa hai ví: sổ cái bút toán kép và deadlock khi chuyển chéo

21:04:07,120  Phiên L (Lan → Minh 350.000) đọc: Lan 1.000.000, Minh 500.000
21:04:07,135  Phiên M (Minh → Lan 120.000) đọc: Minh 500.000, Lan 1.000.000
21:04:07,160  Phiên L ghi Lan = 650.000, Minh = 850.000, COMMIT
21:04:07,190  Phiên M ghi Minh = 380.000, Lan = 1.120.000, COMMIT

CREATE TABLE dbo.Vi (
    ViId int NOT NULL CONSTRAINT PK_Vi PRIMARY KEY CLUSTERED,
    KhachHangId int NULL,                -- NULL với tài khoản hệ thống
    LoaiVi tinyint NOT NULL,             -- 1 ví khách, 2 ví cửa hàng, 9 đối ứng ngân hàng liên kết
    SoDu decimal(18, 2) NOT NULL CONSTRAINT DF_Vi_SoDu DEFAULT 0,
    CONSTRAINT CK_Vi_SoDu CHECK (SoDu >= 0 OR LoaiVi = 9)
);

CREATE TABLE dbo.LenhChuyen (
    LenhChuyenId bigint IDENTITY(1, 1) NOT NULL CONSTRAINT PK_LenhChuyen PRIMARY KEY CLUSTERED,
    MaLenh uniqueidentifier NOT NULL,    -- client sinh, gửi lại y nguyên ở mọi lần thử
    ViGui int NOT NULL CONSTRAINT FK_LenhChuyen_ViGui REFERENCES dbo.Vi (ViId),
    ViNhan int NOT NULL CONSTRAINT FK_LenhChuyen_ViNhan REFERENCES dbo.Vi (ViId),
    SoTien decimal(18, 2) NOT NULL CONSTRAINT CK_LenhChuyen_SoTien CHECK (SoTien > 0),
    TaoLuc datetime2(3) NOT NULL CONSTRAINT DF_LenhChuyen_TaoLuc DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_LenhChuyen_MaLenh UNIQUE (ViGui, MaLenh),
    CONSTRAINT CK_LenhChuyen_HaiVi CHECK (ViGui <> ViNhan)
);

CREATE TABLE dbo.ButToan (
    ButToanId bigint IDENTITY(1, 1) NOT NULL CONSTRAINT PK_ButToan PRIMARY KEY CLUSTERED,
    LenhChuyenId bigint NOT NULL CONSTRAINT FK_ButToan_LenhChuyen REFERENCES dbo.LenhChuyen (LenhChuyenId),
    ViId int NOT NULL CONSTRAINT FK_ButToan_Vi REFERENCES dbo.Vi (ViId),
    SoTien decimal(18, 2) NOT NULL CONSTRAINT CK_ButToan_SoTien CHECK (SoTien <> 0)  -- âm: ví giảm, dương: ví tăng
);

CREATE INDEX IX_ButToan_Vi ON dbo.ButToan (ViId) INCLUDE (SoTien);

SELECT
    (SELECT COUNT(*) FROM dbo.Vi AS v
     WHERE v.SoDu <> (SELECT ISNULL(SUM(b.SoTien), 0) FROM dbo.ButToan AS b WHERE b.ViId = v.ViId)) AS ViLech,
    (SELECT COUNT(*) FROM dbo.Vi AS v
     WHERE v.LoaiVi <> 9 AND (SELECT SUM(b.SoTien) FROM dbo.ButToan AS b WHERE b.ViId = v.ViId) < 0) AS ViAmTheoSoCai,
    (SELECT COUNT(*) FROM (SELECT LenhChuyenId FROM dbo.ButToan GROUP BY LenhChuyenId
                           HAVING SUM(SoTien) <> 0 OR COUNT(*) <> 2) AS x) AS LenhKhongCan,
    (SELECT SUM(SoTien) FROM dbo.ButToan) AS TongButToan,
    (SELECT SUM(SoDu) FROM dbo.Vi) AS TongSoDu;

CREATE OR ALTER PROCEDURE dbo.usp_LenhChuyen_Tao
    @MaLenh uniqueidentifier, @ViGui int, @ViNhan int, @SoTien decimal(18, 2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @ViNho int = IIF(@ViGui < @ViNhan, @ViGui, @ViNhan);
    DECLARE @ViLon int = IIF(@ViGui < @ViNhan, @ViNhan, @ViGui);
    DECLARE @LenhChuyenId bigint, @SoViKhoa int = 0;
    BEGIN TRY
        BEGIN TRANSACTION;
        -- 1. Khóa hai ví theo thứ tự ViId tăng dần. Khóa U giữ đến hết giao dịch.
        SELECT @SoViKhoa += 1 FROM dbo.Vi WITH (UPDLOCK, ROWLOCK) WHERE ViId = @ViNho;
        SELECT @SoViKhoa += 1 FROM dbo.Vi WITH (UPDLOCK, ROWLOCK) WHERE ViId = @ViLon;
        IF @SoViKhoa <> 2
            THROW 50011, N'Ví không tồn tại.', 1;
        -- 2. Lệnh gửi lại với cùng MaLenh: trả lệnh cũ, không chuyển lần hai.
        SELECT @LenhChuyenId = LenhChuyenId FROM dbo.LenhChuyen
        WHERE ViGui = @ViGui AND MaLenh = @MaLenh;
        IF @LenhChuyenId IS NULL
        BEGIN
            -- 3. Trừ có điều kiện rồi cộng. Cả hai dòng đã nằm trong tay phiên này.
            UPDATE dbo.Vi SET SoDu = SoDu - @SoTien WHERE ViId = @ViGui AND SoDu >= @SoTien;
            IF @@ROWCOUNT = 0
                THROW 50010, N'Số dư không đủ.', 1;
            UPDATE dbo.Vi SET SoDu = SoDu + @SoTien WHERE ViId = @ViNhan;
            -- 4. Một lệnh, hai bút toán, tổng bằng 0.
            INSERT INTO dbo.LenhChuyen (MaLenh, ViGui, ViNhan, SoTien) VALUES (@MaLenh, @ViGui, @ViNhan, @SoTien);
            SET @LenhChuyenId = SCOPE_IDENTITY();
            INSERT INTO dbo.ButToan (LenhChuyenId, ViId, SoTien)
            VALUES (@LenhChuyenId, @ViGui, -@SoTien), (@LenhChuyenId, @ViNhan, @SoTien);
        END;
        COMMIT TRANSACTION;
        SELECT @LenhChuyenId AS LenhChuyenId;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

#:package Microsoft.Data.SqlClient@7.1.1
using Microsoft.Data.SqlClient;

string cach = args[0];                           // doc-ghi | tru-cong | thu-tu
string cs = args.Length > 1 ? args[1] : @"Server=(localdb)\MSSQLLocalDB;Database=BHPay;Integrated Security=true";
const string SoCai = """
    INSERT INTO dbo.LenhChuyen (MaLenh, ViGui, ViNhan, SoTien) VALUES (@MaLenh, @ViGui, @ViNhan, @SoTien);
    DECLARE @Id bigint = SCOPE_IDENTITY();
    INSERT INTO dbo.ButToan (LenhChuyenId, ViId, SoTien) VALUES (@Id, @ViGui, -@SoTien), (@Id, @ViNhan, @SoTien);
    """;
string lenh = cach == "thu-tu" ? "EXEC dbo.usp_LenhChuyen_Tao @MaLenh, @ViGui, @ViNhan, @SoTien;" : $"""
    SET XACT_ABORT ON; BEGIN TRANSACTION;
    UPDATE dbo.Vi SET SoDu = SoDu - @SoTien WHERE ViId = @ViGui AND SoDu >= @SoTien;
    IF @@ROWCOUNT = 0 THROW 50010, N'Số dư không đủ.', 1;
    UPDATE dbo.Vi SET SoDu = SoDu + @SoTien WHERE ViId = @ViNhan;
    {SoCai} COMMIT;
    """;
await using var chuan = new SqlConnection(cs);   // làm lại dữ liệu: 20 ví, mỗi ví nạp 2.000.000 từ tài khoản 1
await chuan.OpenAsync();
await Sql(chuan, null, """
    DELETE dbo.ButToan; DELETE dbo.LenhChuyen; DELETE dbo.Vi;
    INSERT INTO dbo.Vi (ViId, LoaiVi) VALUES (1, 9);
    INSERT INTO dbo.Vi (ViId, KhachHangId, LoaiVi)
    SELECT 10000 + n, n, 1 FROM (SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS n FROM sys.all_objects) AS s WHERE n <= 20;
    INSERT INTO dbo.LenhChuyen (MaLenh, ViGui, ViNhan, SoTien) SELECT NEWID(), 1, ViId, 2000000 FROM dbo.Vi WHERE LoaiVi = 1;
    INSERT INTO dbo.ButToan (LenhChuyenId, ViId, SoTien)
    SELECT LenhChuyenId, ViGui, -SoTien FROM dbo.LenhChuyen UNION ALL SELECT LenhChuyenId, ViNhan, SoTien FROM dbo.LenhChuyen;
    UPDATE dbo.Vi SET SoDu = (SELECT SUM(SoTien) FROM dbo.ButToan AS b WHERE b.ViId = Vi.ViId);
    """);
long xong = 0, tuChoi = 0, deadlock = 0, ghiDe = 0, batDau = Environment.TickCount64;
await Parallel.ForAsync(0, 16, new ParallelOptions { MaxDegreeOfParallelism = 16 }, async (phien, _) =>
{
    var rng = new Random(42 + phien);            // 16 phiên, mỗi phiên 400 lệnh cố định
    await using var cn = new SqlConnection(cs);
    await cn.OpenAsync();
    for (int i = 0; i < 400; i++)
    {
        int gui = 10001 + rng.Next(20), nhan = 10001 + rng.Next(19), n;
        if (nhan >= gui) nhan++;                 // ví nhận khác ví gửi
        decimal soTien = rng.Next(1, 21) * 10_000m;
        var p = new (string, object)[] { ("@MaLenh", Guid.NewGuid()), ("@ViGui", gui), ("@ViNhan", nhan), ("@SoTien", soTien) };
        while (true)                             // gặp 1205: chạy lại cả lệnh, cùng MaLenh
        {
            try { n = cach == "doc-ghi" ? await DocTinhGhi(cn, gui, nhan, soTien, p) : await Lenh(cn, lenh, p); break; }
            catch (SqlException e) when (e.Number == 50010) { n = -1; break; }
            catch (SqlException e) when (e.Number == 1205) { Interlocked.Increment(ref deadlock); }
        }
        if (n < 0) Interlocked.Increment(ref tuChoi);
        else { Interlocked.Increment(ref xong); Interlocked.Add(ref ghiDe, n); }
    }
});
double giay = (Environment.TickCount64 - batDau) / 1000.0;
Console.WriteLine($"{cach} xong={xong} tu_choi={tuChoi} deadlock={deadlock} ghi_de={ghiDe} lenh_moi_giay={xong / giay:0}");

// Cách 1: đọc số dư về app, tính, ghi giá trị mới. Trả số lần đè lên thay đổi của phiên khác, -1 khi thiếu tiền.
async Task<int> DocTinhGhi(SqlConnection cn, int gui, int nhan, decimal soTien, (string, object)[] p)
{
    decimal soDuGui = (decimal)(await Sql(cn, null, "SELECT SoDu FROM dbo.Vi WHERE ViId = @ViGui", p))!;
    decimal soDuNhan = (decimal)(await Sql(cn, null, "SELECT SoDu FROM dbo.Vi WHERE ViId = @ViNhan", p))!;
    if (soDuGui < soTien) return -1;
    await using var tx = (SqlTransaction)await cn.BeginTransactionAsync();
    const string Ghi = "UPDATE dbo.Vi SET SoDu = @Moi OUTPUT deleted.SoDu WHERE ViId = @ViId";
    decimal cuGui = (decimal)(await Sql(cn, tx, Ghi, ("@Moi", soDuGui - soTien), ("@ViId", gui)))!;
    decimal cuNhan = (decimal)(await Sql(cn, tx, Ghi, ("@Moi", soDuNhan + soTien), ("@ViId", nhan)))!;
    await Sql(cn, tx, SoCai, p);
    await tx.CommitAsync();
    return (cuGui != soDuGui ? 1 : 0) + (cuNhan != soDuNhan ? 1 : 0);
}

static async Task<int> Lenh(SqlConnection cn, string sql, (string, object)[] p) { await Sql(cn, null, sql, p); return 0; }
static async Task<object?> Sql(SqlConnection cn, SqlTransaction? tx, string sql, params (string, object)[] p)
{
    using var cmd = new SqlCommand(sql, cn, tx);
    foreach (var (ten, giaTri) in p) cmd.Parameters.AddWithValue(ten, giaTri);
    return await cmd.ExecuteScalarAsync();
}