併發控制是多個事務在併發執行時，資料庫保證事務一致性（Consistency）和隔離性（Isolation）的一種機制。主流商用關聯式資料庫使用的併發控制技術主要有三種：嚴格兩階段封鎖（S2PL）、多版本併發控制（MVCC）和樂觀併發控制（OCC）。
本文是PostgreSQL併發控制的第4篇，介紹了在RR（Repeatable Read，可重複讀）隔離級別下MySQL和PostgreSQL的異同。

### 一、PostgreSQL RR Isolation Level
PostgreSQL的RR隔離級別，其實是SI隔離級別，要求滿足以下兩個規則：
Rule 1：事務T讀取資料物件x，其中x是T啟動前已提交事務產生的最新版本
Rule 2：併發事務的寫集合之間不相交，否則會出現衝突，其中一個事務必須回滾
PostgreSQL使用的衝突處理協議是FUW（First Updater Wins，先更新者勝）：事務Tj已持有資料物件x的鎖，同時Ti希望變更x，則Ti必須等待直至Tj提交或回滾；如Tj提交，則Ti回滾，如Tj回滾，則Ti成功獲取x的寫鎖，繼續執行。

**Rule 1**，PostgreSQL RR隔離級別下，在啟動事務時獲取快照，以後該事務均使用該快照作為元組可讀性的判斷依據，簡單來說就是在此時間點之前已提交的修改，可見，否則（包括未提交或者回滾的），不可見。
**Rule 2**，參見下面的例子：

|時間點|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)

```

### 二、MySQL RR Isolation Level
MySQL預設的隔離級別是RR
```
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

```
在RR隔離級別下，PostgreSQL可以保證“讀不會阻塞寫，寫不會阻塞讀”，但MySQL在RR下會出現阻塞的情況，詳見下面的例子。

執行的SQL指令碼：
```
use testdb;
CREATE TABLE tbl1(counter int);
CREATE TABLE tbl2(counter int);
```
SQL執行順序：

|時間點|T1|T2|
| ------ | ------ | ------ |
|t1|begin;| |
|t2|  |begin;|
|t3|INSERT INTO tbl1 SELECT count(*) FROM tbl2;|
|t4|  |INSERT INTO tbl2 SELECT count(*) FROM tbl1;|
|||<session hang="">|
|t5|commit;|
|t6|  |執行成功|

在Session Hang的時候使用show engine innodb status;命令檢視TRANSACTIONS資訊
```
------------
TRANSACTIONS
------------
Trx id counter 2591
Purge done for trx's n:o < 2587 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421821791990000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421821791988288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421821791987432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2590, ACTIVE 63 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 140346785715968, query id 52 localhost root executing
INSERT INTO tbl2 SELECT count(*) FROM tbl1
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9 page no 4 n bits 72 index GEN_CLUST_INDEX of table `testdb`.`tbl1` trx id 2590 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000020300; asc       ;;
 1: len 6; hex 000000000a1d; asc       ;;
 2: len 7; hex 81000001090110; asc        ;;
 3: len 4; hex 80000000; asc     ;;

------------------
---TRANSACTION 2589, ACTIVE 80 sec
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 140346786010880, query id 36 localhost root
--------
```
注意其中的等待資訊：
```
...
RECORD LOCKS space id 9 page no 4 n bits 72 index GEN_CLUST_INDEX of table `testdb`.`tbl1` trx id 2590 lock mode S waiting
...
```
可以看到，T2在等待testdb.tbl1索引GEN_CLUST_INDEX（索引組織表內部建立的索引）上的共享鎖，無法獲取是因為需要等待T1持有tbl1索引GEN_CLUST_INDEX的RECORD X鎖。
```
mysql> SELECT ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,object_schema,object_name,index_name,LOCK_TYPE,lock_mode,lock_status,lock_data  FROM performance_schema.data_locks  where object_name='tbl1' and index_name is not null  order by ENGINE_TRANSACTION_ID;
+---------------------------------------+-----------------------+---------------+-------------+-----------------+-----------+---------------+-------------+----------------+
| ENGINE_LOCK_ID                        | ENGINE_TRANSACTION_ID | object_schema | object_name | index_name      | LOCK_TYPE | lock_mode     | lock_status | lock_data      |
+---------------------------------------+-----------------------+---------------+-------------+-----------------+-----------+---------------+-------------+----------------+
| 140346815278488:9:4:2:140346713517384 |                  2589 | testdb        | tbl1        | GEN_CLUST_INDEX | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000020300 |
| 140346815280200:9:4:2:140346713529984 |                  2590 | testdb        | tbl1        | GEN_CLUST_INDEX | RECORD    | S             | WAITING     | 0x000000020300 |
+---------------------------------------+-----------------------+---------------+-------------+-----------------+-----------+---------------+-------------+----------------+
2 rows in set (0.00 sec)

```

PostgreSQL使用heap table，沒有“GEN_CLUST_INDEX”這一資料結構，自然也無需對該資料結構進行併發控制，而MySQL使用索引組織表，提升讀取效能的同時但需要額外對這一資料結構進行管理和維護。

除了寫可能會阻塞讀之外，MySQL還有一些讓PGer詫異的現象。
測試SQL指令碼：
```
mysql> drop table tbl;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE tbl(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tbl VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

```
執行順序：

|時間點|T1|T2|
| ------ | ------ | ------ |
|t1|begin;| |
|t2|  |begin;|
|t3|UPDATE tbl SET id=id-1;| |
|t4|  |SELECT * FROM tbl; |
|t5|DELETE FROM tbl WHERE id=4;| |
|||Session Wait!|
|t6|commit;| |
|t7| |select * from tbl;|
|t8| |DELETE FROM tbl WHERE id=4;|

執行結果是：

```
...
-- T1
mysql> select * from tbl;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)

-- T2
...
mysql> select * from tbl;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> DELETE FROM tbl WHERE id=4;
Query OK, 0 rows affected (0.00 sec)

```
查詢結果滿足RR的要求返回先前的元組版本，這本身沒有問題，但對PGer來說不太好理解的地方是id = 4這條記錄查詢時明明存在，但執行delete時卻找不到該記錄，結果返回0行。

另外值得一提的是，相對於PostgreSQL，MySQL有很”豐富“的鎖型別，從鎖本身的語義出發來理解鎖雖然直觀但看不到背後的原理，如果從併發控制的角度來看MySQL的鎖，可以理解這些鎖存在的價值或者意義，會有不一樣的認識。

### 三、參考資料
[1] MySQL Document,InnoDB Locking,GEN_CLUST_INDEX...
[2] Daniel Verite,Isolation Repeatable Read in PostgreSQL versus MySQL</session>

PostgreSQL 併發控制機制（4）：RR隔離級別，MySQL vs PostgreSQL

相關文章