MySQL深度剖析之事务隔离级别和锁机制(2021)

一 事务隔离级别和锁机制

1.1 多个事务并发修改同一条数据或者对同一条数据并发读写存在哪些事务并发问题

1.1.1 脏读(未提交读)

A事务读取了别的事务还未提交的更新，而B事务是有可能回滚的。

1.1.2 虚读(不可重复读)

A事务读取了别的事务提交的更新，导致A事务范围内多次查询，返回的结果不一样。一般不是什么大问题，取决于你的业务是否允许在同一个事务内每次查询结果不一样，如果允许，就没啥事，如果不允许则就有问题了。

1.1.3 幻读

A事务读取了别的事务新增的数据，导致事务内每次查询结果数量不一样，可能比之前的多一些输出来。

1.1.4 第一类丢失更新(回滚了别人提交的事务)

A事务先发起，B事务后发起，修改同一行数据，B事务先提交，A事务回滚，就会回滚到A事务之前状态，那么就把提交的B事务的值也给回滚了。

1.1.5 第二类就是更新(覆盖了别人提交的事务)

A事务先发起，B事务后发起，修改同一行数据，B事务先提交，A事务后提交，因为修改金额不一样，导致A事务的结果可能会出错，如图示：

1.2 事务隔离级别

1.2.1 未提交读(read uncommitted)

允许读取别的事务未提交的更新或者提交的更新，即允许发生脏读、不可重复读(虚读)和幻读，一般数据库不会设置这个隔离级别

1.2.2 提交读(read committed)

简称RC, 允许读取别人提交的事务，即不允许发生脏读，但是可能发生不可重复读(虚读)或者是幻读

1.2.3 可重复读(repeatable read)

简称RR，只能允许每一个事务在查询的时候，查询的内容都是一样的，不存在脏读、不可重复读和幻读，生产环境一般使用这种。

1.2.4 串行读(serializable)

不允许多事务并发执行，肯定不会用

1.3 undo log版本链

1.3.1 每一个记录的隐藏字段

DB_TRX_ID, 6byte, 创建这条记录/最后一次更新这条记录的事务ID

DB_ROLL_PTR, 7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

DB_ROW_ID, 6byte，隐含的自增ID，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

1.3.2 undo log版本链

DB_ROLL_PTR,我们知道是指向这一条记录的上一个版本，即上一个undo log，那么undo log通过DB_ROLL_PTR串联起来形成一个undo log版本链，如图所示：

 

1.4 什么是mvcc？怎么实现提交读和可重复读的?

1.4.1 mvcc是什么？ReadView是什么? 有什么作用呢?

mvcc：是multi version concurrent control，多版本并发控制的缩写，在很多中间件都有这个机制，主要用来控制并发。在MySQL中主要是通过read view和undo log版本链实现。

ReadView: 当MySQL执行事务的时候，获取当前MySQL实例的所有活跃的事务，它有以下几个重要字段：

#1 trx_ids: 当前MySQL有哪些正在活跃的事务

#2 up_limit_id: 正在进行事务中最小的事务ID

#3 low_trx_id: MySQL下一个要生成的事务，即在当前最大的事务ID基础上递增

#4 creator_trx_id: 当前事务ID

MySQL可以基于ReadView查询出在当前事务之前就已经提交的数据更新，怎么实现的呢？

之前已经提交的记录：

此时，来了2个事务，事务A(id=35)和事务B(id=45)事务B有操作更新数据，事务A有操作要读这个数据：

 

 

 

1.4.2 怎么基于ReadView实现read committed提交读

实现原理：当你一个事务设置RC(提交读)的时候，在事务内每一次查询都会重新生成一个ReadView，这样每次就可以查询到已经提交的事务的数据更新

来了2个事务，事务A(id=35)和事务B(45)，事务A有操作要读这个数据，事务B要修改：

B事务修改数据并提交

 

C事务在B事务完成后修改数据，但是还未提交，此时A事务发起查询，然后C事务进行提交

 

 

上面就可以看出，只要设置了RC隔离级别，则每次事务内查询产生一个ReadView都可以读到别的事务新增或者提交的数据，实现了RC隔离机制

1.4.3 怎么基于ReadView实现repeatable read 可重复读

实现原理：当前事务内，在第一次读取的时候会产生一个ReadView，以后无论读取多少次，都不产生新的ReadView，即视图在事务内始终是第一次读取是产生的ReadView的情况。

 

 

二 MySQL锁专题

在并发中，有时候为了控制多线程安全的访问表或者行，需要对表或者行加锁，在MySQL中锁主要分为全局锁、表级锁和行级锁。

2.1 全局锁(Global Lock)

MySQL 有一种锁，可以锁定数据库实例，叫做全局锁。但是一般很少使用，比如flush tables with read lock;

2.2 表锁

2.2.1 表数据锁

顾名思义，表锁就是锁定整个表的，对于不同的存储引擎，可能不一样。比如MyISAM引擎，更新记录行的时候，就是锁表，所以MyISAM并发写的性能差；

但对于InnoDB存储引擎，锁表的场景或者触发时机有所不同。比如主动锁表，lock tables 表名 read/write，然后unlock tables；或者当对MySQL中对没有索引的字段更新，会触发全表锁。

2.2.2 元数据锁(Metadata Lock)

元数据指的是表的元数据，MySQL会隐式的给表元数据加锁，比如DDL操作，阻塞其他增删改操作。分为四种类型：

MDL_SHARED_READ: 元数据共享读锁，可以访问表结构和读数据，但是不能写,比如SELECT语句

MDL_SHARED_WRITE: 元数据共享写锁，可以访问表结构和读写数据，可以进行DML

MDL_SHARED_NO_WRITE: 当前事务可以进行表结构访问和读写数据，但是不允许其他事务写，比如alter table操作

MDL_EXCLUSIVE: 元数据共独占锁，防止其他线程读写元数据,比如CREATE/DROP/RENAME TABLE等

有时候我们一些小表数据也不大，但是在并发量大的时候，进行DDL操作也有可能导致整个库挂了。如图示：

所以在并发量小的时候给小表加字段看以避免这个问题。

2.3 行锁

以上数据id是主键，聚簇索引；card是二级索引(非聚簇索引)

我们知道InnoDB每一个表都有聚簇索引：

#1 如果自己定义了主键，则主键就是聚簇索引

#2 如果没有定义主键，则表中第一个字段是非空且唯一的列，则是聚簇索引

#3 如果第一列不是非空且唯一则使用隐藏的rowid作为聚簇索引

聚簇所以和非聚簇索引区别：

聚簇索引：InnoDB索引页和数据页是存储在一起的，在一棵B+树上，但是MyISAM是分开存储的

非聚簇索引：比如二级索引，在单独的一个B+树，存储主键和索引字段，通过普通索引字段找到了的对应的主键，然后在通过主键回表查询。

MySQL行锁分为三种类型：记录锁、间隙锁和临键锁。在RR隔离级别下，默认使用临键锁，根据不同的场景，可以退化为记录锁和间隙锁。

2.3.1 记录锁(record lock)

MySQL 对唯一索引的单个记录查询，即等值查询会添加记录锁，只锁住单行记录。比如：

select * from student where id = 10 for update;

但是，记录锁，只能锁住已经存在的记录，无法锁住不存在记录，所以可以产生幻读，而间隙锁就解决了这个问题。

2.3.2 间隙锁(gap lock)

间隙是什么呢，就是2个值之间的空隙，如图示：

如果 id = 10, 那么范围就是(5,10]和(10,15);如果 id = 32, 则属于(30,50)这个间隙范围。间隙锁可以锁住一个范围区间，防止幻读情况。

2.3.3 临键锁(next-key lock)

临键锁是是间隙锁和记录锁的合并，前开后闭，也是MySQL默认使用的行锁。如图示：

 

2.3.4 MySQL 行锁加锁规则

原则1：加锁是根据next-key lock 临键锁加锁

原则2：查找过程中访问的对象才会被加锁

优化1：基于唯一索引的等值查询，next-key lock 临键锁退化成记录锁

优化2：基于索引的等值查询，无论是唯一还是普通索引，向右遍历不等于查询条件，则临键锁退化成间隙锁。

2.3.4.1索引等值查询(值存在)

情况一：唯一索引

select * from role where id = 10;

如果是id = 10, 则临键锁范围(5,10], 又因为是唯一索引等值查询，根据优化1所以临键锁退化为记录锁，只会锁住id = 10这一行,而不是6,7,8,9,10五行，减少被锁的记录。

情况二：普通索引

假设普通索引的值和id一样。

如果是card = 10, 则临键锁范围(5,10], 注意，这是普通索引，完全有可能右边还有card = 10的值，所以需要继续向右查询，右边临键锁范围(10,15]；根据原则2访问到的都要加锁，即(5,15]都要加锁；但是根据优化2，需要向右判断是否card = 10, 因为最后一个是15，所以需要退化成间隙锁，即加锁范围（5,15）

2.3.4.2 唯一索引等值查询(值不存在)

情况一：唯一索引

这是一个等值查询，id =23 不等于30，不满足查询条件，根据原则2，next-key lock 退化成间隙锁 (20,30), 因此最终加锁范围就是(20,30)。id = 30的记录就不会被锁

情况二：普通索引

假设普通索引的值和id一样。

#1 如果是card = 12, 则临键锁范围(10,15]

#2 注意，虽然是普通索引，但是因为12不存在，所以不需要继续向右查询

#3 但是根据优化2，因为card =12,不等于锁范围右边最大值15，所以临键锁需要退化成间隙锁(10,15)

2.3.4.3 索引范围查询

情况一：唯一索引

select * from id >= 10 and id < 11 for update;

#1 id = 10是等值查询，范围是(5,10]，然后 id > 10 和 id < 11则临键锁范围是(10,15]，

#2 根据优化1，如果是等值查询，则退化为记录锁，锁定记录只有id=10这一行，#2 因为是范围查询，不是等值查询，所以(10,15],不会退化为间隙锁(10,15), 因此

[10]和(10,15] 最后锁定范围是[10,15]

情况二：普通索引

select * from student where card >= 15 and card < 19 for update;

分析：

#1 会话A发起查询, 因为card>=15, 那么临键锁范围是(10,15], 因为不是主键等值查询，所以不必退化成记录锁；然后右遍历，锁住(15,18];然后锁住(18,19],所以整个锁住区间是(10,19];

#2 因为加锁范围是(15,19] ，所以会话B更新card=19的记录是会被阻塞的；更新card=20的记录是不会被阻塞的

#3 因为加锁范围是(15,19] ，所以会话C更新添加记录也是会被阻塞的

2.4 死锁

2.4.1 什么是死锁

在同一时刻T1, 事务A执行update t1 set age = 26 where id = 1001; 获取id=1001的行锁；事务B执行update t1 set age = 25 where id = 1002;获取id=1002行锁

我们知道锁的释放是需要事务提交才可以释放锁，所以目前事务A因为饿都还没有执行完，所以不会提交事务，所以不会释放锁。

当事务A执行update t1 set name = 'nicky' where id = 1002;等待获取锁，事务B执行update t1 set name = 'bell' where id = 1001;等待获取锁，但是又都没释放锁，所以造成互相等待对方释放锁

2.4.2 如何定位死锁

#1 通过应用业务日志定位到问题代码，找到相应的事务对应的sql；

#2 确定数据库隔离级别。执行select @@global.tx_isolation，可以确定数据库的隔离级别，我们数据库的隔离级别是RC，这样可以很大概率排除gap锁造成死锁的嫌疑;

#3 找DBA执行下show engine innodb status 看看最近死锁的日志。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定

合理设计索引。不经常使用的列最好不加锁

尽可能减少基于范围的数据检索过滤条件

2.4.3 如何避免死锁

#1 尽量使用主键更新，防止使用非聚簇索引更新

因为非聚簇索引，不会退化成行锁，所以可能会锁住一个区间，从而增加的死锁风险

#2 避免长事务

如果一个事务太长，锁持有的时间就长，如果在高并发的时候，发生死锁风险增大

#3 尽可能减少基于范围的数据检索过滤条件

范围越大，被锁住的记录越多，从而发生死锁概率越大

#4 如果允许读取别人已提交更新数据或者已提交新增数据，则隔离级别间使用RC，降低间隙锁造成的死锁

2.5 共享锁和排它锁

2.5.1 排它锁

排它锁就是互斥锁，也就是E锁，当加了互斥锁之后，其他事务就无法再加锁了，共享锁和互斥锁都不行。

2.5.2 共享锁

当我们一个事务在更新的时候，加了独占锁，其他事务查询的时候可以查询吗？肯定是可以的，因为MVCC机制，在查询的时候，可以获取当前MySQL实例的视图，隔离级别是RR，只会读取当前比当前事务小的已经提交的数据，这样可以避免频繁加锁互斥。

如果你就是在查询的时候一定要加锁呢？MySQL也是支持的，MySQL提供了共享锁，就是S锁，在查询语句后面添加lock in share mode。当你加了共享锁，别人也加了共享锁，这个是允许的，因为共享锁对读共享；但是对读写或者写读是互斥 的，比如事务A加了共享锁，事务B想更新，是无法加互斥锁的；又或者A事务加了互斥锁，但是事务B想查询，但是无法加共享锁，因为是互斥的。