Isolation Levels

Isolation Levels (Mức độ cô lập) xác định mức độ mà một transaction phải bị cô lập khỏi các thao tác đọc/ghi của các transaction đồng thời khác. Chọn đúng isolation level là sự cân bằng giữa tính nhất quán dữ liệu và hiệu suất đồng thời (concurrency).

1. Các vấn đề Concurrency (Tại sao cần Isolation Levels?)

Dirty Read

Transaction đọc dữ liệu mà transaction khác đang sửa nhưng chưa COMMIT. Nếu transaction kia ROLLBACK, dữ liệu đã đọc là không tồn tại.

-- Session 1: Chưa commit
BEGIN TRANSACTION;
UPDATE Products SET Price = 999 WHERE ProductId = 1; -- Giá thực: 500
-- Chưa COMMIT...

-- Session 2 (READ UNCOMMITTED): Đọc được 999 — Dirty Read!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Price FROM Products WHERE ProductId = 1; -- Trả về 999 (SAI!)

-- Session 1: Rollback → giá thực vẫn là 500
ROLLBACK;

Non-repeatable Read

Cùng một row được đọc hai lần trong cùng transaction nhưng cho kết quả khác nhau vì transaction khác đã UPDATE/DELETE row đó giữa hai lần đọc.

-- Session 1 (READ COMMITTED):
BEGIN TRANSACTION;
SELECT Price FROM Products WHERE ProductId = 1; -- Lần 1: 500

-- Session 2: Commit update
UPDATE Products SET Price = 800 WHERE ProductId = 1;
COMMIT;

-- Session 1: Đọc lại (vẫn trong cùng transaction)
SELECT Price FROM Products WHERE ProductId = 1; -- Lần 2: 800 → Non-repeatable Read!
ROLLBACK;

Phantom Read

Cùng một query (với WHERE condition) được chạy hai lần nhưng trả về số rows khác nhau vì transaction khác đã INSERT/DELETE rows phù hợp với điều kiện WHERE giữa hai lần chạy.

-- Session 1 (REPEATABLE READ):
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE Amount > 1000000; -- Lần 1: 5 rows

-- Session 2: Thêm order mới
INSERT INTO Orders (CustomerId, Amount) VALUES (1, 2000000);
COMMIT;

-- Session 1: Đọc lại
SELECT * FROM Orders WHERE Amount > 1000000; -- Lần 2: 6 rows → Phantom Read!
ROLLBACK;

Lost Update

Hai transactions cùng đọc một giá trị, sau đó cùng update dựa trên giá trị đọc được — một trong hai update bị ghi đè (overwrite) mất.

-- Cả hai cùng đọc: Quantity = 100
-- Session 1: UPDATE Products SET Quantity = 100 - 10 = 90 WHERE Id = 1;
-- Session 2: UPDATE Products SET Quantity = 100 - 15 = 85 WHERE Id = 1;
-- Kết quả cuối: 85 (mất đi việc trừ 10 của Session 1!) → Lost Update

2. Ma trận Isolation Levels vs Concurrency Problems

* RCSI đọc snapshot tại thời điểm statement bắt đầu — không phải transaction bắt đầu.
** SNAPSHOT phát hiện write conflicts và fail transaction.

3. READ UNCOMMITTED — Mức thấp nhất

Cơ chế: Không lấy bất kỳ shared lock nào khi đọc. Đọc được mọi dữ liệu kể cả chưa commit.

Cho phép: Dirty Read, Non-repeatable Read, Phantom Read.

-- Cách 1: Set cho toàn session
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Orders;

-- Cách 2: NOLOCK hint (tương đương READ UNCOMMITTED cho query đó)
SELECT * FROM Orders WITH (NOLOCK);

-- Cách 3: READUNCOMMITTED hint
SELECT * FROM Orders WITH (READUNCOMMITTED);

Khi nào dùng: Báo cáo approximate (ước tính) cần tốc độ, chấp nhận số liệu không chính xác tuyệt đối. Ví dụ: Dashboard hiển thị “khoảng X đơn hàng đang xử lý”.

⚠️ Cảnh báo nghiêm trọng với NOLOCK:

• Đọc được dữ liệu sẽ bị rollback (Dirty Read)
• Có thể skip rows (bỏ sót rows) khi page split xảy ra
• Có thể đọc trùng rows (duplicate rows)
• KHÔNG BAO GIỜ dùng trong: báo cáo tài chính, tính toán tổng/số lượng cần chính xác, business logic quan trọng.

4. READ COMMITTED — Mặc định của SQL Server

Cơ chế: Lấy shared lock khi đọc, giải phóng ngay khi đọc xong (statement-level). Chỉ đọc dữ liệu đã COMMIT.

Ngăn chặn: Dirty Read.
Cho phép: Non-repeatable Read, Phantom Read.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Đây là mặc định

BEGIN TRANSACTION;
    SELECT Price FROM Products WHERE ProductId = 1; -- 500, lấy S lock rồi release
    
    -- Trong lúc này, session khác CÓ THỂ update và commit!
    
    SELECT Price FROM Products WHERE ProductId = 1; -- Có thể là 800 → Non-repeatable Read
COMMIT;

Ưu điểm: Cân bằng tốt giữa consistency và concurrency. Phù hợp cho hầu hết OLTP scenarios.
Nhược điểm: Readers block writers (và ngược lại) → giải quyết bằng RCSI.

5. REPEATABLE READ

Cơ chế: Giữ shared lock cho đến khi transaction kết thúc. Đảm bảo cùng một row đọc nhiều lần sẽ cho kết quả giống nhau.

Ngăn chặn: Dirty Read, Non-repeatable Read, Lost Update.
Cho phép: Phantom Read.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;
    SELECT Price FROM Products WHERE ProductId = 1; -- 500, giữ S lock
    
    -- Session khác muốn UPDATE ProductId = 1 sẽ bị BLOCK!
    -- S lock giữ cho đến COMMIT
    
    SELECT Price FROM Products WHERE ProductId = 1; -- LUÔN LUÔN là 500
    
    -- Nhưng INSERT rows mới vẫn có thể xảy ra → Phantom Read vẫn được
COMMIT; -- Giải phóng tất cả S locks

Khi nào dùng: Khi cần đọc cùng dữ liệu nhiều lần trong cùng transaction và phải nhất quán (ví dụ: tính toán phức tạp trên cùng tập dữ liệu).
Nhược điểm: Giảm concurrency đáng kể vì giữ locks lâu.

6. SERIALIZABLE — Mức cao nhất

Cơ chế: Thêm key-range locks vào REPEATABLE READ. Ngăn chặn INSERT vào ranges mà transaction đang query.

Ngăn chặn: Tất cả — Dirty Read, Non-repeatable Read, Phantom Read, Lost Update.
Cho phép: Không có concurrency anomaly nào.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
    -- Query này lock cả key-range Orders WHERE Amount > 1000000
    SELECT * FROM Orders WHERE Amount > 1000000; -- 5 rows, lock key range
    
    -- Session khác muốn INSERT Orders (Amount = 2000000) sẽ bị BLOCK!
    
    SELECT * FROM Orders WHERE Amount > 1000000; -- LUÔN LUÔN là 5 rows
COMMIT;

Khi nào dùng: Giao dịch tài chính cần độ chính xác tuyệt đối, kiểm kho (inventory check + reserve), phân bổ số thứ tự (allocation).
Nhược điểm: Giảm concurrency nhất, dễ gây deadlock nhất.

7. READ COMMITTED SNAPSHOT ISOLATION (RCSI)

RCSI là phiên bản “optimistic” của READ COMMITTED, dùng row versioning thay vì shared locks.

Cách hoạt động

Khi a row bị UPDATE, SQL Server:

1. Ghi phiên bản cũ của row vào Version Store trong tempdb
2. Row trên data page được update với version mới

Khi một reader muốn đọc, thay vì chờ lock → reader đọc committed version gần nhất từ Version Store (không cần lock!).

-- Bật RCSI cho database (phải không có connection nào đang dùng)
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

-- Kiểm tra
SELECT name, is_read_committed_snapshot_on
FROM sys.databases WHERE name = 'MyDatabase';

-- Khi đã bật, không cần thay đổi code:
-- Tất cả READ COMMITTED queries tự động dùng row versioning
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Vẫn dùng level này
SELECT * FROM Orders; -- Nhưng không block, không bị block

So sánh READ COMMITTED vs RCSI

-- Scenario: Session 1 UPDATE chưa commit, Session 2 SELECT

-- Với READ COMMITTED (không có RCSI):
-- Session 1: BEGIN TRAN; UPDATE Orders SET Status = 'Processed' WHERE Id = 1;
-- Session 2: SELECT * FROM Orders WHERE Id = 1; → BLOCKED! Phải chờ Session 1

-- Với RCSI bật:
-- Session 1: BEGIN TRAN; UPDATE Orders SET Status = 'Processed' WHERE Id = 1;
-- Session 2: SELECT * FROM Orders WHERE Id = 1; → Đọc ngay phiên bản cũ (committed)!
-- Không bị block!

Đặc điểm của RCSI

• Statement-level snapshot: Mỗi statement thấy committed data tại thời điểm statement bắt đầu
• Writers không block Readers và ngược lại
• Overhead: tempdb phải lưu version store → tempdb lớn hơn
• Mặc định trong Azure SQL Database: Luôn bật

8. SNAPSHOT ISOLATION

SNAPSHOT ISOLATION là transaction-level snapshot — toàn bộ transaction thấy cùng một snapshot từ thời điểm transaction bắt đầu (không phải statement bắt đầu).

-- Step 1: Cho phép Snapshot Isolation ở database level
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Step 2: Session sử dụng
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION; -- Snapshot được tạo tại thời điểm này!
    -- Mọi SELECT trong transaction này thấy data tại thời điểm BEGIN TRAN
    SELECT * FROM Orders WHERE CustomerId = 1; -- Snapshot tại T0

    -- Dù session khác commit update, bạn vẫn thấy snapshot T0
    
    SELECT * FROM Orders WHERE CustomerId = 1; -- Vẫn là snapshot T0 → Repeatable!
COMMIT;

Write Conflict Detection — Điểm khác biệt với RCSI

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION; -- Snapshot tại T0, Orders.Status = 'Pending'

    -- Session khác: UPDATE Orders SET Status = 'Cancelled' WHERE OrderId = 1; COMMIT;
    
    -- Bạn cố UPDATE cùng row:
    UPDATE Orders SET Status = 'Processed' WHERE OrderId = 1;
    -- → LỖI! Error 3960: Snapshot isolation transaction aborted due to update conflict.
    -- SNAPSHOT phát hiện row đã bị modify sau khi transaction bắt đầu
COMMIT;

Đây là Optimistic Concurrency: Assume không có conflict, nhưng check khi commit. Phù hợp khi conflicts hiếm.

9. Row Versioning — Cơ chế nền tảng

Cả RCSI và SNAPSHOT ISOLATION đều dùng Row Versioning Store trong tempdb:

Data Page (data file):
┌─────────────────────────────────────────────┐
│ Row: OrderId=1, Amount=500, [Version Ptr] ───┼──► tempdb Version Store
└─────────────────────────────────────────────┘

tempdb Version Store:
┌─────────────────────────────────────────────┐
│ Version 1: OrderId=1, Amount=500 (original) │ ← Reader thấy cái này
│ Version 2: OrderId=1, Amount=800 (new)      │ ← Writer đang update
└─────────────────────────────────────────────┘

Monitor Version Store:

-- Xem kích thước version store
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    SUM(version_store_reserved_page_count) * 8 AS version_store_KB
FROM sys.dm_db_file_space_usage
GROUP BY database_id
ORDER BY version_store_KB DESC;

-- Xem oldest active snapshot transaction (giữ versions lâu nhất)
SELECT 
    transaction_id,
    transaction_begin_time,
    DATEDIFF(SECOND, transaction_begin_time, GETDATE()) AS seconds_open,
    elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY transaction_begin_time;

10. Đặt Isolation Level

Session Level

-- Cài đặt cho toàn bộ session từ đây trở đi
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Mặc định
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;           -- Cần ALLOW_SNAPSHOT_ISOLATION ON

Query Level (Table Hints)

-- Read Uncommitted (Dirty Read OK)
SELECT * FROM Orders WITH (NOLOCK);
SELECT * FROM Orders WITH (READUNCOMMITTED);

-- Read Committed (default behavior)
SELECT * FROM Orders WITH (READCOMMITTED);

-- Repeatable Read
SELECT * FROM Orders WITH (REPEATABLEREAD);

-- Serializable
SELECT * FROM Orders WITH (SERIALIZABLE);
SELECT * FROM Orders WITH (HOLDLOCK); -- Tương đương SERIALIZABLE

-- Update Lock (dùng khi chuẩn bị UPDATE)
SELECT * FROM Orders WITH (UPDLOCK) WHERE OrderId = 1;

-- Serializable + Update Lock
SELECT * FROM Orders WITH (UPDLOCK, HOLDLOCK) WHERE OrderId = 1;

11. Performance Implications

Locking Approach (READ COMMITTED, REPEATABLE READ, SERIALIZABLE)

Ưu điểm:
+ Không overhead để ghi version history
+ Đơn giản, ít phức tạp

Nhược điểm:
- Readers block Writers
- Writers block Readers  
- Deadlock risk cao hơn
- Latency cao hơn trong môi trường contention cao

Row Versioning Approach (RCSI, SNAPSHOT)

Ưu điểm:
+ Readers không block Writers (và ngược lại)
+ Latency thấp hơn khi có contention
+ Ít deadlock hơn (read-write deadlocks không xảy ra)

Nhược điểm:
- tempdb overhead (version store phải duy trì)
- Long-running transactions → version store phình to
- Slight overhead để đọc từ version chain
- Write conflicts trong SNAPSHOT có thể cần retry logic

Benchmark guidelines

-- Kiểm tra version cleanup performance
SELECT 
    version_store_reserved_page_count * 8.0 / 1024 AS version_store_MB,
    version_cleanup_rate_kb_per_s
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Transactions%'
AND counter_name IN ('Version Store Size (KB)', 'Version Cleanup rate (KB/s)');

12. Chọn Isolation Level đúng

OLTP (Online Transaction Processing)

-- Use Case: Đặt hàng, thanh toán
-- Dùng: READ COMMITTED + RCSI (best practice)
-- Lý do: Readers không block writers, ít deadlock, consistent reads

-- Setup database:
ALTER DATABASE OrderDB SET READ_COMMITTED_SNAPSHOT ON;
-- Không cần thay đổi code, tự động áp dụng

Báo cáo / Analytics trên Production DB

-- Use Case: Report cần chạy trên live DB, chấp nhận dữ liệu hơi cũ
-- Dùng: SNAPSHOT ISOLATION hoặc query on replica
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- Report queries - thấy consistent snapshot tại thời điểm bắt đầu
    SELECT SUM(Amount) FROM Orders WHERE OrderDate = CAST(GETDATE() AS DATE);
    SELECT COUNT(*) FROM Customers WHERE CreatedAt > DATEADD(DAY, -30, GETDATE());
COMMIT;

Tài chính / Tồn kho cần chính xác tuyệt đối

-- Use Case: Kiểm tra và giữ tồn kho trước khi bán
-- Dùng: SERIALIZABLE hoặc REPEATABLE READ + UPDLOCK hint
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- Check tồn kho
    SELECT Stock FROM Products WHERE ProductId = @ProductId;
    
    -- Trong serializable: không có INSERT/UPDATE nào có thể chen vào!
    
    IF (SELECT Stock FROM Products WHERE ProductId = @ProductId) >= @Quantity
    BEGIN
        UPDATE Products SET Stock = Stock - @Quantity WHERE ProductId = @ProductId;
        INSERT INTO Orders ...;
    END
COMMIT;

-- Hoặc dùng UPDLOCK hint để ít restrictive hơn:
BEGIN TRANSACTION;
    SELECT Stock 
    FROM Products WITH (UPDLOCK, ROWLOCK) -- Ngăn concurrent updates
    WHERE ProductId = @ProductId;
    
    UPDATE Products SET Stock = Stock - @Quantity WHERE ProductId = @ProductId;
COMMIT;

Kiểm tra nhanh không cần chính xác 100%

-- Use Case: Dashboard monitoring, approximate counts
-- Dùng: READ UNCOMMITTED / NOLOCK
SELECT 
    COUNT(*) AS ApproxPendingOrders
FROM Orders WITH (NOLOCK)
WHERE Status = 'Pending';

Tóm tắt