Mức isolation và SNAPSHOT

Báo cáo cuối ngày của BanHang đọc tổng tiền rồi đọc danh sách đơn, và hai con số lệch nhau vì một đơn commit đúng lúc giữa hai câu. Chọn sai mức isolation thì báo cáo in số chưa từng commit, đếm sót đơn mới, hoặc để hai phiên cùng phá một quy tắc nghiệp vụ. Đọc xong, bạn biết mức nào chặn hiện tượng nào, khi nào cần SNAPSHOT, và đặt mức isolation đúng từ .NET.

Bài thứ ba trong năm bài về giao dịch, cùng mốc với bài Giao dịch và XACT_ABORT: SQL Server 2019, READ_COMMITTED_SNAPSHOT và ADR tắt. Các chế độ khóa S, U, X ở bài Khóa và leo thang khóa.

1. 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 2). 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 (mục 3 đo điều này), 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 và cách sửa nằm ở bài Mất cập nhật và upsert. 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 B-tree, seek, scan và key lookup.

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, cả hai phiên ở READ COMMITTED.

sequenceDiagram
  participant A as Phiên A
  participant D as Đơn mở của khách 42
  participant B as Phiên B
  A->>D: 12:20:00 BEGIN TRAN, đếm được 4
  B->>D: 12:20:01 BEGIN TRAN, đếm được 4
  A->>D: 12:20:02 4 nhỏ hơn 5, INSERT đơn 10048
  B->>D: 12:20:03 4 nhỏ hơn 5, INSERT đơn 10049
  A->>D: 12:20:04 COMMIT
  B->>D: 12:20:05 COMMIT
  Note over A,B: Khách 42 có 6 đơn mở, vượt hạn mức 5

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.

2. 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 (vòng thử lại). 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. Mục 3 chạy đúng báo cáo này.

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 (đọc không chặn ghi, ADR). 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, nên 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ó.

3. Áp dụng trong .NET #

EF Core đặt mức isolation qua BeginTransactionAsync(IsolationLevel), chuyển xuống SqlConnection.BeginTransaction(IsolationLevel) của SqlClient. Báo cáo cuối ngày viết bằng EF Core, hai câu trong một giao dịch SNAPSHOT:

await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.Snapshot, ct);
var homNay = db.DonHang.Where(d => d.NgayTao >= ngay && d.NgayTao < ngay.AddDays(1));
decimal tong = await homNay.SumAsync(d => d.TongTien, ct);           // ảnh chụp bắt đầu ở đây
var danhSach = await homNay.Select(d => d.TongTien).ToListAsync(ct); // cùng ảnh chụp
await tx.CommitAsync(ct);

MucIsolation.cs chạy báo cáo này trên LocalDB (SQL Server 2019, 15.0.4382), .NET 10.0.401, EF Core 10.0.12, một lần ở READ COMMITTED với RCSI bật và một lần ở SNAPSHOT. Giữa hai câu, một phiên khác chèn và commit đơn 10061 giá 300.000:

TransactionScope là bẫy thứ hai. new TransactionScope() không tham số mở giao dịch Serializable, và cùng chương trình cho thấy mức đó đi theo kết nối về pool:

TransactionScope mặc định: transaction_isolation_level = 4
  kết nối lấy lại từ pool sau scope: 4
TransactionScope với ReadCommitted: 2

Mức 4 là SERIALIZABLE, mức 2 là READ COMMITTED. Một request sau đó lấy đúng kết nối này sẽ chạy ở SERIALIZABLE và lấy khóa phạm vi mà không ai trong code yêu cầu. Luôn truyền mức isolation:

var docDaCommit = new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted };
using var scope = new TransactionScope(TransactionScopeOption.Required, docDaCommit,
    TransactionScopeAsyncFlowOption.Enabled);

Chương trình bật RCSI bằng ALTER DATABASE CURRENT ... WITH ROLLBACK IMMEDIATE rồi tắt lại, nên chỉ chạy trên database thử. Dòng #:property PublishAot=false cần vì file-based app của .NET 10 bật Native AOT mặc định, còn EF Core dựng model lúc chạy.

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.12
#:property PublishAot=false
// Báo cáo hai câu dưới RCSI và dưới SNAPSHOT; mức isolation của TransactionScope.
// Chạy: dotnet run MucIsolation.cs   (BANHANG_DB: database thử có dbo.DonHang; chương trình bật rồi tắt RCSI)
using System.Data;
using System.Transactions;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

string cs = Environment.GetEnvironmentVariable("BANHANG_DB")
    ?? @"Server=(localdb)\MSSQLLocalDB;Database=BanHang_Thu;Integrated Security=true;TrustServerCertificate=true";
System.Globalization.CultureInfo.CurrentCulture = new("vi-VN");

await QuanTri("ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE");
await QuanTri("ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON");
await QuanTri("UPDATE dbo.DonHang SET TongTien = 1750000.00 WHERE DonHangId = 10042 AND NgayTao = '2026-10-02T11:58:00'");

// 1. Báo cáo cuối ngày 2026-10-02: câu 1 lấy tổng, câu 2 lấy danh sách. Giữa hai câu, đơn 10061 commit.
foreach (var muc in new[] { System.Data.IsolationLevel.ReadCommitted, System.Data.IsolationLevel.Snapshot })
{
    await QuanTri("DELETE dbo.DonHang WHERE DonHangId = 10061");
    await using var db = new BanHangDb(cs);
    await using var tx = await db.Database.BeginTransactionAsync(muc);
    decimal tong = await db.DonHang.Where(d => d.NgayTao >= new DateTime(2026, 10, 2) && d.NgayTao < new DateTime(2026, 10, 3))
                                   .SumAsync(d => d.TongTien);
    await QuanTri("INSERT INTO dbo.DonHang (DonHangId, NgayTao, KhachHangId, TrangThai, TongTien) VALUES (10061, '2026-10-02T20:00:00', 42, 2, 300000.00)");
    var ds = await db.DonHang.Where(d => d.NgayTao >= new DateTime(2026, 10, 2) && d.NgayTao < new DateTime(2026, 10, 3))
                             .Select(d => d.TongTien).ToListAsync();
    await tx.CommitAsync();
    Console.WriteLine($"{(muc == System.Data.IsolationLevel.Snapshot ? "SNAPSHOT" : "RCSI    ")}: câu 1 tổng {tong:N0}; câu 2 có {ds.Count} đơn, cộng lại {ds.Sum():N0}");
}
await QuanTri("DELETE dbo.DonHang WHERE DonHangId = 10061");

// 2. TransactionScope
const string Muc = "SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID";
string api = cs + ";Application Name=BanHang.Api";
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    await using var c = new SqlConnection(api);
    await c.OpenAsync();
    Console.WriteLine($"TransactionScope mặc định: transaction_isolation_level = {await new SqlCommand(Muc, c).ExecuteScalarAsync()}");
    scope.Complete();
}
await using (var c = new SqlConnection(api))
{
    await c.OpenAsync();
    Console.WriteLine($"  kết nối lấy lại từ pool sau scope: {await new SqlCommand(Muc, c).ExecuteScalarAsync()}");
}
var docDaCommit = new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted };
using (var scope = new TransactionScope(TransactionScopeOption.Required, docDaCommit, TransactionScopeAsyncFlowOption.Enabled))
{
    await using var c = new SqlConnection(api);
    await c.OpenAsync();
    Console.WriteLine($"TransactionScope với ReadCommitted: {await new SqlCommand(Muc, c).ExecuteScalarAsync()}");
    scope.Complete();
}

SqlConnection.ClearAllPools();
await QuanTri("ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE");

async Task QuanTri(string sql)
{
    await using var c = new SqlConnection(cs + ";Pooling=false");
    await c.OpenAsync();
    await new SqlCommand(sql, c).ExecuteNonQueryAsync();
}

public class DonHang
{
    public long DonHangId { get; set; }
    public DateTime NgayTao { get; set; }
    public decimal TongTien { get; set; }
}

public class BanHangDb(string cs) : DbContext
{
    public DbSet<DonHang> DonHang => Set<DonHang>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseSqlServer(cs);
    protected override void OnModelCreating(ModelBuilder m) => m.Entity<DonHang>(e =>
    {
        e.ToTable("DonHang", "dbo");
        e.HasKey(d => new { d.NgayTao, d.DonHangId });
        e.Property(d => d.NgayTao).HasColumnType("datetime2(0)");
        e.Property(d => d.TongTien).HasColumnType("decimal(18, 2)");
    });
}

RCSI    : câu 1 tổng 2.650.000; câu 2 có 3 đơn, cộng lại 2.950.000
SNAPSHOT: câu 1 tổng 2.650.000; câu 2 có 2 đơn, cộng lại 2.650.000
TransactionScope mặc định: transaction_isolation_level = 4
  kết nối lấy lại từ pool sau scope: 4
TransactionScope với ReadCommitted: 2

Những chỗ hay hiểu sai #

Kết luận #

Mức isolation quyết định câu đọc thấy gì, không quyết định câu ghi nhả khóa khi nào; báo cáo cần số khớp nhau thì dùng SNAPSHOT, còn quy tắc nghiệp vụ dựa trên một lần đếm thì phải khóa đúng khoảng mình đã đếm.

Trong dự án .NET của bạn:

• Truyền mức isolation tường minh cho mọi TransactionScope và BeginTransactionAsync; tìm new TransactionScope() không tham số trong code.
• Báo cáo đọc nhiều câu trong một giao dịch dùng BeginTransactionAsync(IsolationLevel.Snapshot) sau khi bật ALLOW_SNAPSHOT_ISOLATION.
• Gỡ WITH (NOLOCK) khỏi truy vấn báo cáo; bật RCSI nếu cần đọc không chặn ghi, và theo dõi version store trong tempdb.
• Quy tắc kiểu "tối đa 5 đơn mở" đếm với WITH (UPDLOCK, HOLDLOCK) trong cùng giao dịch với câu chèn.

Đọc tiếp #

• Bài trước trong series: SQL Server — Khóa và leo thang khóa.
• Bài sau trong series: SQL Server — Mất cập nhật tồn kho và upsert: đọc rồi ghi làm mất cập nhật ở cả READ COMMITTED lẫn RCSI, và bốn cách sửa trong EF Core.
• PostgreSQL — MVCC, HOT update và VACUUM: 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
• Table hints
• 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)
• TransactionScope class
• EF Core — Transactions