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

PostgreSQL中文社区 2020-11-23

1168

作者简介

何小栋，从事产品研发和架构设计工作，对PostgreSQL数据库及其源代码有深入研究。现就职于广州海量数据库技术有限公司，系统架构师。

校对者简介

崔鹏,任职于海能达通信股份有限公司,数据库开发高级工程师,致力于postgresql数据库在专网通信领域、公共安全领域的应用与推广。

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

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

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

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

-- T1
[local]:5432 postgres@testdb=#* commit;
COMMITTime: 3.241 ms

-- T2
[local]:5432 postgres@testdb=#* update t1 set id = 11 where id = 5;ERROR:  could not serialize access due to concurrent updateTime: 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 started0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421821791988288, not started0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421821791987432, not started0 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 sec4 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

扫二维码｜报名参与

2020 PostgreSQL第十届

中国技术大会

PostgreSQL中文社区欢迎广大技术人员投稿
投稿邮箱：press@postgres.cn

mysql postgresql

文章转载自PostgreSQL中文社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

一、PostgreSQL RR Isolation Level

二、MySQL RR Isolation Level

三、参考资料

评论