MySQL锁和事务篇

原文链接：https://blog.csdn.net/qq_35105015/article/details/129294685

MySQL锁和事务篇
MySQL锁机制
MySQL锁介绍

按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个database。由MySQL的SQLlayer层实现的

表级锁：锁的是某个table。由MySQL的SQLlayer层实现的

行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

按照锁的功能来说分为：共享读锁和排他写锁。

按照锁的实现方式分为：悲观锁和乐观锁（使用某一版本列或者唯一列进行逻辑控制）

表级锁和行级锁的区别：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

MySQL表级锁
表级锁介绍
由MySQL SQL layer层实现。MySQL的表级锁有两种：

一种是表锁。

一种是元数据锁（meta data lock，MDL)。

MySQL 实现的表级锁定的争用状态变量：

- table_locks_immediate：产生表级锁定的次数；

- table_locks_waited：出现表级锁定争用而发生等待的次数；

表锁
介绍
表锁有两种表现形式：

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

手动增加表锁：

lock table 表名称 read(write),表名称2 read(write)，其他;

查看表锁情况：

show open tables;

删除表锁：

unlock tables;

表锁演示
环境准备

CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
);

INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
读锁演示

写锁演示

元数据锁介绍
在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加MDL 读锁；当要对表做结构变更操作的时候，加MDL 写锁。

元数据锁演示

session1（Navicat）、session2（mysql）

session1: begin;--开启事务
select * from mylock;--加MDL读锁
session2: alter table mylock add f int; -- 修改阻塞
session1：commit; --提交事务或者 rollback 释放读锁
session2：Query OK, 0 rows affected (38.67 sec) --修改完成
Records: 0 Duplicates: 0 Warnings: 0
MySQL行级锁
行级锁介绍
MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB的行级锁，按照锁定范围来说，分为三种：

- 记录锁（Record Locks）:锁定索引中一条记录。

- 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。

- Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合

InnoDB的行级锁，按照功能来说，分为两种：

- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

- 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

手动添加共享锁（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHAREMODE

手动添加排他锁（x）：

SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。

- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。

共享锁（S）

排他锁（X）

意向共享锁（IS）

意向排他锁（IX）

共享锁（S）

兼容

冲突

兼容

冲突

排他锁（X）

冲突

意向共享锁（IS）

兼容

冲突

兼容

意向排他锁

冲突

兼容

Innodb所使用的行级锁定争用状态查看：

show status like 'innodb_row_lock%';

-Innodb_row_lock_current_waits：当前正在等待锁定的数量；

-Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

-Innodb_row_lock_time_avg：每次等待所花平均时间；

-Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

-Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是：

innodb_row_lock_time_avg（等待平均时长）

Innodb_row_lock_waits（等待总次数）

Innodb_row_lock_time（等待总时长）这三项。

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

两阶段锁
传统RDBMS加锁的一个原则，就是2PL (Two-Phase Locking，二阶段锁)。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。

从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。

加锁阶段：只加锁，不放锁。

解锁阶段：只放锁，不加锁。

InnoDB行锁演示

创建表及索引
create table test_innodb_lock (a int(11),b varchar(16)) engine=innodb;

create index test_innodb_a_idx on test_innodb_lock(a);
##create index test_innodb_lock_b_idx on test_innodb_lock(b);

行锁定基本演示

Session a

Session b

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b

= 'b1' where a = 1;

Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

更新，但是不提交

mysql> update test_innodb_lock set b = 'b2' where a = 1;

被阻塞，等待

mysql> commit;

Query OK, 0 rows affected (0.05 sec)

提交

mysql> update test_innodb_lock set b = 'b2' where a = 1;

Query OK, 0 rows affected (36.14 sec) Rows matched: 1 Changed: 0 Warnings: 0

解除阻塞，更新正常进行

无索引行锁升级为表锁演示

Session a

Session b

mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b = '2' where b = 2000;

Query OK, 1 row affected (0.02 sec) Rows

matched: 1 Changed: 1 Warnings: 0

mysql> update test_innodb_lock set b = '3' where b = 3000;

被阻塞，等待

mysql> commit;

Query OK, 0 rows affected (0.10 sec)

mysql> update test_innodb_lock set b = '3' where b = 3000;

Query OK, 1 row affected (1 min 3.41 sec) Rows matched: 1 Changed: 1 Warnings: 0

阻塞解除，完成更新

间隙锁带来的插入问题演示
　

Session a

Session b

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected(0.00 sec)

mysql> select * from test_innodb_lock;

| a | b | |1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 |6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 |b1 | 9 rows in set (0.00 sec)matched: 1 Changed: 1 Warnings: 0

mysql> update test_innodb_lock set b = '3' where b = 3000;

被阻塞，等待

mysql> update test_innodb_lock set b = a * 100 where a < 4 and a > 1;

Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0affected (0.10 sec)

mysql> insert into test_innodb_lock values(2,'200');

被阻塞，等待

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_innodb_lock values(2,'200');

Query OK, 1 row affected (38.68 sec)

阻塞解除，完成插入

使用共同索引不同数据的阻塞示例
　

Session a

Session b

mysql> set autocommit=0;

QueryOK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b= 'b2';

Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1';

被阻塞

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1';

Query OK, 1 row affected (42.89 sec) Rows matched: 1 Changed: 1 Warnings: 0 session

提交事务，阻塞去除，更新完成

死锁演示
　

Session a

Session b

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set id = 110 where id = 11;

Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

mysql> update t2 set id = 210 where id = 21;

Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql>update t2 set id=2100 where id=21;

等待sessionb释放资源，被阻塞

mysql>update t1 set id=1100 where id=11;

Query OK,0 rows affected (0.39sec) Rows matched: 0 Changed: 0 Warnings:0

等待 sessiona释放资源，被阻塞

两个 session 互相等等待对方的资源释放之后才能释放自己的资源,造成了死锁

行锁演示
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

where 、索引使用行锁，否则表锁。

行读锁
session1（Navicat）、 session2（mysql）

Innodb_row_lock_current_waits：当前正在等待锁定的数量；

Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

Innodb_row_lock_time_avg：每次等待所花平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

查看行锁状态: show STATUS like 'innodb_row_lock%';

session1: begin;--开启事务未提交
select * from mylock where ID=1 lock in share mode; --手动加id=1
的行读锁,使用索引
session2：update mylock set name='y' where id=2; -- 未锁定该行可以修改
session2：update mylock set name='y' where id=1; -- 锁定该行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 锁定超时
session1: commit; --提交事务或者 rollback 释放读锁
session2：update mylock set name='y' where id=1; --修改成功Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
使用索引加行锁，未锁定的行可以访问

行读锁升级为表锁
session1（Navicat）、session2（mysql）

session1: begin;--开启事务未提交
--手动加name='c'的行读锁,未使用索引
select * from mylock where name='c' lock in share mode;
session2：update mylock set name='y' where id=2; -- 修改阻塞未用索引行锁升级为表锁
session1: commit; --提交事务或者 rollback 释放读锁
session2：update mylock set name='y' where id=2; --修改成功Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
未使用索引行锁升级为表锁

行写锁
session1（Navicat）、session2（mysql）

session1: begin;--开启事务未提交
--手动加id=1的行写锁,
select * from mylock where id=1 for update;
session2: select * from mylock where id=2 ; -- 可以访问
session2: select * from mylock where id=1 ; -- 可以读不加锁
session2: select * from mylock where id=1 lock in share mode ; -- 加读锁
session1: commit ; -- 提交事务或者 rollback 释放写锁
session2：执行成功
主键索引产生记录锁

间隙锁

间隙锁防止两种情况
1、防止插入间隙内的数据

2、防止已有数据更新为间隙内的数据

死锁
两个 session 互相等等待对方的资源释放之后，才能释放自己的资源,造成了死锁

session1（Navicat）、session2（mysql）

session1: begin;--开启事务未提交
--手动加行写锁 id=1 ，使用索引
update mylock set name='m' where id=1;
session2：begin;--开启事务未提交
--手动加行写锁 id=2 ，使用索引
update mylock set name='m' where id=2;

session1: update mylock set name='nn' where id=2; -- 加写锁被阻塞
session2：update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
InnoDB架构分析
InnoDB架构图

InnoDB磁盘文件

InnoDB的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是redo日志文件和归档文件。

二进制文件(binlog)等文件是MySQL Server层维护的文件，所以未列入InnoDB的磁盘文件中。

系统表空间和用户表空间

系统表空间存储哪些数据？
系统表空间是一个共享的表空间，因为它是被多个表共享的。

InnoDB系统表空间包含InnoDB数据字典(元数据以及相关对象)、double write buﬀer、change buﬀer、undo logs的存储区域。

系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。

系统表空间配置解析
系统表空间是由一个或者多个数据文件组成。

默认情况下，一个初始大小为10MB，名为ibdata1的系统数据文件在MySQL的data目录下被创建。

用户可以使用innodb_data_ﬁle_path对数据文件的大小和数量进行配置：

innodb_data_file_path=datafile1[,datafile2]...

innodb_data_file_path=/db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

如果这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。

两个文件的文件名之后都跟了属性，表示文件ibdata1的大小为1000MB，文件ibdata2的大小为1000MB，而且用完空间之后可以自动增长(autoextend)。

如何使用用户表空间？
如果设置了参数innodb_ﬁle_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立的用户表空间。用户表空间的命名规则为：表名.ibd。

通过这种方式，用户不用将所有数据都存放于默认的系统表空间中。

用户表空间存储哪些数据？
用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统表空间中。

重做日志文件和归档文件

哪些文件是重做日志文件？
默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logﬁle0和ib_logﬁle1的文件，这就是InnoDB的重做日志文件(redo log ﬁle)，它记录了对于InnoDB存储引擎的事务日志。

重做日志文件的作用是什么？
当InnoDB的数据存储文件发生错误时，重做日志文件就能派上用场。InnoDB存储引擎可以使用重做日志文件将数据恢复为正确状态，以此来保证数据的正确性和完整性。

为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上，以此来提高重做日志的高可用性。

重做日志文件组是如何写入数据的？
每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。

在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。

InnoDB存储引擎先写入重做日志文件1，当文件被写满时，会切换到重做日志文件2，再当重做日志文件2也被写满时，再切换到重做日志文件1。

如何设置重做日志文件大小？
用户可以使用innodb_log_file_size来设置重做日志文件的大小，这对InnoDB存储引擎的性能有着非常大的影响。

如果重做日志文件设置的太大，数据丢失时，恢复时可能需要很长的时间；

另一方面，如果设置的太小，重做日志文件太小会导致依据checkpoint的检查需要频繁刷新脏页到磁盘中，导致性能的抖动。

InnoDB内存结构
Buﬀer Pool缓冲池
概述
InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能。

所以，缓冲池的大小直接影响着数据库的整体性能，可以通过配置参数innodb_buﬀer_pool_size来设置。

具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲(insert buﬀer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)和数据字典信息(data dictionary)。

在架构图上可以看到，InnoDB存储引擎的内存区域除了有缓冲池之外，还有重做日志缓冲和额外内存池。InnoDB存储引擎首先将重做日志信息先放到这个缓冲区中，然后按照一定频率将其刷新到重做日志文件中。重做日志缓冲一般不需要设置的很大，该值可由配置参数innodb_log_buﬀer_size控制。

数据页和索引页
InnoDB存储引擎工作时，需要以Page页为最小单位去将磁盘中的数据加载到内存中，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型，数据页和索引页就是其中最为重要的两种类型。

插入缓冲
主要针对次要索引的数据插入存在的问题而设计。

我们都知道，在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在次要索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于次要索引叶节点的插入不再是顺序的了，这时就需要离散的访问次要索引页，由于随机读取的存在导致插入操作性能下降。

InnoDB为此设计了Insert Buﬀer来进行插入优化。对于次要索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非主键索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buﬀer中。看似数据库这个非主键的索引已经插到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buﬀer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

内存数据落盘分析
整体思路分析

InnoDB内存缓冲池中的数据page要完成持久化的话，是通过两个流程来完成的，一个是脏页落盘；一个是预写redolog日志。

当缓冲池中的页的版本比磁盘要新时，数据库需要将新版本的页从缓冲池刷新到磁盘。但是如果每次一个页发送变化，就进行刷新，那么性能开发是非常大的，于是InnoDB采用了Write AheadLog（WAL）策略和Force Logat Commit机制实现事务级别下数据的持久性。

WAL要求数据的变更写入到磁盘前，首先必须将内存中的日志写入到磁盘；

Force-log-at-commit要求当一个事务提交时，所有产生的日志都必须刷新到磁盘上，如果日志刷新成功后，缓冲池中的数据刷新到磁盘前数据库发生了宕机，那么重启时，数据库可以从日志中恢复数据。

为了确保每次日志都写入到重做日志文件，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作，将缓冲文件从文件系统缓存中真正写入磁盘。可以通过innodb_ﬂush_log_at_trx_commit来控制重做日志刷新到磁盘的策略。

脏页落盘
在数据库中进行读取操作，将从磁盘中读到的页放在缓冲池中，下次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。

重做日志落盘
InnoDB存储引擎会首先将重做日志信息先放入重做日志缓冲中，然后再按照一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况每一秒钟都会讲重做日志缓冲刷新到日志文件中。可通过配置参数innodb_log_buﬀer_size控制，默认为8MB。

CheckPoint检查点机制
简介
思考一下这个场景：如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。

但是这需要两个前提条件：

缓冲池可以缓存数据库中所有的数据；

重做日志可以无限增大

因此Checkpoint（检查点）技术就诞生了，目的是解决以下几个问题：

缩短数据库的恢复时间；

缓冲池不够用时，将脏页刷新到磁盘；

重做日志不可用时，刷新脏页。

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。

当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。

当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

对于InnoDB存储引擎而言，是通过LSN（Log Sequence Number）来标记版本的。

LSN是8字节的数字，每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。可以通过命令 SHOW ENGINE INNODB STATUS 来观察：

Checkpoint发生的时间、条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘，不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint。

Checkpoint分类
在InnoDB存储引擎内部，有两种Checkpoint，分别为：Sharp Checkpoint、Fuzzy Checkpoint

sharpcheckpoint

在关闭数据库的时候，将buﬀerpool中的脏页全部刷新到磁盘中。

fuzzycheckpoint

数据库正常运行时，在不同的时机，将部分脏页写入磁盘。仅刷新部分脏页到磁盘，也是为了避免一次刷新全部的脏页造成的性能问题。

Master Thread Checkpoint

在Master Thread中，会以每秒或者每10秒一次的频率，将部分脏页从内存中刷新到磁盘，这个过程是异步的。正常的用户线程对数据的操作不会被阻塞。

FLUSH_LRU_LIST Checkpoint

FLUSH_LRU_LIST checkpoint是在单独的page cleaner线程中执行的。

MySQL对缓存的管理是通过buﬀer pool中的LRU列表实现的，LRU 空闲列表中要保留一定数量的空闲页面，来保证buﬀer pool中有足够的空闲页面来相应外界对数据库的请求。

当这个空间页面数量不足的时候，发生FLUSH_LRU_LIST checkpoint。

空闲页的数量由innodb_lru_scan_depth参数表来控制的，因此在空闲列表页面数量少于配置的值的时候，会发生checkpoint，剔除部分LRU列表尾端的页面。

因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。

在InnoDB1.1.x版本之前，需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中，显然这会阻塞用户的查询操作。倘若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LISTCheckpoint。而从MySQL 5.6版本，也就是InnoDB1.2.x版本开始，这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024.

Async/Sync Flush Checkpoint

Async/Sync Flush checkpoint是在单独的page cleaner线程中执行的。

Async/Sync Flush checkpoint 发生在重做日志不可用的时候，将buffer pool中的一部分脏页刷新到磁盘中，在脏页写入磁盘之后，事物对应的重做日志也就可以释放了。

关于redo_log文件的的大小，可以通过 innodb_log_file_size 来配置。

对于是执行Async Flushcheckpoint还是Sync Flush checkpoint，由checkpoint_age以及async_water_mark和sync_water_mark来决定。因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。

而从MySQL 5.6版本，也就是InnoDB1.2.x版本开始，这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024.

当[checkpoint_age的时候，无需执行Flush checkpoint。也就说，redo log剩余空间超过25%的时候，无需执行Async/Sync Flush checkpoint。

当[async_water_mark的时候，执行Async Flush checkpoint，也就说，redo log剩余空间不足25%，但是大于10%的时候，执行Async Flushcheckpoint，刷新到满足条件1

当checkpoint_age>sync_water_mark的时候，执行sync Flush checkpoint。也就说，redo log剩余空间不足10%的时候，执行Sync Flush checkpoint，刷新到满足条件1。在mysql5.6之后，不管是Async Flushcheckpoint还是Sync Flush checkpoint，都不会阻塞用户的查询进程。

总结：

由于磁盘是一种相对较慢的存储设备，内存与磁盘的交互是一个相对较慢的过程由于innodb_log_file_size定义的是一个相对较大的值，正常情况下，由前面两种checkpoint刷新脏页到磁盘，在前面两种checkpoint刷新脏页到磁盘之后，脏页对应的redolog空间随即释放，一般不会发生Async/Sync Flush checkpoint。同时也要意识到，为了避免频繁低发生Async/Sync Flushcheckpoint，也应该将innodb_log_file_size配置的相对较大一些。

Dirty Page too much Checkpoint

Dirty Page too much Checkpoint是在Master Thread 线程中每秒一次的频率实现的。

Dirty Page too much 意味着buﬀer pool中的脏页过多，执行checkpoint脏页刷入磁盘，保证buﬀerpool中有足够的可用页面。

Dirty Page 由innodb_max_dirty_pages_pct配置，innodb_max_dirty_pages_pct的默认值在innodb 1.0之前是90%，之后是75%。

Double Write双写
如果说InsertBuﬀer给InnoDB存储引擎带来了性能上的提升，那么Double Write带给InnoDB存储引擎的是数据页的可靠性。

Redo log Buﬀer重做日志缓冲

如上图所示，InnoDB在缓冲池中变更数据时，会首先将相关变更写入重做日志缓冲中，然后再按时或者当事务提交时写入磁盘，这符合Force-log-at-commit原则；

当重做日志写入磁盘后，缓冲池中的变更数据才会依据checkpoint机制择时写入到磁盘中，这符合WAL原则。

在checkpoint择时机制中，就有重做日志文件写满的判断，所以，如前文所述，如果重做日志文件太小，经常被写满，就会频繁导致checkpoint将更改的数据写入磁盘，导致性能抖动。

操作系统的文件系统是带有缓存的，当InnoDB向磁盘写入数据时，有可能只是写入到了文件系统的缓存中，没有真正的“落袋为安”。

InnoDB的innodb_flush_log_at_trx_commit属性可以控制每次事务提交时InnoDB的行为。

当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入；

当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；

当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。

innodb_flush_log_at_commit是InnoDB性能调优的一个基础参数，涉及InnoDB的写入效率和数据安全。

当参数值为0时，写入效率最高，但是数据安全最低；

参数值为1时，写入效率最低，但是数据安全最高；

参数值为2时，二者都是中等水平。

一般建议将该属性值设置为1，以获得较高的数据安全性，而且也只有设置为1，才能保证事务的持久性。

日志的刷盘机制如下图所示：

事务原理及MVCC
事前准备
为了故事的顺利发展，我们需要创建一个表：

1 CREATE TABLE t (
2 id INT PRIMARY KEY,
3 c VARCHAR(100)
4 ) Engine=InnoDB CHARSET=utf8;
然后向这个表里插入一条数据：

INSERT INTO t VALUES(1, '刘备');
现在表里的数据就是这样的：

mysql> SELECT * FROM t;
+----+--------+
| id | c |
+----+--------+
| 1 | 刘备 |
+----+--------+
1 row in set (0.01 sec)
隔离级别
未提交读（READUNCOMMITTED/RU）
未提交读会导致脏读：一个事务读取到另一个事务未提交的数据。

脏读违背了现实世界的业务含义，所以这种READ UNCOMMITTED 算是十分不安全的一种隔离级别。

已提交读（READ COMMITTED/RC）
已提交读会导致:

不可重复读：一个事务因读取到另一个事务已提交的update。导致对同一条记录读取两次以上的结果不一致。

幻读：一个事务因读取到另一个事务已提交的insert数据或者delete数据。导致对同一张表读取两次以上的结果不一致。

我们在Session B 中提交了几个隐式事务，这些事务都修改了id 为 1 的记录的列c的值，每次事务提交之后， Session A 中的事务都可以查看到最新的值。这种现象也被称之为不可重复读。

可重复读（REPEATABLE READ/RR）

串行化（SERIALIZABLE）

丢失更新
两个事务针对同一数据都发生修改操作时，会存在丢失更新问题。

总结
数据库的事务并发问题需要使用并发控制机制去解决，数据库的并发控制机制有很多，最为常见的就是锁机制。锁机制一般会给竞争资源加锁，阻塞读或者写操作来解决事务之间的竞争条件，最终保证事务的可串行化。

而MVCC则引入了另外一种并发控制，它让读写操作互不阻塞，每一个写操作都会创建一个新版本的数据，读操作会从有限多个版本的数据中挑选一个最合适的结果直接返回，由此解决了事务的竞争条件。

版本链
回滚段/uodo log
根据行为的不同，undo log分为两种： insert undo log 和update undo log

insert undo log
是在insert操作中产生的 undo log。

因为insert操作的记录只对事务本身可见，对于其它事务此记录是不可见的，所以insert undo log 可以在事务提交后直接删除而不需要进行 purge 操作。

如下图所示（初始状态）：

update undo log ：
是 update 或 delete 操作中产生的 undo log。

因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此 update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入history list 上，等待 purge 线程进行最后的删除操作。

如下图所示（第一次修改）：

当事务2使用UPDATE语句修改该行数据时，会首先使用排他锁锁定改行，将该行当前的值复制到undo log中，然后再真正地修改当前行的值，最后填写事务ID，使用回滚指针指向undo log中修改前的行。

当事务3进行修改与事务2的处理过程类似，如下图所示（第二次修改）：

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种Undo 文件组织方式。

案例演示
InnoDB行记录有三个隐藏字段：分别对应该行的rowid、事务号db_trx_id和回滚指针db_roll_ptr，其中db_trx_id表示最近修改的事务的id，db_roll_ptr指向回滚段中的undo log。

对于使用InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（ row_id并不是必要的，我们创建的表中有主键或者非NULL唯一键时都不会包含 row_id 列）：

trx_id ：每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给trx_id 隐藏列。

roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

假设插入该记录的事务id为 80 ，那么此刻该条记录的示意图如下所示：

假设之后两个id 分别为 100 、 200 的事务对这条记录进行UPDATE 操作，操作流程如下：

小贴士：能不能在两个事务中交叉更新同一条记录呢？哈哈，这是不可以滴，第一个事务更新了某条记录后，就会给这条记录加锁，另一个事务再次更新时就需要等待第一个事务提交了，把锁释放之后才可以继续更新。

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（ INSERT 操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer 属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要，我们稍后就会用到。

ReadView
对于使用READ UNCOMMITTED 隔离级别的事务来说，直接读取记录的最新版本就好了。

对于使用SERIALIZABLE 隔离级别的事务来说，使用加锁的方式来访问记录。

对于使用READ COMMITTED 和REPEATABLE READ 隔离级别的事务来说，就需要用到我们上边所说的版本链了。

核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。所以设计InnoDB 的设计者提出了一个ReadView的概念，这个 ReadView 中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids。

这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本（版本链中的版本）是否可见：

如果被访问版本的trx_id 属性值小于m_ids 列表中最小的事务id，表明生成该版本的事务在生成ReadView 前已经提交，所以该版本可以被当前事务访问。

如果被访问版本的trx_id 属性值大于m_ids 列表中最大的事务id，表明生成该版本的事务在生成ReadView 后才生成，所以该版本不可以被当前事务访问。

如果被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中：如果在，说明创建ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本，如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

在MySQL 中，READ COMMITTED 和REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

READ COMMITTED
每次读取数据前都生成一个ReadView

比方说现在系统里有两个id 分别为 100 、 200 的事务在执行：

# Transaction 100 BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;
# Transaction 200 BEGIN;

# 更新了一些别的表的记录
小贴士：事务执行过程中，只有在第一次真正修改记录时（比如使用INSERT、DELETE、UPDATE语句），才会被分配一个单独的事务id，这个事务id是递增的。

此刻，表t 中id 为 1 的记录得到的版本链表如下所示：

假设现在有一个使用READ COMMITTED 隔离级别的事务开始执行：

1 # 使用READ COMMITTED隔离级别的事务
2 BEGIN; 3
4 # SELECT1：Transaction 100、200未提交
5 SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
这个SELECT 的执行过程如下：

在执行SELECT 语句时会先生成一个ReadView， ReadView 的m_ids 列表的内容就是[100, 200] 。

然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c 的内容是'张飞' ，该版本的trx_id 值为 100 ，在m_ids列表内，所以不符合可见性要求，根据roll_pointer 跳到下一个版本。

下一个版本的列c 的内容是'关羽' ，该版本的trx_id 值也为 100 ，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。

下一个版本的列c 的内容是'刘备' ，该版本的trx_id 值为 80 ，小于m_ids列表中最小的事务id 100 ，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c 为'刘备' 的记录。

之后，我们把事务id为 100 的事务提交一下，就像这样：

# Transaction 100 BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1; UPDATE t SET c = '张飞' WHERE id = 1;
COMMIT;
然后再到事务id为 200 的事务中更新一下表t 中id 为1的记录：

# Transaction 200 BEGIN;

# 更新了一些别的表的记录
...
UPDATE t SET c = '赵云' WHERE id = 1; UPDATE t SET c = '诸葛亮' WHERE id = 1;
此刻，表t 中id 为 1 的记录的版本链就长这样：

然后再到刚才使用READ COMMITTED 隔离级别的事务中继续查找这个id为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'张飞'
这个SELECT的执行过程如下：

在执行SELECT 语句时会先生成一个ReadView ， ReadView 的m_ids 列表的内容就是[200] （事务id为 100 的那个事务已经提交了，所以生成快照时就没有它了）。

然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c的内容是'诸葛亮' ，该版本的trx_id 值为 200 ，在m_ids 列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。

下一个版本的列c 的内容是'赵云' ，该版本的trx_id 值为 200 ，也在m_ids 列表内，所以也不符合要求，继续跳到下一个版本。

下一个版本的列c 的内容是'张飞' ，该版本的trx_id 值为 100 ，比m_ids 列表中最小的事务id 200 还要小，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c 为'张飞' 的记录。

以此类推，如果之后事务id为 200 的记录也提交了，再此在使用READ COMMITTED 隔离级别的事务中查询表t 中id 值为 1 的记录时，得到的结果就是'诸葛亮' 了，具体流程我们就不分析了。

总结一下就是：使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

REPEATABLE READ
在事务开始后第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ 隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView ，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。

比方说现在系统里有两个id 分别为 100 、 200 的事务在执行：

# Transaction 100 BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;
# Transaction 200 BEGIN;

# 更新了一些别的表的记录
此刻，表t 中id 为 1 的记录得到的版本链表如下所示：

假设现在有一个使用REPEATABLE READ 隔离级别的事务开始执行：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
这个SELECT 的执行过程如下：

在执行SELECT 语句时会先生成一个ReadView ， ReadView 的m_ids 列表的内容就是[100, 200] 。

然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c 的内容是'张飞' ，该版本的trx_id 值为 100 ，在m_ids 列表内，所以不符合可见性要求，根据roll_pointer 跳到下一个版本。

下一个版本的列c 的内容是'关羽' ，该版本的trx_id 值也为 100 ，也在m_ids 列表内，所以也不符合要求，继续跳到下一个版本。

下一个版本的列c 的内容是'刘备' ，该版本的trx_id 值为 80 ，小于m_ids 列表中最小的事务id 100 ，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c 为'刘备' 的记录。

之后，我们把事务id为 100 的事务提交一下，就像这样：

# Transaction 100 BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1; UPDATE t SET c ='张飞' WHERE id = 1;
COMMIT;
再到事务id为 200 的事务中更新一下表 t 中 id 为1的记录：

# Transaction200
BEGIN;
# 更新了一些别的表的记录
...
UPDATEt SET c = '赵云' WHERE id = 1;
UPDATEt SET c = '诸葛亮' WHERE id = 1
此刻，表t 中id 为 1 的记录的版本链就长这样：

然后再到刚才使用REPEATABLE READ 隔离级别的事务中继续查找这个id为 1 的记录，如下：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
# SELECT2：Transaction 100提交，Transaction200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值仍为'刘备'
这个SELECT2 的执行过程如下：

因为之前已经生成过ReadView 了，所以此时直接复用之前的ReadView ，之前的ReadView 中的m_ids 列表就是[100, 200] 。

下一个版本的列c 的内容是'赵云' ，该版本的trx_id 值为 200 ，也在m_ids 列表内，所以也不符合要求，继续跳到下一个版本。

下一个版本的列c 的内容是'张飞' ，该版本的trx_id 值为 100 ，而m_ids 列表中是包含值为100 的事务id的，所以该版本也不符合要求，同理下一个列c 的内容是'关羽' 的版本也不符合要求。继续跳到下一个版本。

下一个版本的列c 的内容是'刘备' ，该版本的trx_id 值为 80 ， 80 小于m_ids 列表中最小的事务id 100 ，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c 为'刘备' 的记录。

也就是说两次SELECT 查询得到的结果是重复的，记录的列c 值都是'刘备' ，这就是可重复读的含义。如果我们之后再把事务id为 200 的记录提交了，之后再到刚才使用REPEATABLE READ 隔离级别的事务中继续查找这个id为 1 的记录，得到的结果还是'刘备' 。

MVCC总结
从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READCOMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

READCOMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同就是生成ReadView的时机不同， READ COMMITTD 在每一次进行普通 SELECT操作前都会生成一个 ReadView ，而 REPEATABLE READ 只在第一次进行普通SELECT 操作前生成一个 ReadView ，之后的查询操作都重复这个ReadView。

当前读和快照读
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？

以MySQL InnoDB为例：

快照读：简单的select操作，读取的是记录的可见版本 (有可能是历史版本)，不加锁。(当然，也有例外，下面会分析)

当前读：特殊的读操作，插入/更新/删除操作，读取的是记录的最新版本，并且当前读返回的记录，需要加锁保证其他事务不会再并发修改这条记录。

当前读
特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * fromtable where ? lock in share mode;//共享锁（S）

select * from table where ? forupdate;//排他锁(X)

insert into table values (…) ; //排他锁(X)

update table set ? where ? ; //排他锁(X)

delete from table where ? ; //排他锁(X)

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。

更新操作，在数据库中的执行流程：

从图中，可以看到，一个Update操作的具体流程：

当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁(current read)。

待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。

一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。

因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

一致性非锁定读
一致性非锁定读(consistentnonlocking read)是指InnoDB存储引擎通过多版本控制(MVCC)读取当前数据库中行数据的方式。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个快照。

上图直观地展现了InnoDB一致性非锁定读的机制。之所以称其为非锁定读，是因为不需要等待行上排他锁的释放。快照数据是指该行的之前版本的数据，每行记录可能有多个版本，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制(Multi VersionConcurrency Control,MVCC)。InnoDB是通过undolog来实现MVCC。

在事务隔离级别READ COMMITTED和REPEATABLE READ下，InnoDB默认使用一致性非锁定读。然而，对于快照数据的定义却不同。在READCOMMITTED事务隔离级别下，一致性非锁定读总是读取被锁定行的最新一份快照数据。而在REPEATABLEREAD事务隔离级别下，则读取事务开始时的行数据版本。

我们下面举个例子来详细说明一下上述的情况。

# session A
mysql> BEGIN;
mysql> SELECT * FROM test WHERE id = 1;
我们首先在会话A中显示地开启一个事务，然后读取test表中的id为1的数据，但是事务并没有结束。于此同时，用户在开启另一个会话B，这样可以模拟并发的操作，然后对会话B做出如下的操作：

# session B
mysql> BEGIN;
mysql> UPDATE test SET id = 3 WHERE id = 1;
在会话B的事务中，将test表中id为1的记录修改为id=3，但是事务同样也没有提交，这样id=1的行其实加了一个排他锁。由于InnoDB在READ COMMITTED和REPEATABLE READ事务隔离级别下使用一致性非锁定读，这时如果会话A再次读取id为1的记录，仍然能够读取到相同的数据。此时，READCOMMITTED和REPEATABLE READ事务隔离级别没有任何区别。

会话A和会话B示意图：

如上图所示，当会话B提交事务后，会话A再次运行 SELECT * FROM test WHERE id = 1 的SQL语句时，两个事务隔离级别下得到的结果就不一样了。

对于READ COMMITTED的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。因为会话B的事务已经提交，所以在该隔离级别下上述SQL语句的结果集是空的。

对于REPEATABLE READ的事务隔离级别，总是读取事务开始时的行数据，因此，在该隔离级别下，上述SQL语句仍然会获得相同的数据。

InnoDB的MVCC实现
我们首先来看一下wiki上对MVCC的定义：

Multiversion concurrencycontrol (MCC or MVCC), is a concurrency control method commonly used bydatabase management systems to provide concurrent access to the database and inprogramming languages to implement transactional memory.

由定义可知，MVCC是用于数据库提供并发访问控制的并发控制技术。与MVCC相对的，是基于锁的并发控制， Lock-Based Concurrency Control 。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

多版本并发控制仅仅是一种技术概念，并没有统一的实现标准，其核心理念就是数据快照，不同的事务访问不同版本的数据快照，从而实现不同的事务隔离级别。虽然字面上是说具有多个版本的数据快照，但这并不意味着数据库必须拷贝数据，保存多份数据文件，这样会浪费大量的存储空间。InnoDB通过事务的undo日志巧妙地实现了多版本的数据快照。

数据库的事务有时需要进行回滚操作，这时就需要对之前的操作进行undo。因此，在对数据进行修改时，InnoDB会产生undo log。当事务需要进行回滚时，InnoDB可以利用这些undo log将数据回滚到修改之前的样子。

事务总结
事务的隔离性由多版本控制机制和锁实现，而原子性，持久性和一致性主要是通过redolog、undo log 和Force Log at Commit机制机制来完成的。redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控制。而Force Log at Commit机制保证事务提交后redo log日志都已经持久化。

我们再来总结一下数据库事务的整个流程，如下图所示。

行锁原理分析
一条简单SQL的加锁分析
在介绍完一些背景知识之后，接下来将选择几个有代表性的例子，来详细分析MySQL的加锁处理。当然，还是从最简单的例子说起。经常有朋友发给我一个SQL，然后问我，这个SQL加什么锁？就如同下面两条简单的SQL，他们加什么锁？

SQL1：select* from t1 where id = 10;

SQL2：deletefrom t1 where id = 10;

针对这个问题，该怎么回答？能想象到的一个答案是：

SQL1：不加锁。因为MySQL是使用多版本并发控制的，读不加锁。

SQL2：对id = 10的记录加写锁 (走主键索引)。

这个答案对吗？说不上来。即可能是正确的，也有可能是错误的，已知条件不足，这个问题没有答案。必须还要知道以下的一些前提，前提不同，能给出的答案也就不同。要回答这个问题，还缺少哪些前提条件？

前提一：id列是不是主键？

前提二：当前系统的隔离级别是什么？

前提三：id列如果不是主键，那么id列上有索引吗？

前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？

前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？

没有这些前提，直接就给定一条SQL，然后问这个SQL会加什么锁，都是很业余的表现。而当这些问题有了明确的答案之后，给定的SQL会加什么锁，也就一目了然。下面，我们将这些问题的答案进行组合，然后按照从易到难的顺序，逐个分析每种组合下，对应的SQL会加哪些锁？

注：下面的这些组合，需要做一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤 (索引扫描)。但实际情况会复杂很多，真正的执行计划，还是需要根据MySQL输出的为准。

组合一：id列是主键，RC隔离级别

组合二：id列是二级唯一索引，RC隔离级别

组合三：id列是二级非唯一索引，RC隔离级别

组合四：id列上没有索引，RC隔离级别

组合五：id列是主键，RR隔离级别

组合六：id列是二级唯一索引，RR隔离级别

组合七：id列是二级非唯一索引，RR隔离级别

组合八：id列上没有索引，RR隔离级别

组合九：Serializable隔离级别

排列组合还没有列举完全，但是看起来，已经很多了。真的有必要这么复杂吗？事实上，要分析加锁，就是需要这么复杂。但是从另一个角度来说，只要你选定了一种组合，SQL需要加哪些锁，其实也就确定了。接下来，就让我们来逐个分析这9种组合下的SQL加锁策略。

注：在前面八种组合下，也就是RC，RR隔离级别下SQL1：select操作均不加锁，采用的是快照读，因此在下面的讨论中就忽略了，主要讨论SQL2：delete操作的加锁。

组合一：id主键+RC
这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上id = 10的记录加上X锁即可。

如下图所示：

组合二：id唯一索引+RC
这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图：

此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 wherename = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

组合三：id非唯一索引+RC
相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？

同样见下图：

根据此图，可以看到，首先，id列索引上，满足id=10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

组合四：id无索引+RC
相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。

对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id = 10;的记录加上X锁。那么实际情况呢？请看下图：

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。

有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQLServer过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

若id列上没有索引，SQL会走聚簇索引的全表扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束

上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在Repeatable Read隔离级别下的加锁行为。

组合五：id主键+RR
组合五，id列是主键列，Repeatable Read隔离级别，针对delete from t1 whereid = 10; 这条SQL，加锁与组合一：[id主键，Read Committed]一致。

组合六：id唯一索引+RR
与组合五类似，组合六的加锁，与组合二：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

组合七：id非唯一索引+RR
还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？问题的答案，就在组合七中揭晓。

组合七，Repeatable Read隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图：

此图，相对于组合三：[id列上非唯一锁，Read Committed]看似相同，其实却有很大的区别。最大的区别在于，这幅图中多了一个GAP锁，而且GAP锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP锁有何用？

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP 锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。

如图中所示，有哪些位置可以插入新的满足条件的项 (id= 10)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[6,c]之前，不会插入id=10的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b] 与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。

Insert操作，如insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)，与组合三类似。同时还是增加3把GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

有心的朋友看到这儿，可以会问：既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，却不需要加GAP锁呢？

首先，这是一个好问题。其次，回答这个问题，也很简单。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id是主键；组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了GAP锁的使用。其实，针对此问题，还有一个更深入的问题：如果组合五、组合六下，针对SQL： select * from t1 where id = 10 for update; 第一次查询，没有找到满足查询条件的记录，那么GAP锁是否还能够省略？此问题留给大家思考。

结论：

Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：deletefrom t1 where id= 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

组合八：id无索引+RR
组合八，Repeatable Read隔离级别下的最后一种情况，id列上没有索引。此时SQL：deletefrom t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示

如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？

在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

当然，跟组合四：[id无索引, Read Committed]类似，这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistentread开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是read committed隔离级别；要么是RepeatableRead隔离级别，同时设置了 innodb_locks_unsafe_for_binlog 参数。

总结

在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semiconsistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

组合九：Serializable
针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

结论

在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

一条复杂SQL的加锁分析
写到这里，其实MySQL的加锁实现也已经介绍的八八九九。只要将本文上面的分析思路，大部分的SQL，都能分析出其会加哪些锁。而这里，再来看一个稍微复杂点的SQL，用于说明MySQL加锁的另外一个逻辑。SQL用例如下：

如图中的SQL，会加什么锁？假定在Repeatable Read隔离级别下 (Read Committed隔离级别下的加锁情况，留给学生们分析)，同时，假设SQL走的是idx_t1_pu索引。

在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？在这里，我直接给出分析后的结果：

Index key：pubtime > 1 andpuptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。

Index Filter：userid= ‘hdc’ 。此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index Key。

Table Filter：comment is not NULL。此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。

在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，如下图所示：

从图中可以看出，在RepeatableRead隔离级别下，由IndexKey所确定的范围，被加上了GAP锁； Index Filter锁给定的条件(userid = ‘hdc’)何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index ConditionPushdown(ICP)，因此IndexFilter在MySQLServer层过滤，在5.6后支持了Index Condition Pushdown，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁；若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server 层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

结论：

在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。

- Index Key确定的范围，需要加上GAP锁；

- Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足IndexFilter的记录，不加X锁，否则需要X锁；

- Table Filter过滤条件，无论是否满足，都需要加X锁。

死锁原理与分析
本文前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有两个比较重要的作用：

可以根据MySQL的加锁规则，写出不会发生死锁的SQL；

可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

下面，来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)：

上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。

第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而 Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

结论：

死锁的发生与否，并不在于事务中有多少条SQL语句，【死锁的关键在于】：两个(或以上)的Session【加锁的顺序】不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

如何解决死锁呢？

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。

原理分析总结
要做到完全掌握MySQL/InnoDB的加锁规则，甚至是其他任何数据库的加锁规则，需要具备以下的一些知识点：

了解数据库的一些基本理论知识：数据的存储格式 (堆组织表 vs 聚簇索引表)；并发控制协议(MVCC vs Lock-BasedCC)；Two-Phase Locking；数据库的隔离级别定义 (Isolation Level)；了解SQL本身的执行计划 (主键扫描 vs 唯一键扫描 vs 范围扫描 vs 全表扫描)；

了解数据库本身的一些实现细节 (过滤条件提取；Index Condition Pushdown；Semi-Consistent Read)；

了解死锁产生的原因及分析的方法 (加锁顺序不一致；分析每个SQL的加锁顺序)
————————————————
版权声明：本文为CSDN博主「刘了个牛」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_35105015/article/details/129294685

posted on 2024-01-08 16:12 怦然丶心动阅读(163) 评论(0) 编辑收藏举报

MySQL锁和事务篇

搜索

常用链接

我的标签

随笔分类

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论