并发控制是多个事务在并发运行时，数据库保证事务一致性（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

ITPub博客

PostgreSQL 并发控制机制（4）：RR隔离级别，MySQL vs PostgreSQL

最新文章