​作者简介

何小栋,从事产品研发和架构设计工作,对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,参见下面的例子:

CENTER_PostgreSQL_Community

执行输出如下:

-- 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执行顺序:

CENTER_PostgreSQL_Community

在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

执行顺序:

CENTER_PostgreSQL_Community

执行结果是:

...-- 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

CENTER_PostgreSQL_Community

请在登录后发表评论,否则无法保存。
1楼 xcvxcvsdf
2024-10-24 12:23:02+08

https://jj.tiancebbs.cn/qths/472905.html https://www.tiancebbs.cn/ershoufang/473553.html https://heyuan.tiancebbs.cn/qths/460003.html https://huangnan.tiancebbs.cn/qths/460246.html https://changshushi.tiancebbs.cn/hjzl/464059.html https://www.tiancebbs.cn/ershoufang/468373.html https://mdj.tiancebbs.cn/qths/466423.html https://cy.tiancebbs.cn/qths/450815.html https://wh.tiancebbs.cn/qths/452833.html https://aihuishou.tiancebbs.cn/sh/810.html https://www.tiancebbs.cn/qtfwxx/473292.html https://aihuishou.tiancebbs.cn/sh/1601.html https://www.tiancebbs.cn/ershoufang/473929.html https://aihuishou.tiancebbs.cn/sh/3774.html https://www.tiancebbs.cn/qtfwxx/474180.html https://aihuishou.tiancebbs.cn/sh/1039.html https://aihuishou.tiancebbs.cn/sh/4478.html

2楼 xiaowu
2024-04-24 10:50:17+08

贫困生证明:https://www.nanss.com/xuexi/18624.html 你比划我猜题目大全:https://www.nanss.com/shenghuo/20557.html 看淡一切释然的句子:https://www.nanss.com/yulu/20274.html 现实表现证明:https://www.nanss.com/gongzuo/20301.html 飞机每小时飞行多少千米:https://www.nanss.com/shenghuo/18943.html 幼儿园班务工作总结:https://www.nanss.com/gongzuo/20479.html 春节值班总结:https://www.nanss.com/gongzuo/20224.html 补录一般多少分有希望:https://www.nanss.com/wenti/20155.html 好听的店铺名字:https://www.nanss.com/mingcheng/20577.html 文员的工作内容:https://www.nanss.com/xuexi/20719.html 芙蓉花语:https://www.nanss.com/shenghuo/18523.html 回族的习俗:https://www.nanss.com/shenghuo/19400.html 上诉状格式:https://www.nanss.com/gongzuo/20131.html 班主任职责:https://www.nanss.com/gongzuo/19089.html 菊花代表什么:https://www.nanss.com/wenti/19024.html 顾左右而言其他:https://www.nanss.com/xuexi/20615.html 政协提案答复:https://www.nanss.com/gongzuo/zhuanti/20724.html 防暑降温:https://www.nanss.com/shenghuo/19119.html 红星照耀中国读书笔记:https://www.nanss.com/xuexi/19677.html 适合农村的代加工厂:https://www.nanss.com/shenghuo/20688.html 励志电影排行榜前十名:https://www.nanss.com/shenghuo/20563.html 年终总结模板:https://www.nanss.com/gongzuo/20361.html 水的沸点:https://www.nanss.com/shenghuo/20212.html 东盟国家有哪些:https://www.nanss.com/wenti/18273.html 青春的活力:https://www.nanss.com/xuexi/xiezuo/20733.html 出纳工作内容:https://www.nanss.com/gongzuo/18422.html 可爱的中国读后感:https://www.nanss.com/xuexi/20256.html 自我介绍英语:https://www.nanss.com/xuexi/20368.html 摆地摊卖什么最赚钱而且很受欢迎:https://www.nanss.com/wenti/20700.html 古尔邦节的习俗:https://www.nanss.com/shenghuo/18270.html

© 2010 PostgreSQL中文社区