政采云技术团队.png

火花.png

一.MVCC

为什么需要MVCC

MySQL5.5 版本之后默认为 InnoDB 引擎,引入了行级锁，并发事务处理能力大大提高数据库资源的利用率，同时带来了脏读、不可重复读、幻读等问题。

图二十四.png

MySQL 默认数据隔离级别为 RR，主要是通过 MVCC 与间隙锁解决了幻读的问题，MVCC 多版本并发控制是数据库引擎实现中常用的处理读写冲突的手段，主要的目的是提高数据库的并发性能，从规避锁的角度去避免读写冲突，MVCC 主要实现是通过 undo log 版本链与 Readview 组成。

undolog版本链

是指一条由不同或者相同的事务，对一条数据进行修改，根据事务的提交时间形成的链表结构。头部是最新的记录，尾部是最早的记录。

数据库中有两个默认的隐藏字段，db_trx_id 记录最近更新这条行记录的事务ID，占用 6 个字节，db_roll_ptr 指向该行回滚段的指针，占用 7 个字节。在多个事务并行操作数据的情况下，不同事务对该行数据的操作会产生多个版本。

图二十五.png

Readview

Readview 中包含以下几个字段：

m_ids 当前活跃的事务 id 集合
min_trx_id 最小活跃事务 id
max_trx_id 预分配事务 id，当前最大事务 id 自增 1
creator_trx_id 快照读创建者的事务 id

在 RU 的隔离级别下，select 都是当前读，在序列化的隔离级别下是通过表锁来限制数据，不需要 MVCC 的帮助

因此 MVCC 运行在 RC 和 RR 这两个隔离级别下。

在 RR 的隔离级别下，每个事务 read 时，会将当前系统中所有的活跃事务拷贝到一个列表中生成 ReadView，后续所有的 select 都是复用这个 ReadView。

在 RC 的隔离级别下，每一次 select 都会重新将系统中所有活跃的事务拷贝到每一个列表中生成 ReadView。

图十七.png

图十八.png

图十九.png

数据库锁

MySQL 中的锁按粒度可以分为全局锁、表锁、行锁。

全局锁

全局锁就是对整个数据库实例加锁，加锁之后整个实例处于只读，DDL、DML 无法执行，未提交的事务也会阻塞

一般用于数据库的逻辑备份。

添加全局锁

flush tables with read lock

释放全局锁

unlock tables

表级锁

1.表

表共享读锁（ read lock ）

加锁后所有 DDL / DML 语句会阻塞，多个事务可以执行 DQL

lock tables xxx read;

表独写锁（ write lock ）

加锁之后只有当前事务可以执行 DQL、DDL、DML

lock tables xxx write;

释放锁

 unlock tables;

2.元数据锁（ Meta Data Lock ）

MySQL 中元数据锁是系统自动控制添加的，对于用户来说无需显示调用，当我们使用一张表的时候就会加上原数据锁。元数据锁的作用是为了保护表原数据的一致性，如果在表上有活动事务的时候，不可以对元数据进行写入操作。也就是为了避免 DML 和 DDL 之间的冲突，保证读写的正确性。

在执行 select 操作的时候，MySQL 会自动加上 SHARED_READ 锁，在 insert、update、delete 操作的时候会加上 SHARED_WRITE 锁，在 alter 操作的时候会加上 EXCLUSIVE 锁，SHARED_READ 与 SHARED_WRITE 兼容，与SHARED_READ / SHARED_WRITE 与 EXCLUSIVE 互斥。

select * from test_table';

图二十.png

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

可以看到此时表 test_table 已经加了 SHARED_READ 锁

图二十一.png

alter table test_table add meta_data_lock varchar(32) comment '元数据锁测试字段';

此时 DDL 语句被阻塞了无法执行

图二十二.png

再次查看数据库加锁情况

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

可以看到 SHARED_READ 与 EXCLUSIVE 互斥了

图二十三.png

3.意向锁

避免 DML 在执行时，加的行锁与表锁冲突，减少数据库表锁的检查步骤，在 InnoDB 中引入了意向锁

意向共享锁（IS）

与表共享锁兼容，与表独写锁互斥。

select.....lock in share mode

意向排他锁（IX）

与表共享锁及表读写锁都互斥。

 ....for update

行级锁

每次的操作锁住对应的行数据

InnoDB 的数据是基于索引的，行锁是通过对索引上的索引项加锁来实现的，而不是通过对数据进行加锁

1.行锁（S/X,REC_NOT_GAP）

针对单个行记录，防止其他事务进行 update 和 delete，在 RC RR 下支持，当非唯一索引字段时，行锁会升级为表锁

共享锁（S）

允许一个事务读一行，防止其他事务获得相同数据的排他锁

排他锁（X）

允许获取排他锁的事务更新数据，防止其他事务获取相同数据的排他锁和共享锁

共享锁之间兼容，排他锁之间不兼容，共享锁与排他锁之间不兼容

SELECT * from test_table where id = 1 lock in share mode;

图一.png

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

可以看到数据库存在两条锁，第一条是表意向共享锁，第二条是对主键 id 为 1 的数据加了一条行锁

图二.png

2.间隙锁（S/X,GAP）/临键锁(S/X)

间隙锁( Gap Loack ):锁定索引记录间隙(不包含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙 insert，产生幻读，在 RR 隔离级别下支持，

间隙锁的目的是防止其他事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

临键锁( Next-key Lock ):行锁和间隙锁组合，同时锁住数据，并锁住数据前的间隙 Gap，在 RR 隔离级别下支持

间隙锁/临键锁加锁规则：

1.唯一索引上的等值查询，给不存在的记录加锁时，优化为间隙锁。

数据库现有数据如下

图三.png

对不存在主键为 7 的数据进行修改

update test_table set field_three ='加锁规则一' where id =7;'

查询数据库的锁，可以看到已经加上了间隙锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data
from performance_schema.data_locks;

图四.png

这条间隙锁锁住的是主键 id 6-10 之间的数据，不包括这两条数据本身

图五.png

此时在主键 id 为 10 的数据之前插入一条 id 为 8 的数据，可以看到已经阻塞了

INSERT INTO test_table (id,field_one, field_two, field_three) VALUES (8,1, 0, '插入间隙锁之间的数据');

图六.png

验证对 id 为 10 的数据进行修改，可以看到间隙锁并没有锁住，数据更改成功

update test_table set field_three='测试间隙锁的范围' where id =10;

图七.png

2.普通索引上的等值查询，向右遍历最后一个值不满足查询需求时，临键锁会退化为间隙锁，对于非唯一索引，由于字段可以重复，所以不仅仅会有间隙锁还有临键锁，为了防止幻读。

查询索引可以看到 field_two 为普通索引

图八.png

对这个字段进行一个等值查询

select * from test_table where field_two = 5 lock in share mode;

图九.png

观察数据库的加锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

从 2-4 行可以观察到对在索引 field_two 下，对数据库对主键 id 为 3，4，6 的数据加上了临键锁，5-7 行同时对主键id为 3，4，6 的数据加上了行锁，第八行表示对主键 id 为 10 的数据加上了间隙锁

图十.png

此时再次插入一条主键 id 为 8 的数据, field_two 为 8 的数据进行验证，可以看到被阻塞了

INSERT INTO test_table (id,field_one, field_two, field_three) VALUES(8,1, 8, '插入数据验证');

图十一.png

上文中为什么要说在索引 field_two 下对数据进行加锁，因为 InnoDB 索引下，行锁不是加在数据上，而是加在索引上，如下图所示

图十二.png

图十三.png

此时插入主键 id 为 8，field_two 为 11 的数据进行验证，可以看到插入数据成功，说明行锁没有根据id去锁定数据

INSERT INTO test_table (id,field_one, field_two, field_three) VALUES(8,1, 2, '插入数据验证');

图十四.png

3.唯一索引上的范围查询会访问到不满足条件的第一个值为止

查询主键 id 大于等于 6 的数据

select * from test_table where id>=6 lock in share mode;

图十五.png

观察数据库的加锁情况，可以看到对主键 id 为 6 的数据加上了行锁，对主键 id 为 10 的数据加上了临键锁，同时对 supremum pseudo-record 正无穷加上了临键锁，避免数据的插入。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

图十六.png

总结：

MySQL 是我们开发过程中经常使用到的数据库，使用的过程中会产生必不可少的疑问，通过这篇文章相信你也进一步了解了 MySQL 如何在不同的隔离级别下做到数据之间的隔离，如何去规避幻读产生的问题，同时通过对索引的合理使用，也可以去减少锁冲突的发生概率。

参考资料：

《MySQL数据库原理及应用》
《高性能MySQL》

招贤纳士

政采云技术团队（Zero），一个富有激情、创造力和执行力的团队，Base 在风景如画的杭州。团队现有 500 多名研发小伙伴，既有来自阿里、华为、网易的“老”兵，也有来自浙大、中科大、杭电等校的新人。团队在日常业务开发之外，还分别在云原生、区块链、人工智能、低代码平台、中间件、大数据、物料体系、工程平台、性能体验、可视化等领域进行技术探索和实践，推动并落地了一系列的内部技术产品，持续探索技术的新边界。此外，团队还纷纷投身社区建设，目前已经是 google flutter、scikit-learn、Apache Dubbo、Apache Rocketmq、Apache Pulsar、CNCF Dapr、Apache DolphinScheduler、alibaba Seata 等众多优秀开源社区的贡献者。如果你想改变一直被事折腾，希望开始折腾事；如果你想改变一直被告诫需要多些想法，却无从破局；如果你想改变你有能力去做成那个结果，却不需要你；如果你想改变你想做成的事需要一个团队去支撑，但没你带人的位置；如果你想改变本来悟性不错，但总是有那一层窗户纸的模糊……如果你相信相信的力量，相信平凡人能成就非凡事，相信能遇到更好的自己。如果你希望参与到随着业务腾飞的过程，亲手推动一个有着深入的业务理解、完善的技术体系、技术创造价值、影响力外溢的技术团队的成长过程，我觉得我们该聊聊。任何时间，等着你写点什么，发给 zcy-tc@cai-inc.com

微信公众号

文章同步发布，政采云技术团队公众号，欢迎关注