併發控制是多個事務在併發執行時，資料庫保證事務一致性（Consistency）和隔離性（Isolation）的一種機制。主流商用關聯式資料庫使用的併發控制技術主要有兩種：嚴格兩階段封鎖（S2PL）和多版本併發控制（MVCC）。
PostgreSQL使用了多版本併發控制技術的一種變體：快照隔離Sanpshot Isolation（簡稱SI）。透過SI，PostgreSQL提供了與ANSI SQL標準相應的四個隔離級別，但實現與標準定義有一些差異。

### 一、基礎知識
本節介紹用於抽象表達的形式化語言和標準隔離級別。
#### 1、形式化語言
為了更好的描述，使用以下更一般化（抽象）的形式化語言來描述資料庫事務操作。

**讀寫操作**
w和r，示例：w1(x),r2(y)
其中w和r分別表示寫和讀，1和2分別表示事務T1和T2，x和y表示資料項。
注意：這裡的w(寫)泛指對資料變更的操作，包括但不限於插入、刪除等；資料項是一個寬泛的概念，包括但不限於元組tuple、頁page甚至是整個關係relation。

**事務操作**
c和a，示例：c1,a2
c表示commit提交，其後的數字指代事務，如c1表示事務T1提交
a表示abort回滾，a2表示事務T2回滾

**謂詞**
謂詞P，通常與讀寫操作一起出現，示例：r1[P],w2[P]
r1[P]表示事務T1讀滿足謂詞P的結果集，
w2[P]表示事務T2對滿足謂詞P的結果集進行變更（寫）。

#### 2、ANSI SQL標準隔離級別
ANSI SQL標準透過現象（Phenomena）定義（除此之外還可以透過鎖、資料流圖定義），下面首先使用形式化語言來定義以下幾種現象：
*髒寫*P0 – （Dirty Write）
w1(x) w2(x) (c1 or a1)

*髒讀*P1 – Dirty Read
w1(x) r2(x) (c1 or a1)

*不可重複讀*P2 – Fuzzy or Non-Repeatable Read
r1(x) w2(x) (c1 or a1)

*幻讀*P3 – Phantom
r1(P)w2(y in P) (c1 or a1)
這裡的P表示邏輯術語謂詞（Predicate）

ANSI SQL標準透過禁止上述現象的形式定義了四個隔離級別，分別是讀未提交、讀已提交、可重複讀和可序列化：

|隔離級別|P0 Dirty Write|P1 Dirty Read|P2 Fuzzy Read|P3 Phantom|
| ---- | ---- | ----- | ---- | ---- | 
|Read Uncommitted |Not Possible|Possible|Possible|Possible|
|Read Committed |Not Possible|Not Possible|Possible|Possible|
|Repeatable Read |Not Possible|Not Possible|Not Possible|Possible|
|Serializable|Not Possible|Not Possible|Not Possible|Not Possible|

可序列化：禁止所有並發現象，效果如同事務以序列的方式執行
可重複讀：除幻讀外，禁止其他現象
讀已提交：禁止髒讀和髒寫
讀未提交：禁止髒寫

### 二、PostgreSQL隔離級別
PostgreSQL與ANSI SQL標準相應，也定義了四種隔離級別
```
testdb=# \help set transaction
Command:     SET TRANSACTION
Description: set the characteristics of the current transaction
Syntax:
SET TRANSACTION transaction_mode [, ...]
...

where transaction_mode is one of:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
...

```

#### 1、READ COMMITTED vs READ UNCOMMITTED
基於SI實現的併發控制，READ UNCOMMITTED和READ COMMITTED這兩個級別在PostgreSQL中沒有任何區別。
見下面例子，session 1啟動事務，插入資料到表t1中，但不提交事務；這時候session 2讀取t1的資料，按READ UNCOMMITTED的定義，應可“髒讀”t1的資料，但實際上並沒有獲取髒資料。
```
-- session 1
testdb=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

testdb=# select * from t1;
 id
----
(0 rows)

testdb=# START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION
testdb=# insert into t1 values(1);
INSERT 0 1
testdb=#

-- session 2
testdb=# START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION
testdb=#  select * from t1;
 id
----
(0 rows)

```
詳細可參考PostgreSQL手冊[1]。

#### 2、REPEATABLE READ
在PostgreSQL 9.0或以下版本並沒有該隔離級別，在9.1之後，PostgreSQL實現了Serializable Snapshot Isolation（簡稱SSI），原來的SERIALIZABLE變為新增的REPEATABLE READ，新的SSI成為SERIALIZABLE。實質上，PostgreSQL中的REPEATABLE READ隔離級別是Snapshot Isolation，比起ANSI SQL標準定義的可重複讀，除了P2 Non-Repeatable Read現象之外，還可以禁止P3 Phantom。
```
-- session 1
[local]:5432 postgres@testdb=# insert into t1 select generate_series(1,5);      
INSERT 0 5
Time: 3.638 ms
[local]:5432 postgres@testdb=# select * from t1 where id <= 10;                                 
id
----
  1
  2
  3
  4
  5
(5 rows)

Time: 0.607 ms
[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
Time: 0.292 ms
[local]:5432 postgres@testdb=#* select txid_current();
 txid_current
--------------
          502
(1 row)

Time: 0.284 ms

-- session 2
[local]:5432 postgres@testdb=# insert into t1 select generate_series(6,10);
INSERT 0 5
Time: 1.511 ms

[local]:5432 postgres@testdb=# select * from t1;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

Time: 0.434 ms

-- session 1
[local]:5432 postgres@testdb=#* select * from t1 where id <= 10;                                
id
----
  1
  2
  3
  4
  5
(5 rows)

Time: 0.536 ms

```
如上所示，得益於快照隔離提供的一致性讀，session 1查詢資料表t1，返回1-5，session 2插入5行資料（6-10）。session 1再次查詢表t1，返回的資料仍然是1-5（事務啟動時的快照）。

從理論上來說，快照隔離SI要求滿足以下兩個規則：
Rule 1：事務T讀取資料物件x，其中x是T啟動前已提交事務產生的最新版本
Rule 2：併發事務的寫集合之間不相交。使用形式化語言描述，wset(T)表示事務T的寫集合，設有兩個併發事務T1和T2，SI要求wset(T1) ∩ wset(T2) = ∅，否則出現衝突，其中一個事務必須回滾。

衝突處理常用的協議包括FCW（First Commit Wins，先提交者勝）和FUW（First Updater Wins，先更新者勝）。
FCW：事務Ti準備提交時，檢查是否存在其他已提交的事務變更了資料物件x，如存在，則回滾，否則提交
FUW：如事務Tj已持有資料物件x的鎖，同時Ti希望變更x，則Ti必須等待直至Tj提交或回滾；如Tj提交，則Ti回滾，如Tj回滾，則Ti可以獲取x的寫鎖，可以繼續執行
PostgreSQL在REPEATABLE READ級別使用FUW協議。

|時間點|T1|T2|
| ------ | ------ | ------ |
|t1|START TRANSACTION ISOLATION LEVEL REPEATABLE READ;| |
|t2|  |START TRANSACTION ISOLATION LEVEL REPEATABLE READ;|
|t3|update t1 set id = 1 where id = 5;|
|t4|  |update t1 set id = 11 where id = 5;|
|t5|commit;|
|t6|  |提示出錯|

執行輸出如下：
```
-- T1
[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
Time: 0.197 ms

-- T2
[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
Time: 0.181 ms

-- T1
[local]:5432 postgres@testdb=#* update t1 set id = 1 where id = 5;
UPDATE 1
Time: 0.430 ms

-- T2
[local]:5432 postgres@testdb=#* update t1 set id = 11 where id = 5;
---------->wait

-- T1
[local]:5432 postgres@testdb=#* commit;
COMMIT
Time: 3.241 ms
    
-- T2
[local]:5432 postgres@testdb=#* update t1 set id = 11 where id = 5;
ERROR:  could not serialize access due to concurrent update
Time: 3172.768 ms (00:03.173)

```

#### 3、SERIALIZABLE
REPEATABLE READ隔離級別已然禁止了P3 Phantom幻讀，PostgreSQL為何還提供SERIALIZABLE隔離級別？原因是可重複讀（其實是SI）不能實現可序列化，還存在不可序列化的寫偏序（Write Skew）現象。
**寫偏序（Write Skew）**
使用形式化語言來表示：設rset(Tn)是事務Tn讀資料涉及的資料項集合，wset(Tn)是事務Tn寫資料涉及的資料項集合。
現有兩個併發事務Ti和Tj，
1.Ti和Tj執行前，rset(Ti)和rset(Tj)之間滿足某種業務約束C
2.wset(Ti) ∩ rset(Tj) ≠ ∅ 且 rset(Ti) ∩ wset(Tj) ≠ ∅
3.Ti和Tj執行後，rset(Ti)和rset(Tj)不再滿足業務約束C
以上現象，稱為Write Skew現象。

一個經典的例子，現有資料表doctor，業務約束要求處於oncall狀態的醫生人數不能少於1人，但如果存在併發事務同時更新不同醫生的狀態，則能會出現Write Skew現象。下面是一個例子，測試資料如下：

|name|oncall|remark|
| ------ | ------ | ------ |
|Alice|true||
|Bob|true||
|Jacky|false||
|Susan|false||

測試指令碼：
```
drop table if exists doctor;
create table doctor(name varchar,oncall boolean,remark varchar);
insert into doctor(name,oncall,remark) values('Alice',true,'');
insert into doctor(name,oncall,remark) values('Bob',true,'');
insert into doctor(name,oncall,remark) values('Jacky',false,'');
insert into doctor(name,oncall,remark) values('Susan',false,'');

CREATE OR REPLACE FUNCTION change_doctor_status(pi_name in varchar) 
RETURNS boolean 
AS
$$ 
declare 
  v_count int := 0;
begin  
    -- 獲取處於oncall狀態的醫生數量
    select count(*) from doctor into v_count where oncall = true;
    -- 大於等於2，則設定醫生oncall為false
    if (v_count >= 2) then
      update doctor 
      set oncall = false
      where name = pi_name;
      -- 成功返回t
      return true;
    end if;
    -- 失敗返回f
    return false;
end;
$$  
LANGUAGE plpgsql;

```

併發事務T1和T2的操作序列：

|時間點|T1|T2|
| ------ | ------ | ------ |
|t1|START TRANSACTION ISOLATION LEVEL REPEATABLE READ;| |
|t2|  |START TRANSACTION ISOLATION LEVEL REPEATABLE READ;| |
|t3|select change_doctor_status('Alice');| |
|t4|  |select change_doctor_status('Bob');| |
|t5|commit;| |
|t6|  |commit;|

T1和T2完成後，資料如下：

|name|oncall|remark|
| ------ | ------ | ------ |
|Alice|false||
|Bob|false||
|Jacky|false||
|Susan|false||

處於oncall狀態的醫生數變為0，違反了oncall狀態醫生人數不能少於1人的業務約束。

**Serializable Snapshot Isolation**
PostgreSQL在9.1之後實現了SSI，設定隔離級別為SERIALIZABLE可禁止Write Skew現象，出現衝突時遵循FCW協議。

重新初始化資料，仍然使用上述操作序列，隔離級別設定為SERIALIZABLE：

|時間點|T1|T2|
| ------ | ------ | ------ |
|t1|START TRANSACTION ISOLATION LEVEL SERIALIZABLE;| |
|t2|  |START TRANSACTION ISOLATION LEVEL SERIALIZABLE;| |
|t3|select change_doctor_status('Alice');| |
|t4|  |select change_doctor_status('Bob');| |
|t5|commit;| |
|t6|  |commit;|

處於SERIALIZABLE隔離級別下，T2在執行commit的時候會報錯：
```
-- T1
[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION

-- T2
[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION

-- T1
[local]:5432 postgres@testdb=#* select change_doctor_status('Alice');
 change_doctor_status
----------------------
 t
(1 row)
Time: 0.682 ms

-- T2
[local]:5432 postgres@testdb=#* select change_doctor_status('Bob');
 change_doctor_status
----------------------
 t
(1 row)

-- T1
[local]:5432 postgres@testdb=#* commit;
COMMIT

-- T2
[local]:5432 postgres@testdb=#* commit;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
Time: 0.289 ms

```
SSI的實現原理，簡單來說，PostgreSQL的SSI機制會在讀操作時對讀物件加SIRead鎖，但該鎖不會阻塞寫也不會阻塞讀僅用於判斷是否會出現不可序列化的事務排程，透過該鎖跟蹤事務之間的衝突關係（rw依賴衝突），如可能出現環型結構，則終止其中一個事務。由於實現演算法已超出本文的範疇，在此不再展開。

值得一提的是，如果資料表上沒有索引，PostgreSQL會在整個relation上加SIRead鎖，這意味著對relation的所有寫操作只能序列執行，極大的降低併發度。

|時間點|T1|T2|
| ------ | ------ | ------ |
|t1|START TRANSACTION ISOLATION LEVEL SERIALIZABLE;|
|t2|  |START TRANSACTION ISOLATION LEVEL SERIALIZABLE;|
|t3|update tbl set c1 = 'x' where id = 1;|
|t4|  |update tbl set c1 = 'x' where id = 50000;|
|t5|commit;|
|t6|  |commit;|

示例表tbl沒有索引，有10萬行資料，值為1和50000的tuple不在同一個page中，在t6時刻T2提交時會報錯。

### 三、參考資料
[1].[PostgreSQL Manual](https://www.postgresql.org/docs/12/sql-set-transaction.html)
[2].[A Critique of ANSI SQL Isolation Levels](https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf)
[3].[Concurrency Control](http://www.interdb.jp/pg/pgsql05.html)

PostgreSQL 併發控制機制（1）：隔離級別

相關文章