SERIALIZABLE 分離レベル

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

Miriam Antona

Software Engineer

SERIALIZABLE

最も制約の強い分離レベル

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

分離レベルの比較

	ダーティ読み取り	反復不能読み取り	ファントム読み取り
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes
REPEATABLE READ	no	no	yes
SERIALIZABLE	no	no	no

SERIALIZABLE によるロック対象

インデックス範囲に基づく WHERE 句のクエリ -> その範囲の行のみをロック
インデックス範囲に基づかないクエリ -> テーブル全体をロック

SERIALIZABLE - インデックス範囲に基づく問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

該当行をロック

SERIALIZABLE - インデックス範囲に基づく問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

該当行をロック

トランザクション 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

待機が必要！

SERIALIZABLE - インデックス範囲に基づく問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

トランザクション 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

待機が必要！

SERIALIZABLE - インデックス範囲に基づく問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

COMMIT TRAN

トランザクション 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

ついに実行！

SERIALIZABLE - インデックス範囲に基づく問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

トランザクション 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (200, 'Phantom', 'Ph', '[email protected]', 555666222);

即時に挿入！

SERIALIZABLE - インデックス範囲に基づかない問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

テーブル全体をロック

トランザクション 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

待機が必要！

SERIALIZABLE - インデックス範囲に基づかない問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

トランザクション 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

待機が必要！

SERIALIZABLE - インデックス範囲に基づかない問い合わせ

トランザクション 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

COMMIT TRAN

トランザクション 2

...

INSERT INTO customers 
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

ついに実行！

SERIALIZABLE - まとめ

長所:

高い整合性: ダーティ/反復不能/ファントム読み取りを防止

短所:

SERIALIZABLE 取引によりブロックされ得る

使用タイミング:

データ整合性が最優先のとき

Ayo berlatih!

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