SNAPSHOT

SQL Server 的交易與錯誤處理

Miriam Antona

Software Engineer

SNAPSHOT

每次修改都會儲存在 tempDB 資料庫
只能看到在 SNAPSHOT 交易開始前已提交的變更，以及自己的變更
看不到其他交易在 SNAPSHOT 交易開始後所做的任何變更
讀不會鎖寫，寫不會鎖讀
可能發生更新衝突

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - 隔離層級比較

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

Transaction1

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       |

Transaction2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

不會被封鎖！

SNAPSHOT - 範例

Transaction1

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       |

Transaction2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

不會被封鎖！

SNAPSHOT - 重點整理

優點：

良好的一致性：在不封鎖的情況下避免髒讀、不可重複讀與幻讀

缺點：

tempDB 佔用增加

何時使用？

當一致性必須保證，且不希望發生封鎖時

READ COMMITTED SNAPSHOT

變更 READ COMMITTED 的行為

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

預設為 OFF
要使用 ON：

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

設為 ON 後，每個 READ COMMITTED 陳述式只能看到該陳述式開始前已提交的變更
不會產生更新衝突

READ COMMITTED SNAPSHOT - 範例

Transaction1

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

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT - 範例

Transaction1

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

COMMIT TRAN

Transaction2

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)

用來讀取未提交資料
READ UNCOMMITTED 作用於整個連線／WITH (NOLOCK) 作用於特定資料表
在任一隔離層級下，當你只想讀取特定資料表的未提交資料時使用

WITH (NOLOCK) - 範例

帳戶 5 原始餘額 = $35,000

Transaction1

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

Transaction2

...

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

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

一起來練習吧！

SQL Server 的交易與錯誤處理