MySQL笔记（六）——事务及其ACID特性

InnoDB的逻辑存储结构

支持事务与行级锁定，是InnoDB区别于MyISAM的两大特点。关于事务，需要了解的相关知识很多，涉及到redo日志和undo日志、MySQL自动恢复机制、锁和隔离级别等，各方面的点又相互联系。以前没有深入地去理解过，这一次写这篇笔记，翻了两本书和很多的文章，希望能巩固一下。

在学习事务之前，还是需要再了解一下InnoDB的逻辑存储结构。

InnoDB逻辑存储结构中，处于最顶层的是表空间，对应到物理文件的话，则是在数据目录下的ibdata1这一文件。很多文章都会写道，所有的数据都会存放在这一文件中，所有数据都逻辑地存放于一个表空间中。这一说法并不准确。

表空间可分为独立表空间和共享表空间，顾名思义，独立表空间是指每张表对应一个专属的表空间，共享表空间则是指所有的数据都存在于同一个表空间中，即对应ibdata。使用独立表空间还是共享表空间，是可以通过innodb_file_per_table配置的。

MySQL默认使用独立表空间。可通过以下语句查看是否启用独立表空间。

show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

如果是想要使用共享表空间，先stop掉mysqld进程，再修改配置文件my.cnf，在[mysqld]标签下添加一句：

innodb_file_per_table=0

之后再重启mysqld进程即可。

InnoDB的逻辑存储结构自顶向下分别是表空间、段、区、页，数据是以页为最小单位进行读写，一个数据页默认是16KB。

在使用独立表空间的情况下，我们可以打开一个数据库目录看一看它的文件构成：

从图中可以注意到，每一张表都对应两个文件，分别是.ibd文件和.frm文件。

.ibd文件 用于存放数据和索引信息，逻辑上即数据段和索引段。

.frm文件 用于存放表结构信息。

我在这台机器上还创建了一个mysqld实例，默认使用共享表空间，创建test库、test表，使用InnoDB存储引擎，并向表中插入一些数据，接着再看看test库下的文件构成：

此时，就不会产生以.ibd为后缀的文件，因为在使用共享表空间时，所有表的数据和索引信息都会存放在data目录下的ibdata1文件中，而作为存放表结构信息的.frm后缀的文件依然会产生。

需要特别注意的是，在使用独立表空间时，ibdata1文件并不是就没用了，可以试验一下，往表中进行数据插入后，ibdata1文件依然会增大。这是因为，即使是使用了独立表空间，.ibd文件也只会储存数据、索引和插入缓冲信息等，而回滚信息、插入缓冲索引页、系统事务信息、二次写入缓冲等依然在ibdata1文件中。

表空间是由各个段组成的，常见的段有数据段、索引段和回滚（rollback）段。使用独立表空间时，数据段和索引段是在表对应的.ibd文件中，而回滚段依然会存放在ibdata1文件中。回滚段与这篇笔记接下来要记录的事务息息相关。

每个回滚段（rollback segment）中记录了1024个undo日志段（undo log segment），由于每个事务都要对应一个undo日志，因此每个rollback segment支持1024个在线事务。在InnoDB 1.1之前，只有1个rollback segment，因此只支持1024个在线事务，而现在则有128个rollback segment，在线事务的数量达到了128*1024个。

另外，看到有本书上写到了，MySQL5.6之后支持了指定undo log的位置，可以把undo log分离至独立的表空间中，这一点我还没有具体试验。

页的分配策略

这一点与这篇笔记的关系不太大，但也是我之前一直没搞的一个点，这次查了很多资料又翻了翻书算是明白了一些，稍微做下记录。

在InnoDB逻辑存储结构中，区是由一组连续的页构成，在任何情况下，一个区的大小都是1MB。于是默认情况下，1个区即对应64个连续的页。为了保证数据页的连续性，为一个段分配空间是以区为单位分配的，以这样来想的话，在独立表空间的情况下，创建一张表后，其.ibd文件大小应该至少是1M才对，但创建一张空表后，实际情况是这样的：

可以看到，test.ibd的大小是96K，离我们所预测的1M相去甚远。

这是因为，在一个段刚开始的时候，为了不浪费磁盘空间，系统会先给它分配32个页（书上是称其为碎片页，但是我感觉这个称呼并不准确，容易与碎片混淆）来存放数据，当这32个页使用完后系统才会按区申请磁盘空间。这主要是针对较小的表，在实际业务场景中可能会有很多小表的使用，这样的表往往用不到1M的空间；还有对于undo log segment这样的段，是随着使用增长的，也没有必要在一开始就为其分配1M的磁盘空间。

我参考书上所说做了个小验证。首先重新创建一张test表：

CREATE TABLE `test` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `description` varchar(7000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

description列将类型设置为varchar(7000)，主要是考虑到将这个字段填充满会占用7000B的空间，而一个数据页的大小是16KB，因此两行记录就可以占用1个页面的空间。上文提到了，只有在用完了32页后，系统才会以区为单位来申请磁盘空间存放数据，我们先往test表中插入63行数据，此时就使用了32个数据页。

为了提高试验效率，可以创建一个存储过程来完成插入：

delimiter $$
create procedure p_insert_test(x int)
modifies sql data
begin
set @i = 0;
ins: loop
if @i = x then
leave ins;
end if;
insert into test(description) value(repeat('a', 7000));                                                     
set @i = @i + 1;
end loop ins;
end $$
Query OK, 0 rows affected (0.00 sec)

接下来调用存储过程p_insert_test来插入63行数据：

call p_insert_test(63);

commit;

查看一下test.ibd此时占用空间情况：

 此时空间占用依然不足1M，说明此时空间的分配依然是初始的32个页，而非通过64个连续页的区。再添加一行记录，这32个页面应该就用完了，新的页会使系统按区申请磁盘空间：

call p_insert_test(1);

commit;

再查看一下test.ibd此时占用空间情况：

可以发现，test.ibd文件大小达到了2M，即系统为它分配了两个区的磁盘空间，之后数据的增长都会以64个连续的页区申请磁盘空间，test.ibd文件的大小始终会是1M的整数倍。 

事务

接下来是这篇笔记的正题了。

定义

在基础部分，我们所写的DML语句都只是对于某些表的独立操作。但是在实际业务中，为了完成某一个业务，往往需要对很多表中各种相关数据进行修改，这个过程是需要很多条DML语句才能够完成的，这些DML语句共同构成了一个事务。总结来说，事务主要用于处理操作量大、复杂度高的数据。

特性

事务必须满足4个基本特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），即我们经常会说的事务的ACID特性。

原子性 事务中的所有语句，要么全部执行，要么全部不执行，不能有中间状态；

一致性 一致性可以说是4个特性中最难以理解的了。书面的说法是，数据库从一种状态转变为下一种一致的状态，在事务开始之前和结束以后，数据库的完整性约束没有被破坏。简单来说，就是事务执行过程中的修改动作，必须要满足数据库预定的规则，比如对于一个非唯一键，在事务结束后出现了重复数据，这就是违背了事务的一致性，即将数据库变为了一种不一致的状态；

隔离性 事物之间互不影响；

持久性 事务执行成功后，所有的更改都会准确地记录在数据库中，所做的更改不会丢失。简而言之，就是指内存中的数据顺利写入磁盘。

需要注意的是，只有InnoDB存储引擎支持事务，事务只用于管理DML语句即insert、update以及delete语句。

生命周期及自动提交机制

生性周期

开启事务 start transaction | start；

事务语句 insert、update以及delete语句；

事务结束 提交-commit | 回滚-rollback。

自动提交机制

自动提交

对于DML语句，MySQL会自动开启事务，并会自动提交。

MySQL默认开启了事务的自动提交，可以查看一下具体参数：

select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

在开启自动提交的情况下，当前mysql会话结束后，会话中未提交的事务会自动提交。用上文的test库中的test表来试验一下。

先查看一下test表中原本的数据行数：

select count(*) from test;
+----------+
| count(*) |
+----------+
|      320 |
+----------+

接着用delete语句删除test表中的所有数据：

delete from test;
Query OK, 320 rows affected (0.01 sec)

最后退出当前会话，在重新连接，查看test表中的数据行数：

select count(*) from test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

虽然在执行完delete语句后，并没有commit提交事务，但删除数据的操作确实是生效了。

在生产中，更多情况下我们并不希望事务自动提交，关闭MySQL事务自动提交，主要有以下三种方式：

set @@autocommit=0 设置当前会话禁止自动提交，设置后立即生效；

set global autocommit=0 设置全局禁止自动提交，设置后重新连接生效；

配置文件my.cnf中添加autocommit=0 设置默认禁止自动提交，重启mysqld进程后生效。

这里使用第三种方式，将InnoDB事务自动提交默认禁用。

重启mysqld使配置生效后，我们再到test库中执行上文的p_insert_test存储过程，向test表中添加100条数据：

call p_insert_test(100);
Query OK, 0 rows affected (0.29 sec)

select count(*) from test;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

接着直接退出会话，再重新建立连接，查看test表中数据行数：

select count(*) from test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

由于在退出会话时我们并没有提交事务，并且禁用了自动提交，因此之前的插入数据并没有生效。

隐式提交

还有一些情况，会触发当前事务的隐式提交。

1、同一个会话内，开启两个事务，MySQL会自动提交掉前一个事务；

2、使用set语句设置参数值（无论是设置什么参数），会导致正在执行的事务被提交；

3、所有DDL、DCL语句，都会导致正在执行的事务被提交。

保证事务ACID特性的方法

redo日志和undo日志

相关概念

在具体方法之前，先结合下面的简要图示了解几个基本的概念： 

 redo日志 重做日志，存放于 数据目录/ib_logfile0、数据目录/ib_logfile1 中，MySQL以轮询的方式使用这两个文件；

redo日志缓冲区 内存中操作redo日志的缓冲区；

.ibd文件 开启独立表空间后，用于存储表内的数据和索引。数据的访问都是以页为单位，单个页面默认16KB；

数据缓冲池 内存中操作数据页的缓冲池；

ibdata1文件 共享表空间，存放于 数据目录/ibdata1 中，即便开启独立表空间，所有表的回滚段依然会存放在共享表空间中；

undo日志 回滚日志；

undo日志缓冲区 内存中操作undo日志的缓冲区；

LSN 日志序列号，用于标记数据页和redo日志；

TXID 事务ID，每一个事务开启后，都会产生一个唯一对应的事务ID。

redo日志

即前滚日志，主要用于保证事物的持久性，对原子性和一致性也有一定作用。

作用：

1、记录内存中数据页变化的过程；

2、在数据库故障或断电后的自动恢复中对数据页进行前滚；

3、实现数据的持久化。

 undo日志

即回滚日志，主要用于保证事务的原子性，对持久性和隔离性也有一定作用。

作用：

1、记录内存中数据行修改之前的内容；

2、在数据库故障或断电后的自动恢复中对未提交的事务进行回滚；

3、实现一致性快照，配合隔离级别保证多版本并发控制（MVCC）、实现读和写操作不会相互阻塞。

 

需要注意的是，redo日志记录的是数据页的变化，而undo日志记录的是数据行的状态。

工作流程

如果要执行update语句更新表中的一行记录，我们来设想一下MySQL的大致运行流程：

1、找到目的记录所在的数据页；

2、将整个数据页（16KB）读入内存；

3、在内存中修改目的记录；

4、将修改后的数据页重新写入磁盘。

但是这样的工作流程，在应对具有大量修改语句的事务时就会出现很多的问题。可以结合上文的简图来看。

每次更新记录都需要读写16KB的数据页，而对于由大量修改操作的事务来说，每修改一次后就向磁盘中写入一次显然是低效的。于是在内存空间中引入了数据缓冲池（buffer pool），事务提交后再在某一时机将所有修改后的数据页统一写入磁盘。但是，大量的数据页向磁盘写入依然是一个耗时很长的过程，对于io的长期占用也会降低系统io性能，如果在写入磁盘的过程中发生了故障、断电，则会引起数据的丢失，打破事务的持久性。为此，引入了redo日志。

面对具有大量修改语句的事务，重新理一下数据更新的流程：

1、事务开始后，产生一个对应的txid；

2、对于事务中的每条修改语句：

i）找到目的记录所在的数据页

ii）将页面读入内存区的数据缓冲池（buffer pool）中

iii）将目的记录以及txid通过undo日志缓冲区写入磁盘中的undo日志（undo log buffer -> ibdata1 file -> rollback segment -> undo log segment）

iv）对目的记录进行修改，并更新数据页的LSN

v）将对数据页的修改过程以及LSN记录到redo日志缓冲区（redo log buffer）中

3、事务提交（commit）后，将redo buffer中的日志以及txid写入磁盘中的redo日志文件（ib_logfile0~1）中；

4、当buffer pool中的数据量达到预设的阈值后，再开始将修改过的数据页写回磁盘。

由于redo日志只会记录数据页修改的过程而非整个数据页的内容，因此redo日志的大小是远远小于缓冲池中数据页的大小的，将redo日志写入磁盘是一个很快速的过程。

需要注意两个重点：

1、日志优先于数据写入磁盘；

2、事务提交是指将日志写入磁盘而非将数据写入磁盘。

故障恢复

redo日志和undo日志对系统故障、异常断电后的数据恢复有着很大的作用。这里的故障发生时机主要有两个，分别是事务提交后、数据页未写入磁盘，和事务提交前。

事务提交后

由于事务提交后，redo日志会优先写入磁盘，内存中数据缓冲池里的数据页并不会立即写入磁盘，所以在事务提交后、系统发生故障，可能会出现内存数据页的丢失。

上文说过，磁盘中的数据页和redo日志都保存有LSN。MySQL在启动时，会先对比数据页与对应redo日志中的LSN，如果不一致，将会触发故障断电自动恢复。由于出现故障前事务已经提交，则此时redo日志中的LSN应该比数据页中的LSN要新。MySQL会将LSN不一致的数据页和redo日志从磁盘中调到内存，根据redo日志中记录的数据页变化过程对数据页中的数据进行更新，直到数据缓冲池（Buffer pool）中的数据页都已经恢复到上次提交后的状态，再更新数据页的LSN。至此，内存中的数据页已经恢复到上次提交之后，MySQL才能够正常启动。

事务提交前

事务提交前，系统发生故障、断电的情况又要比事务提交后复杂一些。我们认为，一个事务如果还未提交，那么系统重启后，相关的数据状态应该依然保持事务执行前的状态，即未提交事务执行期间的修改不应生效。

我们首先要知道，redo日志在什么情况下会被写入磁盘文件：

1、当前事务执行完毕，事务commit后；

2、其他并发事务commit，会触发当前事务到此时已产生的redo日志也被一并写入磁盘文件。

于是，在事务提交前发生故障断电，又可以分为两种情况。

redo日志未写入磁盘

redo日志如果还没有写入磁盘，由于日志优先写我们可以知道，此时修改的数据页也没有写入磁盘，磁盘中的数据页和redo日志都还是事务开始前的内容。因此在MySQL启动时，磁盘中数据页和redo日志的LSN应该一致，MySQL正常启动即可。

redo日志已写入磁盘

如果当前事务的redo日志因为其他并发事务的commit而被一并写入磁盘文件，那么MySQL重新启动后，肯定会触发上文中提到的事务提交后数据恢复的流程，MySQL会将LSN不一致的数据页和redo日志调入内存，用redo日志来前滚数据页。但是因为系统故障之前，这一事务并未提交，这个事务执行中带来的数据修改也不应生效。这时，txid就该发挥作用了。

上文提到过，随着事务的开始，会产生一个相应的txid，最后也会被写入redo日志和undo日志保存。在被写入redo日志文件时，还会记录redo日志被写入磁盘文件时，它当中txid对应的事务是否已被提交。我们继续上一段落，此时内存中的数据页已被redo日志恢复到修改后的内容，MySQL又会进一步检查redo日志中记录的这一事务是否提交，如果没有提交，又会拿出这一事务的txid，将对应的undo日志调入内存，用undo日志中记录的数据行在修改之前的状态来对数据页进行回滚。之后MySQL才会正常启动。

简单总结一下，redo日志用于保证事务提交后能顺利持久化到磁盘，在数据恢复中负责将过时的数据页前滚到事务提交后的状态，确保了事务的持久性；undo日志用于保证未提交的事务能够恢复到被修改之前的状态，在数据恢复中负责将无效的修改回滚到事务开始前的状态，确保了事务的原子性。

锁

概述

作用 锁机制用于管理对共享资源的并发访问，保证数据的完整性和一致性。

特点 MySQL使用插件式的存储引擎，对于不同存储引擎，其所支持的表机制也各不相同。MyISAM和Memory引擎采用的是表级锁，BDB引擎采用的是页面锁，但也支持表级锁，而InnoDB引擎则是默认采用行级锁，同时也支持表级锁。

锁定粒度 指锁定的对象范围。锁用来锁定的对象是数据库中的表、页、行，其中表级锁的锁定粒度最大，行级锁的锁定粒度最小。

锁定粒度越大，发生锁冲突的概率自然越高、并发度越低。因此，表级锁更适用于查询为主的业务场景，行级锁更适用于有大量安索引条件并发更新少量不同数据、同时又有并发查询的业务场景。

本文主要记录InnoDB引擎的锁机制。

InnoDB锁的类型

InnoDB引擎支持两种标准的行级锁：

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

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

简要说明一下，当一个事务对T表中的数据行r加上了S锁，则其他并发事务依然可以读取T表中的r行，但不能对其进行修改；当有事务想要更新r行，则需要对其加上X锁，如果此时已有其他事务获取了r行的X锁，则当前进程等待；r行一旦被加上X锁，其他并发事务便不可再对其加上S锁和X锁了。

此外，InnoDB引擎支持行级锁和表级锁共存。

设想这种情况，当表T的r行被事务A加上了X锁，事务A将要更新r行，同时，事务B又对表T加上了表级锁，不允许其他并发事务对表T进行修改。这样一来，共同存在的行级锁和表级锁自然就陷入了矛盾的状态了。为了解决高粒度锁与低粒度锁的矛盾、实现行级锁与表级锁的共存，InnoDB又引入了一种表级锁，即意向锁。意向锁又可分为两类：

意向共享锁（IS锁） 事务在给数据行加共享锁前，必须先取得该表的意向共享锁；

意向排它锁（IX锁） 事务在给数据行加排它锁前，必须先取得该表的意向排它锁。

事务如果要对r行上锁，由于行是最细粒度，在此之前还需要为最高粒度的对象上锁，即r行所在的表T。

由于InnoDB支持的是行级锁，而意向锁的设计意图，其实仅仅是为了揭示事务中要请求的下一行的锁的类型，因此，意向锁并不会阻塞除了全表扫描以外的任何请求。各种锁模式之间的兼容情况如下表所示：

意向锁有InnoDB自动添加，不需要用户干预。

对于insert、update和delete这样的修改数据的语句，InnoDB会自动给涉及数据加上X锁；对于普通select语句，由于一致性非锁定读机制的存在，InnoDB不会为其加任何锁。

锁的查看

information_schema库中，有三个与InnoDB锁相关的表，分别是innodb_trx表、innodb_locks表和innodb_lock_waits表。接下来用一张test表，来熟悉一下这三张表。

在先打开一个shell，启动mysql客户端，对上文中使用过的test表中id=101的行进行修改，修改结束后不提交事务：

update test set description='testlock' where id=101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

由上文可知，InnoDB会自动为test表中id=101的数据行加上X锁。此时，再起一个shell，登录mysql客户端，再次尝试修改这行数据：

update test set description='testlock1' where id=101;

此时shell中的mysql客户端会陷入对X锁的等待，这时再到第一个shell中查看与InnoDB锁相关的三张表的状态。

innodb_trx

select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 8483
                 trx_state: LOCK WAIT
               trx_started: 2020-02-16 22:55:58
     trx_requested_lock_id: 8483:110:5:4
          trx_wait_started: 2020-02-16 22:57:46
                trx_weight: 2
       trx_mysql_thread_id: 310
                 trx_query: update test set description='testlock1' where id=101
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 8482
                 trx_state: RUNNING
               trx_started: 2020-02-16 22:52:00
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 246
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

 显然，这张表记录的是当前系统中发生行锁争用的事务信息，主要关注trx_id、trx_state、trx_query这几列，另外，trx_isolation_level列表示事务的隔离级别，下一节将会展开讨论。

通过查询结果可以知道，事务id为8482的事务正在运行中，而id为8483的事务则陷入了锁等待，在其获取了X后则会执行trx_query列的SQL语句。

innodb_locks

select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
    lock_id: 8483:110:5:4
lock_trx_id: 8483
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: PRIMARY
 lock_space: 110
  lock_page: 5
   lock_rec: 4
  lock_data: 101
*************************** 2. row ***************************
    lock_id: 8482:110:5:4
lock_trx_id: 8482
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: PRIMARY
 lock_space: 110
  lock_page: 5
   lock_rec: 4
  lock_data: 101
2 rows in set, 1 warning (0.00 sec)

这张表记录了当前系统中锁的信息，包括还在等待中的事务请求的锁。

innodb_lock_waits

select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 8483
requested_lock_id: 8483:110:5:4
  blocking_trx_id: 8482
 blocking_lock_id: 8482:110:5:4
1 row in set, 1 warning (0.00 sec)

这张表则存放了正持有锁的事务与等待锁的事务的id。

InnoDB行锁算法

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果建表时没有指定索引列，InnoDB则会通过隐藏的聚簇索引来对记录加锁。

InnoDB引擎有三种锁的算法，分别是：

Record lock 对索引项加锁；

Gap lock 对索引项之间的间隙、第一条记录前的间隙或最后一条记录后的间隙加锁；

Next-Key lock 前两种的组合，对记录及其前面的间隙加锁。

gap lock和next-key lock锁定的都是一个范围内的记录，当隔离级别为read committed时，某一数据行被加锁后，其索引值以及它与相邻行的索引值之间间隔的值，都会被加上锁，这就是next-key lock，主要用于防止幻读。当查询的索引含有unique属性时，InnoDB会自动将next-key lock降为record lock，因此，使用主键检索时，锁定算法将会是record lock。

需要注意的是，行锁的实现与索引息息相关，如果在修改数据时不通过索引来检索，InnoDB则会对表中所有的行加锁，这样也就相当于对全表加锁了，会导致大量的并发冲突，影响事务并发性能。 

隔离级别

一致性问题

上文说到的redo日志和undo日志，分别是主要保证事务的持久性和原子性，而锁和事务的隔离级别则主要保证了事务的一致性和隔离性。隔离级别的实现依赖于锁，而锁又与undo日志有所联系。

先了解几种事务的一致性问题：

脏读 当一个事务执行的过程中，对某些记录行进行了修改，在事务提交之前，这些数据行的修改还在内存中，未持久化至磁盘，这时，就出现了内存中的数据页与磁盘中的数据页不一致的情况，内存中被修改过的数据页被称作脏页，修改过的数据行称为脏数据。如果这时有另一个并发事务读取了脏数据，并将脏数据应用到了之后的业务场景中，则产生了未提交的数据依赖关系；

不可重复读 一个事务在读取某些数据后的某个时间，再次读取之前读过的数据，却发现数据已经被修改或是删除；

幻读 之前看了很多文章对幻读的解释都有很大的问题，可见很多人都没把这个概念真正弄懂。主要需要区分幻读与不可重复读，不可重复读是指再次读取之前读取过的数据行时发现该数据行已被修改或删除，而幻读则是指读取之前读过的数据行时却发现其他事务插入了满足这一查询条件的新数据，一个是update/delete，一个是insert，要注意区分。

事务隔离级别

脏读、不可重复读和幻读，这些都是数据库读一致性问题。SQL定义了四种标准的隔离级别，分别是READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE，InnoDB引擎默认的隔离级别是REPEATABLE READ。

查看隔离级别

select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

修改隔离级别

1、修改当前会话或全局事务的隔离级别，重连后生效：

set [global] transaction isolation level { read uncommitted | read committed | repeatable read | serializable };

2、设置默认的隔离级别：

修改配置文件my.cnf，在mysqld标签下添加transaction-isolation={ read-uncommitted | read-committed | repeatable-read | serializable }，重启mysqld后生效。

READ UNCOMMITTED

未提交读，也称为可脏读。这是事务隔离级别中最低的级别，只能保证不读取物理上损坏的数据，会出现脏读、不可重复读和幻读的问题，在实际生产环境中很少使用。

READ COMMITTED

已提交读。这是语句级的事务隔离级别，并发事务读取的数据只能是其他事务已提交的修改。这种隔离级别能够保证避免脏读，但是无法避免不可重复读和幻读。

REPEATABLE READ

可重复读，这是InnoDB引擎默认的隔离级别。 利用多版本并发控制（MVCC）解决了不可重复读问题，依然会出现幻读的情况，但可以通过Next-key lock解决。

1、多版本并发控制

上文提到过，undo日志不仅保证了事务的原子性，同时对持久性和隔离性也有一定作用，其实主要就体现在多版本并发控制这一点。因为undo日志会记录数据修改之前的状态，可以利用undo日志对数据行之前某一时间点的状态生成一份一致性数据快照，即数据行之前版本的数据。由于每行数据可能有多个时间的历史版本，所以将这种技术称为行多版本技术，由此带来的并发控制，则称为多版本并发控制（MVCC）。

一致性非锁定读

在上文锁的部分提到了，读取数据需要对目的数据行加上S锁，修改数据则需要对目的数据行加上X锁。但是在实际使用中我们知道，InnoDB会自动为update、delete、insert的行加上X锁，但是select语句却不需要任何锁，同时也不会被X锁阻塞。这是因为select语句并不是直接去读取数据行，而是去读取目的行的一个快照，由于undo日志是用来在事务中对数据进行回滚的，因此不会有修改历史数据的情况，对数据快照的读取也就没有必要上锁了。

一致性锁定读

既然有一致性非锁定读，那想必也有一致性锁定读了，不然S锁就完全没了用武之地。在某些场景，可能需要保证读取数据逻辑的一致性，在读取数据时便不允许其他并发事务对数据行进行修改或是读取了，因此，InnoDB引擎也对select语句支持两种一致性锁定读的操作：

select ... for update 这样会对select语句读取的数据行加上一个X锁，排斥其他事务的S锁和X锁；

select ... lock in share mode 这样会对select语句读取的数据行加上一个S锁，排斥其他事务的X锁。  

由于有数据快照的存在，对于被加上了读锁的数据，普通的select语句依然不会被阻塞，它还是能够去读取该数据行的历史版本。

在不同的存储引擎甚至是不同的事务隔离级别中，对于快照数据的定义也可能是不同的。

在READ COMMITTED级别中，非锁定读总是会读取被锁定行的最新一份数据快照，这也是这一隔离级别无法实现可重复读的原因；

而REPEATABLE COMMITTED级别中，非锁定读总是会去读取事务开始时的数据行版本，这就保证了可重复读，而如果在这一级别下想读取最新的行，只需要在会话中commit一下即可。

在REPEATABLE READ级别下依然有可能出现幻读，但是可以通过next-key lock来避免。通过上文知道，next-key lock锁定的对象是索引，它会同时锁住目的数据行的索引，以及目的行索引值到下一行的索引值间隔的索引值，以此来避免并发事务插入满足检索条件的新数据。我们可以试验一下：

创建test_isolation表：

create table test_isolation(
i1 int not null,
i2 int not null,
k1 varchar(10) not null
) engine innodb charset utf8mb4;

alter table test_isolation add index idx(i1);

insert into test_isolation
values
(1, 1, 'test1'), 
(2, 2, 'test2'), 
(5, 5, 'test5'), 
(6, 6, 'test6'), 
(7, 7, 'test7'), 
(10, 10, 'test10'); 

在会话A中将所有i1>2的行的k1列值改为‘test’，但不要提交：

update test_isolation set k1='test' where i1>2;

再到会话B插入i1=3的数据行：

insert into test_isolation value(3, 3, 'test3');

此时，这行语句陷入等待，在会话A中的事务提交之前，其他并发事务都不能在事务A执行中的语句的索引检索范围内插入数据行，这样即避免了幻读现象的发生。

SERIALIZABLE

可序列化。这是事务级的隔离级别，不会出现脏读、不可重复读和幻读这些一致性问题，但是这相当于将事务串行化进行，牺牲了事务的并发性。