MySQL事务原理之MVCC和锁机制

一、MVCC

MVCC全称一致性非锁定读。MVCC是多版本并发控制；用来实现一致性的非锁定读；非锁定读是指不需要等待访问的行上X锁的释放。

在 read committed 和 repeatable read 下，innodb 使用MVCC。但是它们对于快照数据的定义不同：
（1）在 read committed 隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据。
（2）而在 repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本。

思考：为什么读取快照数据不需要上锁？
因为没有事务需要对历史的数据进行修改操作。

1.1、read view

在 read committed 和 read repeatable 隔离级别下，MVCC 采用read view 来实现的，它们的区别在于创建 read view 时机不同：

（1）read committed 隔离级别会在事务中每个 select 都会生成一个新的 read view，也意味着在同一个事务多次读取同一条数据可能出现数据不一致；因为在多次读取期间可能有其他事务修改了该条记录，并提交了。
（2）read repeatable 隔离级别是启动事务时生成一个 read view，在整个事务读取数据都使用这个 read view，这样保证了在事务期间读到的数据都是事务启动前的记录。

review的构成：

（1）m_ids：创建 read view 时，当前数据库活跃事务（开启未提交的事务）的事务 id 列表。
（2）min_trx_id：创建 read view 时，m_ids 中的最小事务 id。
（3）max_trx_id：创建 read view 时，当前数据库将为下一个事务分配的事务 id；并不一定是 m_ids 中的最大事务 id。
（4）creator_trx_id：创建 read view 所在事务的 id。

1.2、聚集索引的隐藏列

（1）trx_id：当某个事务对某条聚集索引记录进行修改时，将会把当前事务的 id 赋值给 trx_id。
（2）roll_pointer：当某个事务对某条聚集索引记录进行修改时，会将上一个版本的记录写到 undo log，然后通过roll_pointer 指向旧版本记录，通过它可以找到修改前的记录。

1.3、事务的可见性问题

事务的状态有：
（1）已提交的事务。
（2）已启动未提交的事务。
（3）还没开始的事务。

事务间的可见性：
（1）trx_id < min_trx_id；说明该记录在创建 read_view 之前已提交，所以对当前事务可见。
（2）trx_id >= max_trx_id；说明该记录是在创建 read_view 之后启动事务生成的，所以对当前事务不可见。
（3）min_trx_id <= trx_id < max_trx_id；此时需要判断是否在 m_ids 列表中：

1. 在列表中。生成该版本记录的事务仍处于活跃状态，该版本记录对当前事务不可见。
2. 不在列表中。生成该版本记录的事务已经提交，该版本记录对当前事务可见。

1.5、快照读

select 后面没有加for update或lock in share mode的就是快照读。
比如：

select * from table where ......

快照读根据read view的roll pointer去读取历史版本信息。

1.6、当前读

出现当前读的情况：

-- 1
select * from table where ... lock in share mode;

-- 2
select * from table where ... for update;

-- 3
insert into table values(...);

-- 4

update table set ?=? where ...;

-- 5

delete from table where ...;

二、redolog

redolog用来实现事务的持久性。 内存中包含 redolog buffer，磁盘中包含 redolog file。

当事务提交时，必须先将该事务的所有日志写入到redolog文件进行持久化，待事务的commit 操作完成才完成了事务的提交。

redo log 顺序写，记录的是对每个页的修改（页、页偏移量、以及修改的内容）。在数据库运行时不需要对 redo log 的文件进行读取操作；只有发生宕机的时候，才会拿redo log 进行恢复。

三、undolog

undo 日志用来帮助事务回滚以及 MVCC 的功能。 存储在共享表空间中。

undo 是逻辑日志，回滚时将数据库逻辑地恢复到原来的样子，根据 undo log 的记录，做之前的逆运算。
比如事务中有 insert 操作，那么执行 delete 操作；对于 update 操作执行相反的 update 操作。

同时 undo 日志记录行的版本信息，用于处理 MVCC 功能。

四、锁 机制

锁机制用于管理对共享资源的并发访问；用来实现事务的隔离级别 。

4.1、锁类型

共享锁和排他锁都是行级锁；MySQL当中事务采用的是粒度锁。
针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁。意向共享锁和意向排他锁都是表级别的锁。

（1）全局锁：锁住数据库，用于全库备份。使用 flush tables with read lock命令使整个数据库处于只读状态；使用unlock tables释放锁。

（2）表级锁：把某张表锁定；表级锁又分为表锁、元数据锁、意向锁、auto-inc锁。
表锁主要锁定一张表，通过lock tables table [read/write] 限制表的读写权限，一旦加锁，本身session也同样没有了表的相关权限，这和行级锁有区别；通过unlock tables释放锁。

元数据锁（又称MDL）的作用是当对数据修改的时候，其他的连接不能修改表的结构。

意向锁目的是快速判断表里是否又记录（或行数据）加锁。当某条记录被加锁时，会同时给表做一个标志，指示表中有记录被加锁，这就是意向锁，可以避免全表扫描查询是否有记录被加锁了。

auto-inc锁是特殊表锁，实现自增约束 ，当往表插入数据时会使用auto-inc锁来加锁，语句结束后释放锁，而不是在事务结束时释放（这和行级锁有区别，行级锁是在事务结束才释放锁）。auto-inc锁有三个模式（0、1、2），0是在语句执行结束后才释放锁（语句级别）；1普通insert语句，自增锁在申请之后就马上释放，而 类似insert …select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放（自适应）；2是所有的申请自增主键的动作都是申请后就释放锁（轻量级）；innodb默认的auto-inc锁模式是2。

（3）行级锁：锁定记录（或称为行，笔者习惯将一行称为记录）。有记录锁（record lock）、间隙锁（gap lock）、临键锁（next-key lock）。
记录锁又有S锁（共享锁或读锁）和X锁（排他锁或独占锁）。
间隙锁只有在repeated read隔离级别使用，用于防止其他事务在记录间插入新的记录，从而避免幻读现象。间隙锁主要是锁范围，是不包含记录本身，是全开区间。
临键锁用于锁范围和记录，包含记录本身是左开右闭区间。

4.1.1、共享锁（S）

事务读操作加的锁；对某一行加锁。
（1）在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁。
（2）在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题。
（3）在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC。
（4）在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用MVCC。

4.1.2、排他锁（X）

事务删除或更新加的锁；对某一行加锁。
在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁。

4.1.3、意向共享锁（IS）

对一张表中某几行加的共享锁。

4.1.4、意向排他锁（IX）

对一张表中某几行加的排他锁。
目的：为了告诉其他事务，此时这条表被一个事务在访问。
作用：排除表级别读写锁 （全面扫描加锁）。

4.1.5、锁的兼容性

锁	S	X	IS	IX	AI
S	兼容	冲突	兼容	冲突	冲突
X	冲突	冲突	冲突	冲突	冲突
IS	兼容	冲突	兼容	兼容	兼容
IX	冲突	冲突	兼容	兼容	兼容
AI	冲突	冲突	兼容	兼容	冲突

由于 innodb 支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求。
（1）意向锁之间是互相兼容的。
（2）IS 只对排他锁不兼容。
（3）当想为某一行添加 S 锁，先自动为所在的页和表添加意向锁IS，再为该行添加 S 锁。
（4）当想为某一行添加 X 锁，先自动为所在的页和表添加意向锁IX，再为该行添加 X 锁。
（5）当事务试图读或写某一条记录时，会先在表上加上意向锁，然后才在要操作的记录上加上读锁或写锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

4.2、锁算法

（1）Record Lock，记录锁，单个行记录上的锁。
（2）Gap Lock，间隙锁，锁定一个范围，但不包含记录本身；全开区间。
REPEATABLE READ 级别及以上支持间隙锁；如果 REPEATABLE READ 修改innodb_locks_unsafe_for_binlog = 0，那么隔离级别相当于退化为 READ COMMITTED。

-- 查看是否支持间隙锁，默认支持，也就是 innodb_locks_unsafe_for_binlog = 0;
SELECT @@innodb_locks_unsafe_for_binlog;

（3）Next-Key Lock，记录锁+间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间。
（4）Insert Intention Lock，插入意向锁，insert 操作的时候产生。
在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不
会发生锁等待。

假设有一个记录索引包含键值 4 和 7，两个不同的事务分别插入5 和 6，每个事务都会产生一个加在 4-7 之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

（5）AUTO-INC Lock（AI锁），自增锁，是一种特殊的表级锁，发生在AUTO_INCREMENT 约束下的插入操作；采用的一种特殊的表锁机制（较低概率造成 B+树分裂）。完成对自增长值插入的 SQL 语句后立即释放。
在大数据量的插入会影响插入性能，因为另一个事务中的插入会被阻塞；从MySQL 5.1.22 开始提供一种轻量级互斥量的自增长实
现机制，该机制提高了自增长值插入的性能。

4.3、锁兼容

锁	GAP（持有）	Insert Intention（持有）	Record（持有）	Next-key（持有）
GAP（请求）	兼容	兼容	兼容	兼容
Insert Intention（请求）	冲突	兼容	兼容	冲突
Record（请求）	兼容	兼容冲突	冲突
Next-key（请求）	兼容	兼容	冲突	冲突

横向：表示已经持有的锁；
纵向：表示正在请求的锁；

（1）一个事务已经获取了插入意向锁，对其他事务是没有任何影响的。
（2）一个事务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞；这个是重点，死锁之源。

4.4、关于锁的讨论

行级锁是针对表的索引加锁，索引包括聚集索引和辅助索引。
表级锁是针对页或表进行加锁。

重点讨论 InnoDB 在 read committed 和 repeatable read 级别下锁的情况。

假设存在如下的students 表作为实例，其中 id 为主键，no（学号）为辅助唯一索引，name（姓名）和 age（年龄）为二级非唯一索引，score（学分）无索引。

id	no	name	age	score
15	S0001	Bob	25	34
18	S0002	Alice	24	77
20	S0003	Jim	24	5
30	S0004	Eric	23	91
37	S0005	Tom	22	22
49	S0006	Tom	25	83
50	S0007	Rose	23	89

（1）聚集索引，查询命中：

UPDATE students SET score = 100 WHERE id = 15;

都给记录加排他锁。

（2）聚集索引，查询未命中：

UPDATE students SET score = 100 WHERE id = 16;

READ COMMITED 隔离级别不会加任何锁，REPEATABLE READ隔离级别会在未命中索引的前一个索引和后一个索引之间加一个gap 锁。

（3）辅助唯一索引，查询命中：

UPDATE students SET score =100 WHERE no = 'S0003';

先通过辅助索引找到聚集索引，然后查询聚集索引B+树。都给辅助索引和聚集索引的记录加排他锁。

（4）辅助唯一索引，查询未命中：

UPDATE students SET score =100 WHERE no = 'S0008';

READ COMMITED 隔离级别不会加任何锁，REPEATABLE READ隔离级别会在未命中索引的前一个索引和后一个索引之间加一个gap 锁。

（5）辅助非唯一索引，查询命中：

UPDATE students SET score =100 WHERE name = 'Tom';

先通过辅助索引找到聚集索引，然后查询聚集索引B+树。READ COMMITED 隔离级别都给辅助索引和聚集索引的记录加排他锁；REPEATABLE READ隔离级别除了都给辅助索引和聚集索引的记录加排他锁，还会在命中索引的前、后、间隙间加gap 锁（如图所示）。

（6）辅助非唯一索引，查询未命中：

UPDATE students SET score= 100 WHERE name = 'John';

READ COMMITED 隔离级别不会加任何锁，REPEATABLE READ隔离级别会根据ASCII码在未命中索引的前一个索引和后一个索引之间加一个gap 锁。

（7）无索引：

UPDATE students SET score = 100 WHERE score= 22;

READ COMMITED 隔离级别会全表加排他锁进行扫描；REPEATABLE READ隔离级别除了会全表加排他锁进行扫描外，还会加gap 锁。

（8）聚集索引，范围查询：

UPDATE students SET score = 100 WHERE id <= 20;

READ COMMITED 隔离级别会加排他锁；REPEATABLE READ隔离级别除了加排他锁外，还会加gap 锁，特殊情况下，有时候加（20，30]。

（9）辅助索引，范围查询：

UPDATE students SET score = 100 WHERE age <= 23;

先通过辅助索引找到聚集索引，然后查询聚集索引B+树。READ COMMITED 隔离级别都给辅助索引和聚集索引的记录加排他锁；REPEATABLE READ隔离级别除了都给辅助索引和聚集索引的记录加排他锁，还会在命中索引的前、后、间隙间加gap 锁（如图所示）。

注意，可以看到聚集索引的加锁有交叉，如果这时有一个事务锁定行，则可能会造成死锁，如下图：

（10）修改索引值：

UPDATE students SET name = 'John' WHERE id = 15;

先通过辅助索引找到聚集索引，然后查询聚集索引B+树。都给辅助索引和聚集索引的记录加排他锁。

五、并发死锁

死锁：两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。
MySQL 中采用 wait-for graph（等待图-采用非递归深度优先的图算法实现）的方式来进行死锁检测。
异常报错：

deadlock found when trying to get lock ;

5.1、相反加锁顺序死锁

不同表的加锁顺序相反或者相同表不同行加锁顺序相反造成死锁。
其中相同表不同行加锁顺序相反造成死锁有很多变种，其中容易忽略的是给辅助索引行加锁的时候，同时会给聚集索引行加锁。
同时还可能出现在外键索引时，给父表加锁，同时隐含给子表加锁。
触发器同样如此，这些都需要视情况分析。

解决方案是 调整加锁顺序。

5.1、锁冲突死锁

nnodb 在 RR 隔离级别下，最常见的是插入意向锁与 gap 锁冲突造成死锁。
主要原理为：一个事务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞。

解决方案：更换语句或者降低隔离级别。

5.3、查看死锁

（1）系统表。

-- 开启标准监控
CREATE TABLE innodb_monitor (a INT)
ENGINE=INNODB;
-- 关闭标准监控
DROP TABLE innodb_monitor;
-- 开启锁监控
CREATE TABLE innodb_lock_monitor (a INT)
ENGINE=INNODB;
-- 关闭锁监控
DROP TABLE innodb_lock_monitor

（2）系统参数

-- 开启标准监控
set GLOBAL innodb_status_output=ON;
-- 关闭标准监控
set GLOBAL innodb_status_output=OFF;
-- 开启锁监控
set GLOBAL innodb_status_output_locks=ON;
-- 关闭锁监控
set GLOBAL innodb_status_output_locks=OFF;
-- 将死锁信息记录在错误日志中
set GLOBAL innodb_print_all_deadlocks=ON;

（3）命令

-- 查看事务
select * from information_schema.INNODB_TRX;
-- 查看锁
select * from information_schema.INNODB_LOCKS;
-- 查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;

5.4、避免死锁的方式

1. 尽可能以相同顺序来访问索引记录和表。
2. 如果幻读和不可重复读对应用影响不大，可以考虑将隔离级别降为READ COMMITTED。
3. 添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大。
4. 尽量在一个事务中锁定所需要的所有资源，减小死锁概率。
5. 避免大事务，将大事务分拆成多个小事务。因为大事务占用资源多，耗时长，冲突概率变高。
6. 避免同一时间点运行多个对同一表进行读写的概率。

总结

1. MVCC工作原理由read view和聚集索引隐藏列（trx_id和roll pointer）两个构造决定。它们都是为了解决事务可见性的问题；即事务之间可以读哪些数据。
2. read committed隔离级别中，每次读取数据生成新的read view；repeatable read隔离级别中，启动事务时生成新的read view，一直使用直到事务提交。
3. lock in share mode是加读锁，for update是加写锁。
4. gap锁添加场景：唯一索引或主键索引 未命中情况需要加gap锁；辅助非唯一索引要加gap锁。
5. 范围加锁可能会出现死锁的问题。
6. 插入意向锁是为了提升并发性能。如果出现相同的索引插入，则会冲突，冲突原因是因为X锁而不是因为意向锁。插入意向锁只会在其他事务已经加了 gap lock 的时候会阻塞。
7. 事务是用户定义的一系列操作，希望这些操作作为整体来执行，不被其他事务影响；尽量保持ACID特性。
8. 隔离性是通过适当的打破逻辑上的一致性来满足更高的并发性能。