Transaction, khóa và mức isolation

Kiến trúc lưu trữ cho thấy commit bền nhờ log record xuống L: trước khi client nhận kết quả. Chương này nói phần còn lại của một giao dịch: nó bắt đầu và kết thúc ở đâu, nó khóa những gì, giữ khóa bao lâu, và khi hai phiên cùng đụng một dòng thì kết quả ra sao. RCSI, ADR và delayed durability đã có ở Kỹ thuật thường dùng. Bài này dùng lại các cơ chế đó, không giới thiệu lại.

Mốc là SQL Server 2019 trên BanHang. READ_COMMITTED_SNAPSHOT và ADR đang tắt, đúng mặc định của bản tự cài. Chỗ nào RCSI hoặc ADR đổi kết quả, bài ghi ngay tại đó. dbo.DonHang có PK_DonHang, IX_DonHang_KhachHang và IX_DonHang_DangMo, không có NCCI_DonHang. Có columnstore thì danh sách khóa ở mục 3 dài hơn, và một phần của mục 11 không áp dụng. Trên Azure SQL Database, RCSI, ADR và optimized locking đã bật sẵn (Azure SQL).

1. Giao dịch và ACID #

Một đơn hàng ghi ba bảng: trừ TonKho trong dbo.SanPham, thêm đầu đơn vào dbo.DonHang, thêm các dòng vào dbo.ChiTietDonHang. Hai bảng đơn không có khóa ngoại với nhau vì khóa ngoại chặn SWITCH. Nếu ba lần ghi là ba giao dịch riêng, một lỗi ở lần thứ hai để lại kho đã trừ mà không có đơn. Giao dịch gom ba lần ghi thành một đơn vị: commit cả ba, hoặc không lần nào.

Engine chỉ giữ consistency ở mức ràng buộc đã khai. Phần còn lại của quy tắc nghiệp vụ đúng hay sai là do code chạy trong giao dịch. Số liệu dùng xuyên bài:

-- Sản phẩm 42 (SP-00042) và 108 nằm trong 5.000 sản phẩm đã có.
UPDATE dbo.SanPham SET DonGia = 250000.00, TonKho = 7 WHERE SanPhamId = 42;
UPDATE dbo.SanPham SET DonGia = 400000.00, TonKho = 20 WHERE SanPhamId = 108;

Ba chế độ giao dịch #

Autocommit không có nghĩa là từng dòng. Một câu UPDATE sửa 3 triệu dòng vẫn là một giao dịch: lỗi ở dòng cuối hoàn tác cả 3 triệu dòng.

Chế độ implicit là cái bẫy kinh điển. SELECT có bảng, INSERT, UPDATE, DELETE, MERGE, TRUNCATE TABLE và DDL đều mở giao dịch ngầm, và không có gì tự commit. Microsoft JDBC Driver chạy phiên ở chế độ này khi ứng dụng gọi setAutoCommit(false). ODBC driver cũng vậy khi tắt autocommit, và pyodbc mặc định autocommit=False. Một script Python đọc vài dòng rồi giữ kết nối mở là đang giữ một giao dịch từ câu SELECT đầu tiên. Nếu script có UPDATE mà quên commit(), khóa X nằm đó đến khi kết nối đóng, rồi lần sửa bị rollback.

SET IMPLICIT_TRANSACTIONS ON;
SELECT TongTien FROM dbo.DonHang
WHERE DonHangId = 10042 AND NgayTao = CAST('2026-10-02T11:58:00' AS datetime2(0));
SELECT @@TRANCOUNT AS trancount;   -- 1: câu SELECT có bảng đã mở giao dịch
ROLLBACK;
SET IMPLICIT_TRANSACTIONS OFF;

Tìm giao dịch ngầm đang mở trên instance. Giao dịch mở bằng BEGIN TRAN không đặt tên mang tên user_transaction, giao dịch ngầm mang tên implicit_transaction:

SELECT st.session_id, s.program_name, s.host_name, at.transaction_begin_time
FROM sys.dm_tran_active_transactions AS at
INNER JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = at.transaction_id
INNER JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
WHERE at.name = N'implicit_transaction'
ORDER BY at.transaction_begin_time;

@@TRANCOUNT không phải giao dịch lồng #

SQL Server không có giao dịch con commit độc lập. BEGIN TRAN lồng chỉ tăng @@TRANCOUNT. COMMIT khi @@TRANCOUNT > 1 chỉ giảm bộ đếm, không làm gì bền và không nhả khóa. ROLLBACK không kèm tên savepoint hoàn tác đến BEGIN TRAN ngoài cùng và đưa @@TRANCOUNT về 0.

BEGIN TRAN;                                     -- @@TRANCOUNT = 1
    UPDATE dbo.DonHang SET TongTien = 1800000.00
    WHERE DonHangId = 10042 AND NgayTao = CAST('2026-10-02T11:58:00' AS datetime2(0));
    BEGIN TRAN;                                 -- 2
        UPDATE dbo.SanPham SET TonKho = TonKho - 1 WHERE SanPhamId = 42;
    COMMIT;                                     -- 1. Chưa có gì bền, khóa X vẫn giữ
    SELECT @@TRANCOUNT AS trancount;            -- 1
ROLLBACK;                                       -- 0. Cả hai UPDATE bị hoàn tác:
                                                -- đơn 10042 vẫn 1.750.000, tồn 42 vẫn 7

Thủ tục con gọi ROLLBACK thì hoàn tác cả phần việc của thủ tục gọi nó. Khi thủ tục kết thúc với @@TRANCOUNT khác lúc vào, SQL Server báo thêm Msg 266 về số BEGIN và COMMIT lệch nhau. COMMIT khi @@TRANCOUNT = 0 báo lỗi 3902.

SAVE TRANSACTION <tên> đặt một mốc để hoàn tác một phần bằng ROLLBACK TRANSACTION <tên>. Lệnh đó không giảm @@TRANCOUNT, và nhả khóa lấy sau mốc trừ khóa đã leo thang hoặc đã chuyển chế độ. Savepoint không dùng được trong giao dịch phân tán, và không cứu được giao dịch đã bị XACT_ABORT ON đánh dấu không commit được: lúc đó chỉ còn rollback toàn bộ.

2. Xử lý lỗi và thủ tục dbo.usp_DonHang_Tao #

Lỗi chỉ hủy một câu #

Mặc định XACT_ABORT là OFF. Nhiều lỗi lúc chạy, như vi phạm ràng buộc, chỉ hủy câu gây lỗi. Giao dịch vẫn mở và các câu sau vẫn chạy.

SET XACT_ABORT OFF;   -- mặc định của phiên
BEGIN TRAN;
UPDATE dbo.SanPham SET TonKho = TonKho - 9 WHERE SanPhamId = 42;   -- tồn 7: vi phạm CHECK
INSERT INTO dbo.DonHang (DonHangId, NgayTao, KhachHangId, TrangThai, TongTien)
VALUES (10050, CAST('2026-10-02T12:25:00' AS datetime2(0)), 42, 1, 2250000.00);
COMMIT;

Msg 547, Level 16, State 0
The UPDATE statement conflicted with the CHECK constraint "CK_SanPham_TonKho". The conflict occurred in database "BanHang", table "dbo.SanPham", column 'TonKho'.
The statement has been terminated.

(1 row affected)

Đơn 10050 đã được lưu, tồn kho vẫn 7. Dọn lại trước khi đi tiếp:

DELETE FROM dbo.DonHang
WHERE DonHangId = 10050 AND NgayTao = CAST('2026-10-02T12:25:00' AS datetime2(0));

Chạy lại đúng khối đó sau SET XACT_ABORT ON: lỗi 547 kết thúc batch và rollback giao dịch. INSERT không chạy, không có gì để dọn.

Timeout để lại giao dịch mở #

Trường hợp thứ hai không có lỗi SQL nào. Lúc 12:40:00, ứng dụng mở giao dịch, trừ kho sản phẩm 42, và câu thứ hai chạy chậm. CommandTimeout mặc định của SqlClient là 30 giây. Lúc 12:40:30, client gửi tín hiệu attention: SQL Server dừng batch nhưng không rollback. Ứng dụng bắt exception rồi trả kết nối về pool. Phiên ở lại trạng thái sleeping, open_transaction_count = 1, giữ X trên dòng sản phẩm 42, và mọi đơn có sản phẩm 42 chờ dòng đó.

CATCH trong T-SQL không chạy trong trường hợp này: TRY...CATCH không bắt attention, cũng không bắt KILL. Kết nối trong pool chỉ được reset khi có người lấy ra dùng lại, nên giao dịch có thể mở thêm một lúc nữa. Với XACT_ABORT ON, SQL Server rollback giao dịch ngay khi nhận attention.

Mẫu chuẩn #

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    -- các câu ghi
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Phía ứng dụng vẫn nên chạy IF @@TRANCOUNT > 0 ROLLBACK trong nhánh bắt lỗi trước khi trả kết nối về pool. Một thủ tục được gọi trong batch có thể đã mở giao dịch mà ứng dụng không biết.

Thủ tục ghi đơn #

Dòng đơn đi vào thủ tục qua một table-valued parameter:

CREATE TYPE dbo.DongDonHang AS TABLE (
    DongSo smallint NOT NULL PRIMARY KEY,
    SanPhamId int NOT NULL,
    SoLuong int NOT NULL CHECK (SoLuong > 0)
);
GO

CREATE OR ALTER PROCEDURE dbo.usp_DonHang_Tao
    @DonHangId bigint,
    @NgayTao datetime2(0),
    @KhachHangId int,
    @Dong dbo.DongDonHang READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        DECLARE @SoSanPham int = (SELECT COUNT(DISTINCT SanPhamId) FROM @Dong);
        IF @SoSanPham = 0
            THROW 50001, N'Đơn hàng phải có ít nhất một dòng.', 1;

        BEGIN TRANSACTION;

        -- 1. Trừ kho trước. Điều kiện TonKho >= số cần chặn bán quá tồn (mục 6).
        UPDATE sp
        SET TonKho = sp.TonKho - d.SoLuong
        FROM dbo.SanPham AS sp
        INNER JOIN (SELECT SanPhamId, SUM(SoLuong) AS SoLuong
                    FROM @Dong GROUP BY SanPhamId) AS d
            ON d.SanPhamId = sp.SanPhamId
        WHERE sp.TonKho >= d.SoLuong;

        IF @@ROWCOUNT <> @SoSanPham
            THROW 50002, N'Không đủ tồn kho hoặc sản phẩm không tồn tại.', 1;

        -- 2. Đầu đơn. Giá đọc từ các dòng SanPham đã bị khóa X ở bước 1.
        INSERT INTO dbo.DonHang (DonHangId, NgayTao, KhachHangId, TrangThai, TongTien)
        SELECT @DonHangId, @NgayTao, @KhachHangId, 1, SUM(d.SoLuong * sp.DonGia)
        FROM @Dong AS d
        INNER JOIN dbo.SanPham AS sp ON sp.SanPhamId = d.SanPhamId;

        -- 3. Dòng chi tiết, cùng NgayTao nên cùng partition với đầu đơn.
        INSERT INTO dbo.ChiTietDonHang (DonHangId, NgayTao, DongSo, SanPhamId, SoLuong, DonGia)
        SELECT @DonHangId, @NgayTao, d.DongSo, d.SanPhamId, d.SoLuong, sp.DonGia
        FROM @Dong AS d
        INNER JOIN dbo.SanPham AS sp ON sp.SanPhamId = d.SanPhamId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

Thứ tự ghi cố định: SanPham, rồi DonHang, rồi ChiTietDonHang. Mục 10 dựa vào thứ tự này để tránh deadlock. Một câu UPDATE nhiều dòng không bảo đảm khóa sản phẩm 42 trước sản phẩm 108, nên hai đơn cùng chứa hai sản phẩm đó vẫn có thể deadlock với nhau. Trường hợp hiếm này do vòng thử lại ở mục 10 xử lý.

Đơn 10051 lúc 12:30, khách 42, hai dòng:

DECLARE @Dong dbo.DongDonHang;
INSERT INTO @Dong (DongSo, SanPhamId, SoLuong) VALUES (1, 42, 2), (2, 108, 1);

EXEC dbo.usp_DonHang_Tao @DonHangId = 10051, @NgayTao = '2026-10-02T12:30:00',
     @KhachHangId = 42, @Dong = @Dong;

SELECT TongTien FROM dbo.DonHang
WHERE DonHangId = 10051 AND NgayTao = CAST('2026-10-02T12:30:00' AS datetime2(0));  -- 900000.00
SELECT SanPhamId, TonKho FROM dbo.SanPham WHERE SanPhamId IN (42, 108);           -- 5 và 19

TongTien = 2 × 250.000 + 1 × 400.000 = 900.000. Tồn sản phẩm 42 còn 5, con số mở đầu mục 6. Gọi tiếp với một dòng 6 sản phẩm 42 (đơn 10052) thì không có gì được ghi:

Msg 50002, Level 16, State 1
Không đủ tồn kho hoặc sản phẩm không tồn tại.

Kết quả rút gọn, bỏ tên thủ tục và số dòng. Gọi thủ tục này bên trong một giao dịch đang mở của bên gọi thì lỗi cũng hoàn tác phần việc của bên gọi, kèm Msg 266. Với ghi đơn, đó là hành vi đúng: đơn không ghi được thì đơn vị việc bên ngoài cũng không nên commit dở.

3. Khóa: chế độ, tài nguyên, tương thích #

Chế độ khóa #

Khóa intent cho phép một yêu cầu khóa cả bảng chỉ kiểm một tài nguyên, thay vì duyệt mọi khóa dòng. IX tương thích với IX: hai phiên sửa hai đơn khác nhau cùng giữ IX trên dbo.DonHang. Mức isolation chỉ đổi cách đọc. Khóa X của lần sửa luôn giữ đến hết giao dịch.

Ma trận tương thích #

Hàng là chế độ đang xin, cột là chế độ phiên khác đang giữ. Bảng đối xứng.

Sch-S tương thích với mọi khóa dữ liệu, kể cả X, và chỉ xung đột với Sch-M. Sch-M xung đột với mọi khóa. Vì vậy một ALTER TABLE dbo.DonHang phải chờ cả câu SELECT ... WITH (NOLOCK) đang chạy, và mọi câu đến sau phải xếp hàng sau ALTER TABLE.

Vì sao có khóa U #

Đọc bằng S rồi sửa, như code tách SELECT và UPDATE dưới REPEATABLE READ, tự tạo deadlock chuyển đổi khóa:

U tương thích với S nhưng không tương thích với U. Câu UPDATE lấy U khi dò dòng, nên phiên thứ hai xếp hàng ngay ở bước đọc thay vì cùng giữ S rồi kẹt nhau ở bước chuyển sang X. Code ứng dụng tách đọc và ghi thì dùng UPDLOCK ở câu đọc để có cùng hiệu quả (mục 6).

Khóa của một UPDATE đơn 10042 #

Giữ một giao dịch sửa đơn 10042 mở, rồi xem khóa của chính phiên đó:

BEGIN TRAN;

UPDATE dbo.DonHang SET TongTien = 1800000.00
WHERE DonHangId = 10042 AND NgayTao = CAST('2026-10-02T11:58:00' AS datetime2(0));

SELECT l.resource_type, l.request_mode, l.request_status, l.resource_description,
       COALESCE(OBJECT_NAME(p.object_id),
                CASE WHEN l.resource_type = 'OBJECT'
                     THEN OBJECT_NAME(l.resource_associated_entity_id) END) AS object_name,
       i.name AS index_name, p.partition_number
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p
    ON l.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')
   AND p.hobt_id = l.resource_associated_entity_id
LEFT JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE l.request_session_id = @@SPID
  AND l.resource_database_id = DB_ID();

ROLLBACK;

Kết quả minh họa, xếp theo tầng. Số page và chuỗi băm của khóa đổi theo máy:

Đọc từ trên xuống: S trên database, IX trên bảng, IX trên page chứa dòng, X trên đúng một khóa của PK_DonHang, partition 3 (năm 2026). resource_description của PAGE là file:page. Của KEY là chuỗi băm của giá trị khóa, không đọc ngược ra được giá trị. IX_DonHang_KhachHang không có khóa vì TongTien không nằm trong chỉ mục đó. Đơn 10042 đang ở TrangThai = 2, nên IX_DonHang_DangMo (chỉ chứa TrangThai = 1) cũng không bị đụng. Đơn ở trạng thái 1 sẽ có thêm cặp PAGE IX và KEY X trên chỉ mục lọc đó, vì nó INCLUDE (TongTien).

4. Leo thang khóa #

Mỗi khóa tốn bộ nhớ. Khi một câu lệnh giữ quá nhiều khóa nhỏ, engine đổi chúng thành một khóa lớn. Điều kiện kích hoạt:

• Một câu lệnh giữ ít nhất 5.000 khóa trên một tham chiếu tới một bảng hoặc chỉ mục. 3.000 khóa trên chỉ mục này cộng 3.000 khóa trên chỉ mục khác của cùng bảng không kích hoạt. Ngưỡng tính theo câu lệnh, không cộng dồn qua các câu của cùng giao dịch.
• Bộ nhớ khóa của cả instance vượt ngưỡng. Khi tùy chọn locks để mặc định 0, ngưỡng là 24% bộ nhớ engine đang dùng.

Khóa dòng không leo lên khóa page. Cả khóa dòng lẫn khóa page đều leo thẳng lên khóa bảng. Bảng phân vùng đặt LOCK_ESCALATION = AUTO thì lên khóa HOBT của partition. Nếu phiên khác đang giữ khóa không tương thích ở mức đích, câu lệnh không chờ: nó tiếp tục khóa từng dòng và thử lại sau mỗi 1.250 khóa mới. Gợi ý ROWLOCK chỉ đổi cách khóa ban đầu, không ngăn leo thang.

Chốt sổ năm 2024 lúc 19:00 #

Lúc 19:00 ngày 2026-10-02, cửa hàng vẫn nhận đơn. Một job chốt sổ năm 2024: đơn nào chưa ở trạng thái cuối thì chuyển sang Hoàn tất.

UPDATE dbo.DonHang
SET TrangThai = 4
WHERE NgayTao >= CAST('20240101' AS datetime2(0))
  AND NgayTao < CAST('20250101' AS datetime2(0))
  AND TrangThai IN (1, 2, 3);

Câu này quét partition 1, khoảng 3,0 triệu dòng. Nếu tỷ lệ trạng thái giống phân phối chung (5% ở trạng thái 1 đến 3), nó sửa khoảng 150.000 dòng. FG_ARCHIVE phải đang READ_WRITE. Đến khoảng khóa thứ 5.000 trên PK_DonHang, engine thử leo thang:

1. dbo.DonHang để mặc định LOCK_ESCALATION = TABLE, nên đích là khóa X trên cả bảng.
2. Các phiên đang chèn đơn 2026 giữ IX trên bảng. X không tương thích với IX, lần thử thất bại, job tiếp tục khóa dòng.
3. Giao dịch chèn đơn chỉ dài vài mili giây. Một khe trống giữa hai đơn là đủ để một lần thử sau đó thành công.
4. Từ đó, mọi INSERT đơn mới chờ LCK_M_IX đến khi job commit, tức sau khi job quét hết 3 triệu dòng.

Đơn 2026 nằm ở partition 3, không trùng dòng nào job đang sửa. Chúng bị chặn chỉ vì khóa đã lên mức bảng. Trong lúc job chạy, sys.dm_tran_locks của phiên job chuyển từ hàng nghìn dòng KEY X thành một dòng OBJECT X. Số lần leo thang tích lũy theo chỉ mục:

SELECT i.name AS index_name, s.partition_number,
       s.index_lock_promotion_attempt_count, s.index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(DB_ID(N'BanHang'), OBJECT_ID(N'dbo.DonHang'), NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id
ORDER BY s.index_lock_promotion_count DESC;

Bộ đếm này mất khi instance khởi động lại hoặc metadata của bảng bị đẩy khỏi cache. Muốn thấy từng lần leo thang kèm nguyên nhân, dùng event lock_escalation của Extended Events.

Cách sửa 1: chia lô #

Mỗi lô 4.000 dòng, đi theo khóa clustered để không quét lại phần đã xử lý. Mỗi lô là một giao dịch autocommit: khóa nhả sau từng lô, và log backup giữa các lô cắt được log.

SET NOCOUNT ON;

DECLARE @NgayCuoi datetime2(0) = CAST('20240101' AS datetime2(0));
DECLARE @IdCuoi bigint = -1;
DECLARE @SoDong int = 1;
DECLARE @Lo TABLE (NgayTao datetime2(0) NOT NULL, DonHangId bigint NOT NULL);

WHILE @SoDong > 0
BEGIN
    DELETE FROM @Lo;

    WITH LoTiepTheo AS (
        SELECT TOP (4000) NgayTao, DonHangId, TrangThai
        FROM dbo.DonHang
        WHERE NgayTao >= @NgayCuoi
          AND NgayTao < CAST('20250101' AS datetime2(0))
          AND (NgayTao > @NgayCuoi OR DonHangId > @IdCuoi)
          AND TrangThai IN (1, 2, 3)
        ORDER BY NgayTao, DonHangId
    )
    UPDATE LoTiepTheo
    SET TrangThai = 4
    OUTPUT inserted.NgayTao, inserted.DonHangId INTO @Lo (NgayTao, DonHangId);

    SET @SoDong = @@ROWCOUNT;

    SELECT TOP (1) @NgayCuoi = NgayTao, @IdCuoi = DonHangId
    FROM @Lo
    ORDER BY NgayTao DESC, DonHangId DESC;
END;

Khoảng 150.000 dòng thành khoảng 38 lô. 4.000 khóa dòng chừa chỗ dưới ngưỡng 5.000 cho các khóa page đi kèm. Chạy xong, index_lock_promotion_count của PK_DonHang không tăng là bằng chứng lô đủ nhỏ. Không bọc cả vòng lặp trong một BEGIN TRAN: khóa của mọi lô sẽ cộng dồn đến cuối, và log không cắt được cho đến khi commit.

Cách sửa 2: leo thang ở mức partition #

ALTER TABLE dbo.DonHang SET (LOCK_ESCALATION = AUTO);
SELECT name, lock_escalation_desc FROM sys.tables WHERE name = N'DonHang';

Lệnh ALTER TABLE cần khóa Sch-M trong chốc lát. Sau đó, job leo thang thành X trên HOBT của partition 1, còn ở mức bảng nó chỉ giữ IX. Phiên chèn đơn 2026 giữ IX trên bảng, tương thích với IX của job, và mọi khóa dòng của nó nằm ở partition 3. Câu đọc chạm cả partition 1, như báo cáo nhiều năm không lọc theo NgayTao, vẫn bị chặn.

Tài liệu Microsoft ghi rủi ro của AUTO: hai giao dịch đã leo thang trên hai partition khác nhau, rồi mỗi bên cần mở rộng khóa X sang partition của bên kia, sẽ deadlock. Hai job bảo trì chạy song song trên năm 2024 và 2025 là đúng hình dạng đó. Trên bảng không phân vùng, AUTO hành xử như TABLE. Chia lô vẫn là cách sửa chính. AUTO là lớp bảo vệ thêm cho bảng phân vùng.

5. Mức isolation và các hiện tượng đồng thời #

Mức isolation đặt cho từng phiên bằng SET TRANSACTION ISOLATION LEVEL, mặc định READ COMMITTED. RCSI không phải mức riêng: nó là tùy chọn database đổi cách READ COMMITTED đọc. SNAPSHOT cần bật ALLOW_SNAPSHOT_ISOLATION trước (mục 8). Mức đặt trong thủ tục trở về mức cũ khi thủ tục kết thúc. Với connection pool, kết nối trả về pool giữ mức đặt lần cuối và mang sang lần dùng sau, nên đặt mức ngay trong thủ tục hoặc ngay sau khi lấy kết nối.

• Dirty read: đọc giá trị chưa commit.
• Non-repeatable read: đọc lại cùng dòng trong một giao dịch, thấy giá trị khác.
• Phantom: chạy lại cùng điều kiện, thấy tập dòng khác vì có dòng mới chèn.
• Lost update: hai giao dịch đọc cùng giá trị, mỗi bên tính rồi ghi đè, một lần ghi biến mất.
• Write skew: hai giao dịch đọc cùng tập dữ liệu, ghi vào hai chỗ khác nhau. Từng bên đúng quy tắc, gộp lại thì sai.

Cột lost update tính cho kiểu đọc ở một câu rồi ghi ở câu sau; timeline nằm ở mục 6. Cột write skew theo ví dụ ở dưới, nơi hai phiên chèn dòng mới. Nếu hai phiên chỉ sửa các dòng cả hai đã đọc, REPEATABLE READ giữ S nên kết cục là chờ hoặc deadlock, không phải write skew.

Dirty read #

Đúng giao dịch lúc 12:04 của bộ bài, nhìn từ một báo cáo dùng NOLOCK:

Lần này A commit, nên con số B đọc trùng kết quả cuối. Nếu bước sau của A lỗi và A rollback, báo cáo đã in 1.750.000, giá trị chưa từng tồn tại ở trạng thái commit. NOLOCK còn có thể đọc một dòng hai lần hoặc bỏ sót dòng khi page đang tách, và có thể gặp lỗi 601.

Bỏ NOLOCK, cùng câu đọc ở READ COMMITTED có khóa: B chờ LCK_M_S từ 12:04:01 đến 12:04:02, rồi đọc 1.750.000 đã commit. Ở RCSI: B không chờ, đọc 1.500.000, bản commit tại lúc câu lệnh bắt đầu. Gợi ý NOLOCK thắng cả RCSI: có nó, câu đọc vẫn là dirty read.

Non-repeatable read #

Khóa S của câu đọc đầu nhả ngay sau khi đọc, nên B không phải chờ. RCSI cho cùng kết quả: câu thứ hai bắt đầu sau 12:04:00 nên thấy bản mới. Ở REPEATABLE READ, A giữ S đến 12:04:12, UPDATE của B chờ đến lúc đó, và A đọc 1.500.000 cả hai lần. Ở SNAPSHOT, B không chờ, A vẫn đọc 1.500.000 hai lần.

Phantom #

Khách 42 có khoảng 300 đơn mỗi ngày. Lúc 12:10 có 3 đơn ngày 2026-10-02 đang ở TrangThai = 1.

SELECT COUNT(*) AS so_don_mo
FROM dbo.DonHang
WHERE KhachHangId = 42 AND TrangThai = 1
  AND NgayTao >= CAST('20261002' AS datetime2(0))
  AND NgayTao < CAST('20261003' AS datetime2(0));

REPEATABLE READ khóa ba dòng đã đọc, không khóa khoảng trống giữa chúng. Ở SERIALIZABLE, câu đếm seek trên một chỉ mục có KhachHangId đứng đầu (IX_DonHang_DangMo hoặc IX_DonHang_KhachHang) và đặt khóa phạm vi trên đoạn khóa (42, 2026-10-02) đến (42, 2026-10-03) của chỉ mục đó. Đơn 10047 phải chèn một khóa vào đúng đoạn này ở cả hai chỉ mục, nên INSERT chờ đến 12:10:12, và A đếm 3 cả hai lần. Khóa phạm vi bám chỉ mục mà câu đọc dùng. Không có chỉ mục phù hợp, câu đọc quét clustered index và khóa phạm vi rộng tương ứng. Cách chỉ mục quyết định seek hay scan nằm ở Chỉ mục và thống kê.

Write skew #

Quy tắc công nợ: đại lý 42 không được có quá 5 đơn ở TrangThai = 1. Lúc 12:20 khách đang có 4 đơn như vậy: 3 đơn mở từ sáng và đơn 10047. Ứng dụng đếm trước rồi mới chèn.

Hai phiên ghi hai dòng khác nhau. Không khóa nào xung đột, nên RCSI và REPEATABLE READ cho cùng kết cục. SNAPSHOT cũng để lọt: không có dòng nào bị cả hai cùng sửa, nên không có xung đột để báo 3960.

Ở SERIALIZABLE, cả hai câu đếm giữ RangeS-S trên đoạn khóa của khách 42. Mỗi INSERT cần RangeI-N trong đoạn đó và bị khóa của bên kia chặn: deadlock, một phiên nhận 1205. Phiên thử lại đếm được 5 và từ chối. Cách gọn hơn là đếm với WITH (UPDLOCK, HOLDLOCK): khóa phạm vi chế độ U không tương thích với nhau, nên B chờ ngay ở câu đếm và đếm được 5 sau khi A commit, không qua deadlock.

6. Mất cập nhật trên tồn kho #

Lúc 15:00, sản phẩm 42 còn 5. Hai khách cùng mua 3. Kết quả đúng: một đơn thành công, tồn còn 2, đơn kia bị từ chối. Code dưới đây, chạy ở mỗi phiên, cho kết quả sai:

DECLARE @TonKho int;
BEGIN TRAN;
SELECT @TonKho = TonKho FROM dbo.SanPham WHERE SanPhamId = 42;
IF @TonKho >= 3
    UPDATE dbo.SanPham SET TonKho = @TonKho - 3 WHERE SanPhamId = 42;
COMMIT;

Sáu sản phẩm đã bán, tồn ghi 2. Lần trừ của A biến mất. CK_SanPham_TonKho không cứu được vì cả hai lần ghi đều là 2, hợp lệ.

Dưới RCSI, lỗ hổng rộng hơn. Câu SELECT không bao giờ chờ: dù B đọc sau khi A đã ghi 2 mà chưa commit, B vẫn đọc 5, bản commit gần nhất. Bật RCSI giải quyết đọc chặn ghi, không giải quyết đọc rồi ghi.

Cách a: một câu UPDATE có điều kiện #

DECLARE @SanPhamId int = 42, @SoLuong int = 3;

UPDATE dbo.SanPham
SET TonKho = TonKho - @SoLuong
WHERE SanPhamId = @SanPhamId AND TonKho >= @SoLuong;

IF @@ROWCOUNT = 0
    THROW 50002, N'Không đủ tồn kho.', 1;

A trừ xong giữ X. UPDATE của B lấy U khi dò dòng nên chờ. A commit, B đọc bản mới nhất là 2, điều kiện 2 >= 3 sai, không dòng nào được sửa, B báo hết hàng. Dưới RCSI kết quả giống hệt: câu UPDATE ở READ COMMITTED dò dòng bằng khóa U trên dữ liệu hiện tại, không đọc phiên bản cũ. dbo.usp_DonHang_Tao dùng đúng cách này. Đây là cách nên chọn mặc định cho mọi phép cộng trừ số lượng.

Cách b: đọc với UPDLOCK #

Khi logic giữa đọc và ghi phức tạp hơn một phép trừ (giữ hàng theo hạng khách, kiểm hạn mức), khóa dòng ngay ở câu đọc:

DECLARE @SanPhamId int = 42, @SoLuong int = 3, @TonKho int;

SET XACT_ABORT ON;
BEGIN TRAN;

SELECT @TonKho = TonKho
FROM dbo.SanPham WITH (UPDLOCK, ROWLOCK)
WHERE SanPhamId = @SanPhamId;

IF ISNULL(@TonKho, 0) < @SoLuong            -- NULL: sản phẩm không tồn tại
    THROW 50002, N'Không đủ tồn kho.', 1;

UPDATE dbo.SanPham SET TonKho = @TonKho - @SoLuong WHERE SanPhamId = @SanPhamId;
COMMIT;

UPDLOCK giữ U đến hết giao dịch. Câu đọc của B chờ ở dòng đó cho đến khi A commit, rồi đọc 2. Gợi ý khóa làm câu đọc dùng khóa trên dữ liệu hiện tại kể cả khi RCSI bật. ROWLOCK giữ khóa ở mức dòng để không chặn sản phẩm khác trên cùng page. Giao dịch phải ngắn: mọi đơn có sản phẩm 42 xếp hàng sau nó.

Cách c: optimistic concurrency với rowversion #

Màn hình kiểm kho: nhân viên mở sản phẩm 42, đếm hàng thật trong vài phút, rồi nhập số tuyệt đối. Không thể giữ khóa trong lúc người dùng suy nghĩ. Thêm một cột phiên bản; cột mới không đổi các cột đã có:

ALTER TABLE dbo.SanPham ADD PhienBan rowversion;

rowversion là 8 byte, đổi mỗi lần dòng bị sửa, bởi bất kỳ ai. Giá trị lấy từ một bộ đếm chung của database, không mang nghĩa ngày giờ, nên chỉ dùng để so bằng.

-- Bước 1, lúc mở màn hình. Ứng dụng giữ PhienBan cùng dữ liệu.
SELECT TonKho, PhienBan FROM dbo.SanPham WHERE SanPhamId = 42;

-- Bước 2, khi bấm Lưu. Giá trị dưới là minh họa; chạy thật thì thay bằng PhienBan ở bước 1.
DECLARE @PhienBanDaDoc binary(8) = 0x00000000000A3F51;

UPDATE dbo.SanPham SET TonKho = 12
WHERE SanPhamId = 42 AND PhienBan = @PhienBanDaDoc;

IF @@ROWCOUNT = 0
    THROW 50003, N'Sản phẩm đã bị sửa từ lúc mở màn hình. Tải lại rồi nhập lại.', 1;

Có một đơn bán sản phẩm 42 trong lúc nhân viên đếm thì PhienBan đã đổi, UPDATE sửa 0 dòng, nhân viên thấy thông báo thay vì ghi đè lên lần bán đó.

7. Upsert và khóa phạm vi #

Bảng tổng hợp doanh thu theo ngày, cộng dồn sau mỗi đơn:

CREATE TABLE dbo.DoanhThuNgay (
    Ngay date NOT NULL,
    SanPhamId int NOT NULL,
    SoLuong int NOT NULL,
    DoanhThu decimal(18, 2) NOT NULL,
    CONSTRAINT PK_DoanhThuNgay PRIMARY KEY CLUSTERED (Ngay, SanPhamId)
);

Cách viết hay gặp là IF NOT EXISTS (SELECT ...) INSERT ... ELSE UPDATE .... Hai đơn đầu tiên của ngày 2026-10-03, mỗi đơn 2 sản phẩm 42, tức 500.000:

Ở READ COMMITTED, câu kiểm tra không khóa được một dòng chưa tồn tại. Bọc cả khối trong BEGIN TRAN không đổi điều đó. Một câu MERGE đơn lẻ có cùng cuộc đua: tài liệu MERGE khuyên thêm HOLDLOCK khi một khóa có thể vừa được chèn vừa được sửa đồng thời.

Cách đúng: khóa khoảng trống nơi dòng sẽ nằm, bằng ngữ nghĩa SERIALIZABLE (HOLDLOCK) cộng chế độ U (UPDLOCK):

CREATE OR ALTER PROCEDURE dbo.usp_DoanhThuNgay_Cong
    @Ngay date, @SanPhamId int, @SoLuong int, @DoanhThu decimal(18, 2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    UPDATE dbo.DoanhThuNgay WITH (UPDLOCK, HOLDLOCK)
    SET SoLuong = SoLuong + @SoLuong, DoanhThu = DoanhThu + @DoanhThu
    WHERE Ngay = @Ngay AND SanPhamId = @SanPhamId;

    IF @@ROWCOUNT = 0
        INSERT INTO dbo.DoanhThuNgay (Ngay, SanPhamId, SoLuong, DoanhThu)
        VALUES (@Ngay, @SanPhamId, @SoLuong, @DoanhThu);

    COMMIT TRANSACTION;
END;
GO

Viết bằng MERGE thì gắn gợi ý vào bảng đích, MERGE dbo.DoanhThuNgay WITH (HOLDLOCK) AS t USING ..., và vẫn đặt trong mẫu XACT_ABORT khi nằm trong thủ tục.

Khóa phạm vi (key-range lock) chỉ xuất hiện dưới SERIALIZABLE hoặc HOLDLOCK. Nó khóa một khóa của chỉ mục cùng khoảng trống ngay trước khóa đó, để không ai chèn được giá trị mới vào khoảng đang được bảo vệ.

Khi chưa có dòng nào của ngày 2026-10-03, khoảng trống bị khóa kéo từ khóa cuối của ngày 2026-10-02 đến hết chỉ mục. Lúc đầu ngày, phiên cộng doanh thu cho sản phẩm 108 cũng rơi vào khoảng đó và phải chờ phiên của sản phẩm 42. Khoảng chờ chỉ dài bằng giao dịch upsert, vài mili giây, nên giao dịch này phải giữ ngắn.

8. SNAPSHOT #

ALTER DATABASE BanHang SET ALLOW_SNAPSHOT_ISOLATION ON;

SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'BanHang';

Lệnh này không đòi ngắt các kết nối khác như RCSI, nhưng chỉ trả về khi mọi giao dịch đang mở trong BanHang đã kết thúc. Từ lúc bật, mọi lệnh sửa dữ liệu sinh phiên bản dòng, kể cả khi chưa phiên nào dùng SNAPSHOT. Ứng dụng muốn dùng thì phải chủ động SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

Xung đột cập nhật #

Lúc 15:30, sau đơn thành công lúc 15:00, sản phẩm 42 còn 2.

Msg 3960, Level 16, State 2
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.SanPham' directly or indirectly in database 'BanHang' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Giao dịch SNAPSHOT sửa một dòng đã bị giao dịch khác sửa và commit sau khi ảnh chụp của nó bắt đầu thì bị hủy. Cách xử lý giống deadlock: thử lại cả giao dịch. Lần thử lại có ảnh chụp mới, thấy 1, trừ còn 0. Giao dịch bắt đầu từ câu đầu tiên đọc dữ liệu, không phải từ BEGIN TRAN.

Khác RCSI ở đâu #

Báo cáo cuối ngày chạy hai câu trong một giao dịch: tổng TongTien ngày 2026-10-02, rồi danh sách đơn. Dưới RCSI, một đơn commit giữa hai câu có trong danh sách mà không có trong tổng. Dưới SNAPSHOT, hai câu thấy cùng một ảnh chụp.

Phiên bản nằm ở version store trong tempdb khi ADR tắt. Khi ADR bật, chúng nằm trong persisted version store của chính BanHang, mặc định trên filegroup PRIMARY nếu lúc bật ADR không chỉ định PERSISTENT_VERSION_STORE_FILEGROUP (Kỹ thuật thường dùng, mục 6 và 7). Mỗi dòng được sửa hoặc chèn sau khi bật RCSI hay SNAPSHOT mang thêm tối đa 14 byte thông tin phiên bản. Dòng DonHang 35 byte của chương lưu trữ thành tối đa 49 byte, cộng 2 byte slot là 51 byte. Page lá toàn những dòng như vậy chứa 8.096 / 51 = 158 dòng thay vì 218. Page đầy có thể tách khi các dòng cũ lần lượt bị sửa.

Giao dịch SNAPSHOT mở lâu giữ version store không dọn được. sys.dm_tran_active_snapshot_database_transactions sắp theo elapsed_time_seconds giảm dần chỉ ra giao dịch đó và session_id của nó.

9. Blocking: tìm phiên đầu chuỗi #

Sáng 2026-10-03, lúc 09:00, một nhân viên kế toán mở SSMS và chạy:

BEGIN TRAN;
UPDATE dbo.DonHang SET TrangThai = 3
WHERE DonHangId = 10042 AND NgayTao = CAST('2026-10-02T11:58:00' AS datetime2(0));

Không có COMMIT. Người đó đi ăn trưa. Từ khoảng 12:00, log ứng dụng đầy lỗi timeout khi xem và sửa đơn 10042.

Bước 1, ai đang 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;

Kết quả minh họa lúc 12:15:

Phiên 87 thêm sản phẩm 42 vào đơn 10042: đã trừ kho, đang chờ dòng đơn. Phiên 112 là dbo.usp_DonHang_Tao của một đơn mới có sản phẩm 42, chờ phiên 87. Phiên 95 là màn hình chăm sóc khách hàng; nó chờ vì RCSI đang tắt. wait_ms đều dưới 30.000 vì ứng dụng timeout sau 30 giây rồi gọi lại. Chuỗi là 112 chờ 87, 87 chờ 58.

Bước 2, phiên đầu chuỗi: chặn người khác nhưng không bị ai chặn.

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);

sleeping cộng open_transaction_count = 1 là chữ ký của giao dịch bị bỏ quên: phiên không chạy gì từ 09:00:04 nhưng vẫn giữ khóa. Phiên 58 không có dòng nào trong sys.dm_exec_requests vì nó không chạy câu nào. sys.dm_exec_input_buffer vẫn trả lệnh cuối cùng nó gửi.

Bước 3, giao dịch mở bao lâu và đã ghi bao nhiêu:

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;

Phiên 58: user_transaction, bắt đầu 09:00:03, mở 195 phút, vài log record. sys.dm_tran_locks lọc theo request_session_id = 58 cho thấy OBJECT IX, PAGE IX, KEY X, đúng hình dạng ở mục 3. Đây cũng là giao dịch mà chương lưu trữ nói tới: khi ADR tắt, log backup sau 09:00 vẫn chạy nhưng không cắt được phần log từ lúc giao dịch này bắt đầu trở đi. DBCC OPENTRAN(N'BanHang') cũng chỉ ra phiên 58 và giờ bắt đầu.

Bước 4, xử lý. Chỉ người giữ phiên mới COMMIT được giao dịch đó. Liên lạc được thì họ quyết định commit hay rollback ngay trong cửa sổ SSMS của mình. Không liên lạc được và blocking đang làm hỏng đơn hàng thì:

KILL 58;
KILL 58 WITH STATUSONLY;

KILL ngắt kết nối và rollback giao dịch: lần đổi TrangThai của đơn 10042 mất. Giao dịch một dòng rollback gần như tức thì, và WITH STATUSONLY báo lỗi 6120 vì không còn rollback nào đang chạy. Giao dịch đã sửa 3 triệu dòng thì khác.

Phòng lần sau: tài khoản chỉ đọc cho nhân viên tra cứu, sửa dữ liệu production qua thủ tục có XACT_ABORT ON, và một job cảnh báo khi truy vấn ở bước 3 thấy giao dịch mở quá 10 phút.

10. Deadlock #

Lúc 16:40, hai thủ tục cũ cùng đụng đơn 10042 và sản phẩm 42 theo thứ tự ngược nhau. Phiên A ghi nhận khách trả 1 sản phẩm 42: giảm tiền đơn rồi cộng kho. Phiên B thêm 1 sản phẩm 42 vào đơn: trừ kho rồi tăng tiền đơn.

-- Phiên A
BEGIN TRAN;
UPDATE dbo.DonHang SET TongTien = TongTien - 250000.00
WHERE DonHangId = 10042 AND NgayTao = CAST('2026-10-02T11:58:00' AS datetime2(0));
UPDATE dbo.SanPham SET TonKho = TonKho + 1 WHERE SanPhamId = 42;
COMMIT;

-- Phiên B
BEGIN TRAN;
UPDATE dbo.SanPham SET TonKho = TonKho - 1 WHERE SanPhamId = 42;
UPDATE dbo.DonHang SET TongTien = TongTien + 250000.00
WHERE DonHangId = 10042 AND NgayTao = CAST('2026-10-02T11:58:00' AS datetime2(0));
COMMIT;

Msg 1205, Level 13, State 51
Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Lock monitor tìm chu trình mỗi 5 giây theo mặc định, và rút xuống tới 100 mili giây khi deadlock xảy ra dày. Nạn nhân là phiên có DEADLOCK_PRIORITY thấp hơn. Cùng mức thì phiên rẻ hơn để rollback. Cùng chi phí thì chọn ngẫu nhiên. Job bảo trì như ở mục 4 có thể đặt SET DEADLOCK_PRIORITY LOW để luôn nhường phiên bán hàng.

Đọc từ system_health #

Session system_health chạy sẵn trên SQL Server và Managed Instance, ghi event xml_deadlock_report vào hai target. ring_buffer nằm trong bộ nhớ và chỉ giữ các event gần nhất. event_file nằm trong thư mục Log của instance và còn sau khi khởi động lại. Từ SQL Server 2016, sys.fn_xe_file_target_read_file nhận tên tệp không kèm đường dẫn và tìm trong thư mục mặc định đó. Cột timestamp_utc có từ SQL Server 2017:

SELECT f.timestamp_utc,
       CAST(f.event_data AS xml).query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_xml
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS f
WHERE f.object_name = N'xml_deadlock_report'
ORDER BY f.timestamp_utc DESC;

Đọc từ ring_buffer thì lấy target_data của sys.dm_xe_session_targets với target_name = N'ring_buffer', ép sang xml, rồi duyệt RingBufferTarget/event[@name="xml_deadlock_report"]. Mốc thời gian của Extended Events là UTC: deadlock lúc 16:40 giờ Việt Nam hiện là 09:40. Bấm vào ô deadlock_xml trong SSMS, lưu thành tệp .xdl rồi mở lại để có đồ thị. Phần cần đọc, rút gọn và minh họa:

<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>

resource-list trả lời câu hỏi chính: ai giữ gì, ai chờ gì. A giữ X trên khóa đơn và chờ sản phẩm; B ngược lại. Mỗi process thật còn có inputbuf chứa câu lệnh cuối. isolationlevel lộ ra phiên nào đang chạy SERIALIZABLE ngoài ý muốn, một nguồn deadlock hay gặp vì TransactionScope của .NET mặc định Serializable. transactionname="implicit_transaction" chỉ ra một driver đang để implicit transactions bật.

Sửa #

1. Cùng thứ tự truy cập. Quy ước của BanHang: SanPham, rồi DonHang, rồi ChiTietDonHang, như dbo.usp_DonHang_Tao. Viết lại phiên A cho cộng kho trước, sửa đơn sau. Hai phiên lúc đó xếp hàng trên sản phẩm 42 thay vì khóa chéo.
2. Giao dịch ngắn. Khóa giữ càng lâu, cửa sổ cho chu trình càng rộng.
3. Chỉ mục đúng. Câu UPDATE ... WHERE không có chỉ mục phù hợp quét và lấy U trên nhiều dòng hơn cần, va vào khóa của phiên khác. Kế hoạch có seek hay scan đọc ở Kế hoạch thực thi.

Thử lại ở ứng dụng #

Deadlock không loại bỏ được hoàn toàn. Ứng dụng phải thử lại cả giao dịch, không phải câu lệnh vừa lỗi: lúc nhận 1205, mọi thay đổi trước đó của giao dịch đã bị rollback. Chỉ thử lại lỗi mà chắc chắn giao dịch đã rollback: 1205 và 3960. Timeout (SqlException.Number = -2) không nằm trong danh sách: lúc client thôi chờ, server có thể đã commit.

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);
            }
        }
    }
}

Cần .NET 6 trở lên cho Random.Shared. Thứ làm vòng lặp an toàn là tính idempotent của cả giao dịch. Bên gọi lấy mã đơn một lần, chẳng hạn SELECT NEXT VALUE FOR dbo.seq_DonHangId với sequence ở Kiểu dữ liệu và khóa chính, chốt NgayTao, rồi mới gọi TaoDonHangAsync. Mọi lần thử dùng lại đúng hai giá trị đó. Nếu sau này mở rộng thử lại cho lỗi mạng, một lần thử trước có thể đã commit mà client không nhận được phản hồi. Lần thử sau khi đó gặp lỗi khóa chính 2627 trên PK_DonHang và toàn bộ giao dịch rollback, kể cả bước trừ kho, thay vì tạo đơn thứ hai và trừ kho hai lần. Lấy số bên trong thủ tục thì mỗi lần thử ra một số mới, và lớp bảo vệ này mất.

11. Optimized locking #

Tính năng gồm hai phần. Transaction ID (TID) locking: mỗi dòng mang mã giao dịch sửa nó lần cuối. Giao dịch sửa dòng vẫn lấy khóa dòng và page, nhưng nhả ngay sau khi sửa xong từng dòng. Thứ duy nhất giữ đến hết giao dịch là một khóa X trên tài nguyên XACT của chính nó. Phiên khác muốn chờ dòng đó thì xin S trên XACT. Job chốt sổ ở mục 4 vì vậy không còn tích hàng nghìn khóa, và leo thang hiếm khi xảy ra.

Lock after qualification (LAQ) chỉ chạy khi RCSI bật: câu sửa dữ liệu kiểm điều kiện WHERE trên bản commit mới nhất mà không lấy U. Dòng không thỏa thì bỏ qua, không chờ. Dòng thỏa mà đang bị giao dịch khác sửa thì chờ giao dịch đó, rồi kiểm lại điều kiện trước khi sửa. Câu trừ kho ở mục 6 vẫn đúng: B thấy 5 thỏa >= 3, chờ A, kiểm lại thấy 2 không thỏa, sửa 0 dòng.

LAQ đổi kết quả ở một kiểu code: câu sửa có điều kiện trên chính cột mà giao dịch khác đang đổi.

Không có LAQ, B chờ ở dòng 10051 rồi thấy 2 và chuyển sang 3. Có LAQ, B kiểm 10051 trên bản commit mới nhất, thấy 1, bỏ qua mà không chờ: đơn 10051 nằm lại ở trạng thái 2 đến lượt giao sau. Microsoft ghi rõ: kể cả khi không có LAQ, ứng dụng không nên giả định thứ tự thực thi chặt giữa các giao dịch dưới mức isolation dựa trên phiên bản. Luồng nào cần thứ tự đó thì dùng UPDLOCK hoặc mức isolation cao hơn.

LAQ không áp dụng khi câu lệnh có gợi ý UPDLOCK, HOLDLOCK, XLOCK, READCOMMITTEDLOCK, khi mức isolation khác READ COMMITTED, khi bảng có columnstore, với MERGE, và với câu có OUTPUT trả kết quả hoặc ghi vào biến bảng. Như vậy cách b ở mục 6 và upsert ở mục 7 giữ nguyên hành vi khóa đã mô tả.

SELECT DATABASEPROPERTYEX(N'BanHang', 'IsOptimizedLockingOn') AS optimized_locking;
-- NULL trên SQL Server 2019 và 2022: tính năng không có.

-- Chỉ SQL Server 2025 (17.x) trở lên. Mỗi lệnh cần không còn kết nối nào khác vào BanHang.
ALTER DATABASE BanHang SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE BanHang SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE BanHang SET OPTIMIZED_LOCKING = ON;

ADR là điều kiện bắt buộc. RCSI không bắt buộc nhưng thiếu nó thì không có LAQ. Khi bật, chờ khóa hiện với wait type LCK_M_S_XACT_READ và LCK_M_S_XACT_MODIFY, và sys.dm_tran_locks có dòng resource_type = 'XACT'. Deadlock giữa các giao dịch cùng sửa một tập dòng theo thứ tự ngược nhau vẫn xảy ra.

12. Quy tắc cho người viết ứng dụng #

1. Giao dịch ngắn và nằm trọn trong một lần gọi thủ tục. Không chờ người dùng, không gọi HTTP, không gửi email giữa BEGIN TRAN và COMMIT.
2. SET XACT_ABORT ON trong mọi thủ tục mở giao dịch. Nhánh bắt lỗi ở ứng dụng chạy IF @@TRANCOUNT > 0 ROLLBACK trước khi trả kết nối.
3. Cùng thứ tự truy cập bảng trong mọi luồng: SanPham, DonHang, ChiTietDonHang.
4. Ghi dựa trên giá trị vừa đọc: một câu UPDATE có điều kiện, hoặc UPDLOCK. Dữ liệu đi qua màn hình người dùng: rowversion. Upsert: UPDLOCK, HOLDLOCK.
5. Báo cáo không chặn ghi: bật RCSI. Không rải NOLOCK.
6. Việc lớn chia lô dưới ngưỡng 5.000 khóa, mỗi lô một giao dịch.
7. Thử lại cả giao dịch khi gặp 1205 hoặc 3960, với mã định danh sinh trước vòng lặp.
8. Biết driver đang làm gì: autocommit hay implicit transactions, CommandTimeout bao nhiêu, mức isolation mặc định của framework là gì.
9. Theo dõi: giao dịch mở quá 10 phút, deadlock trong system_health, index_lock_promotion_count tăng bất thường.

13. Những chỗ hay hiểu sai #

Đọc tiếp #

• Chỉ mục và thống kê: seek hay scan quyết định một câu lệnh khóa bao nhiêu dòng và khóa phạm vi rộng đến đâu.
• Kế hoạch thực thi: đọc kế hoạch để biết câu UPDATE nào quét cả partition trước khi nó leo thang.
• Điều tra truy vấn chậm: khi một API đột nhiên chậm, cách phân biệt chặn khóa (LCK_M_*, CPU thấp) với nghẽn CPU hay hàng đợi bộ nhớ.
• Kiến trúc lưu trữ PostgreSQL: MVCC của PostgreSQL giữ phiên bản dòng ngay trong bảng, khác version store của SQL Server.

Nguồn #

• Transaction locking and row versioning guide
• SET TRANSACTION ISOLATION LEVEL
• SET XACT_ABORT
• SET IMPLICIT_TRANSACTIONS
• TRY...CATCH
• COMMIT TRANSACTION
• ROLLBACK TRANSACTION
• Table hints
• MERGE — concurrency considerations
• ALTER DATABASE SET options
• Snapshot isolation in SQL Server (ADO.NET)
• Row versioning resource usage (SQL Server 2008 R2, phần 14 byte mỗi dòng)
• Deadlocks guide
• Use the system_health session
• sys.fn_xe_file_target_read_file
• KILL
• Understand and resolve blocking problems
• Resolve blocking problems caused by lock escalation
• Optimized locking
• Erland Sommarskog — Error and Transaction Handling in SQL Server, Part Two