SNAPSHOT

SQL Server におけるトランザクションとエラー処理

Miriam Antona

Software Engineer

SNAPSHOT

すべての変更は tempDB に保持
SNAPSHOT トランザクション開始前にコミット済みの変更と自分の変更のみ参照
開始後に他トランザクションが行った変更は見えない
読み取りは書き込みをブロックせず、書き込みは読み取りをブロックしない
更新競合が起こり得る

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - 分離レベルの比較

	ダーティリード	非再現読み取り	ファントムリード
READ UNCOMMITTED	あり	あり	あり
READ COMMITTED	なし	あり	あり
REPEATABLE READ	なし	なし	あり
SERIALIZABLE	なし	なし	なし
SNAPSHOT	なし	なし	なし

SNAPSHOT - 例

トランザクション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       |

トランザクション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

ブロックされません！

SNAPSHOT - 例

トランザクション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       |

トランザクション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

ブロックされません！

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

トランザクション1

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

COMMIT TRAN

トランザクション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 - 例

トランザクション1

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

COMMIT TRAN

トランザクション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)

コミットされていないデータの読み取りに使用
READ UNCOMMITTED は接続全体に適用 / WITH (NOLOCK) は特定テーブルに適用
特定テーブルの未コミットのみ読みたい場合は、任意の分離レベルで使用可

WITH (NOLOCK) - 例

口座5の元残高 = $35,000

トランザクション1

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

トランザクション2

...

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

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

Vamos praticar!

SQL Server におけるトランザクションとエラー処理