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 交易阻擋

何時使用？

當資料一致性是關鍵

一起來練習吧！

SQL Server 的交易與錯誤處理