并发控制是多个事务在并发运行时，数据库保证事务一致性（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) mysql> show variables like '%isolation%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 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