SNAPSHOT

Giao dịch và Xử lý lỗi trong SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Mọi thay đổi được lưu trong tempDB
Chỉ thấy thay đổi đã commit trước khi giao dịch SNAPSHOT bắt đầu và thay đổi của chính nó
Không thấy thay đổi từ giao dịch khác sau khi SNAPSHOT bắt đầu
Đọc không chặn ghi và ghi không chặn đọc
Có thể xảy ra xung đột cập nhật

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - so sánh mức cô lập

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes
REPEATABLE READ	no	no	yes
SERIALIZABLE	no	no	no
SNAPSHOT	no	no	no

SNAPSHOT - ví dụ

Giao dịch 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Giao dịch 2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Không bị chặn!

SNAPSHOT - ví dụ

Giao dịch 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Giao dịch 2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Không bị chặn!

SNAPSHOT - tóm tắt

Ưu điểm:

Tính nhất quán tốt: Ngăn dirty, non-repeatable và phantom reads mà không khóa

Nhược điểm:

tempDB tăng dung lượng

Khi nào dùng?:

Khi cần tính nhất quán và không muốn khóa

READ COMMITTED SNAPSHOT

Thay đổi hành vi của READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

Mặc định OFF
Để dùng ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Bật ON: mọi câu lệnh READ COMMITTED chỉ thấy thay đổi đã commit trước khi câu lệnh bắt đầu
Không có xung đột cập nhật

READ COMMITTED SNAPSHOT - ví dụ

Giao dịch 1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Giao dịch 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED SNAPSHOT - ví dụ

Giao dịch 1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Giao dịch 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 30000,00        |

WITH (NOLOCK)

Dùng để đọc dữ liệu chưa commit
READ UNCOMMITTED áp dụng cho toàn bộ kết nối / WITH (NOLOCK) áp dụng cho một bảng cụ thể
Dùng dưới bất kỳ mức cô lập nào khi chỉ cần đọc dữ liệu chưa commit từ một số bảng

WITH (NOLOCK) - ví dụ

Số dư ban đầu tài khoản 5 = $35.000

Giao dịch 1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

Giao dịch 2

...

SELECT current_balance
FROM accounts WITH (NOLOCK) 
WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

Ayo berlatih!

Giao dịch và Xử lý lỗi trong SQL Server