MySQL探秘(四)MySQL事务与锁详解

1.什么是事务

 

1.1 事务的定义

 

事务（Transaction）是数据库管理系统(DBMS)执行过程中的一个逻辑单位，由 一个有限的数据库操作序列构成；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。作为一个关系型数据库，MySQL支持事务。

这里面有两个关键点，第一个，它是数据库最小的工作单元，是不可以再分的。第 二个，它可能包含了一个或者一系列的 DML 语句，包括 insert delete update。(单条 DDL(create drop)和 DCL(grant revoke)也会有事务)

 

1.2 哪些存储引擎支持事务

 

我们前面说到了mysql是由server层和存储引擎构成，server层不管理事务，事务是由存储引擎实现的。MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛，这个也是它成为默认的存储引擎 的一个重要原因；其他存储引擎不支持事务，如MyIsam、Memory等

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

 

1.3事务的四大特性

 

ACID是衡量事务的四个特性：

• 原子性（Atomicity，或称不可分割性）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

按照严格的标准，只有同时满足ACID特性才是事务；但是在各大数据库厂商的实现中，真正满足ACID的事务少之又少。例如MySQL的NDB Cluster事务不满足持久性和隔离性；InnoDB默认事务隔离级别是可重复读，不满足隔离性；Oracle默认的事务隔离级别为READ COMMITTED，不满足隔离性……因此与其说ACID是事务必须满足的条件，不如说它们是衡量事务的四个维度。

下面将详细介绍ACID特性及其实现原理；为了便于理解，介绍的顺序不是严格按照A-C-I-D。

 

1.3.1.原子性

 

1. 定义

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。

2. 实现原理：undo log

在说明原子性原理之前，首先介绍一下MySQL的事务日志。MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。

下面说回undo log。实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。

 

1.3.2.一致性

 

1. 基本概念

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。

2. 实现

可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。

实现一致性的措施包括：

• 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
• 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
• 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

 

1.3.3.隔离性

 

1. 定义

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰，可以防止多个事务并发执行时由于交叉执行而导致数据的不一致（比如：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）。

事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

2. 实现

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作)，那么隔离性的探讨，主要可以分为两个方面：

• (一个事务)写操作对(另一个事务)写操作的影响：LBCC(基于锁的并发控制)保证隔离性
• (一个事务)写操作对(另一个事务)读操作的影响：MVCC(多版本的并发控制)保证隔离性

 

1.3.4.持久性

 

1. 定义

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

2. 实现原理：redo log

redo log和undo log都属于InnoDB的事务日志。下面先聊一下redo log存在的背景。

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

 

1.4如何开启事务

 

无论是我们在 Navicat 的这种工具里面去操作，还是在我们的 Java 代码里面通过 API 去操作，还是加上@Transactional 的注解或者 AOP 配置，其实最终都是发送一个 指令到数据库去执行，Java 的 JDBC 只不过是把这些命令封装起来了。

我们先来看一下我们的操作环境。版本(5.7)，存储引擎(InnnoDB)，事务隔离级别(RR)。

查询数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

查询数据库引擎

mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

查询事务级别

mysql> show global variables like "tx_isolation";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

执行这样一条更新语句的时候，它有事务吗?

update user set name = 'wangwu' where id=1;

实际上，它自动开启了一个事务，并且提交了，所以最终写入了磁盘。 这个是开启事务的第一种方式，自动开启和自动提交。
InnoDB 里面有一个 autocommit 的参数(分成两个级别， session 级别和 global级别)。

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

它的默认值是 ON。autocommit 这个参数是什么意思呢?是否自动提交。如果它的 值是 true/on 的话，我们在操作数据的时候，会自动开启一个事务，和自动提交事务。

否则，如果我们把 autocommit 设置成 false/off，那么数据库的事务就需要我们手 动地去开启和手动地去结束。

手动开启事务也有几种方式，一种是用 begin;一种是用 start transaction。

那么怎么结束一个事务呢?我们结束也有两种方式，第一种就是提交一个事务， commit;还有一种就是 rollback，回滚的时候，事务也会结束。还有一种情况，客户端 的连接断开的时候，事务也会结束。

后面我们会讲到，当我们结束一个事务的时候，事务持有的锁就会被释放，无论是 提交还是回滚。

我们用 begin 手工开启一个事务，执行第二个 update，但是数据没有写入磁盘，因 为事务还没有提交，这个时候 commit 一下，再刷新一下，OK，写入了。

这个就是我们开启和结束事务的两种方式。

 

1.5事务并发的问题

 

首先来看并发情况下，读操作可能存在的三类问题：

（1）脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。举例如下（以账户余额表为例）：

我们有两个事务，一个是 Transaction A，一个是 Transaction B，在第一个事务里 面，它首先通过一个 where id=1 的条件查询一条数据，返回 name=lisi，balance=100 的这条数据。然后第二个事务，它同样地是去操作 id=1 的这行数据，它通过一个 update 的语句，把这行 id=1 的数据的 balance 改成了 200，但是注意，它没有提交。

这个时候，在第一个事务里面，它再次去执行相同的查询操作，发现数据发生了变 化，获取到的数据 balance 变成了 200。那么，这种在一个事务里面，由于其他的时候修改了 数据并且没有提交，而导致了前后两次读取数据不一致的情况，这种事务并发的问题， 我们把它叫做脏读。

（2）不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。举例如下：

（3）幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。举例如下：

在第一个事务里面我们执行了一个范围查询，这个时候满足条件的数据只有一条。 在第二个事务里面，它插入了一行数据，并且提交了。重点:插入了一行数据。在第一 个事务里面再去查询的时候，它发现多了一行数据。这种情况，我们把它叫做什么呢?

一个事务前后两次读取数据数据不一致，是由于其他事务插入数据造成的，这种情况我们把它叫做幻读。

不可重复读是修改或者删除，幻读是插入。

小结:我们刚才讲了事务并发带来的三大问题，现在来给大家总结一下。无论是脏 读，还是不可重复读，还是幻读，它们都是数据库的读一致性的问题，都是在一个事务 里面前后两次读取出现了不一致的情况。

读一致性的问题，必须要由数据库提供一定的事务隔离机制来解决。就像我们去饭店吃饭，基本的设施和卫生保证都是饭店提供的。那么我们使用数据库，隔离性的问题 也必须由数据库帮助我们来解决。

 

1.6事务隔离级别

 

SQL92标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：

我们详细地分析一下这 4 个隔离级别是怎么定义的。
第一个隔离级别叫做:Read Uncommitted(未提交读)，一个事务可以读取到其

他事务未提交的数据，会出现脏读，所以叫做 RU，它没有解决任何的问题。 第二个隔离级别叫做:Read Committed(已提交读)，也就是一个事务只能读取

到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题， 但是会出现不可重复读的问题。

第三个隔离级别叫做:Repeatable Read (可重复读)，它解决了不可重复读的问题， 也就是在同一个事务里面多次读取同样的数据结果是一样的，但是在这个级别下，没有 定义解决幻读的问题。

最后一个就是:Serializable(串行化)，在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。

在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）。

 

1.7MySQL InnoDB 对隔离级别的支持

 

在 MySQL InnoDB 里面，不需要使用串行化的隔离级别去解决所有问题。那我们来 看一下 MySQL InnoDB 里面对数据库事务隔离级别的支持程度是什么样的。

2.事务隔离实现方案

 

LBCC

第一种，我既然要保证前后两次读取数据一致，那么我读取数据的时候，锁定我要 操作的数据，不允许其他的事务修改就行了。这种方案我们叫做基于锁的并发控制 Lock Based Concurrency Control(LBCC)。

如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率。

MVCC

所以我们还有另一种解决方案，如果要让一个事务前后两次读取的数据保持一致， 那么我们可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照 就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control (MVCC)。

MVCC 的核心思想是: 我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

 

2.1基于锁的并发控制 (LBCC)

 

2.1.1锁的粒度

 

按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。

表锁在操作数据时会锁定整张表，并发性能较差；

行锁则只锁定需要操作的数据，并发性能好。

但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

 

2.1.2锁的分类

第一个行级别的锁就是我们在官网看到的 Shared Locks (共享锁)，我们获取了 一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁，注意不要在加上了读锁 以后去写数据，不然的话可能会出现死锁的情况。而且多个事务可以共享一把读锁。

那怎么给一行数据加上读锁呢?
我们可以用 select …… lock in share mode; 的方式手工加上一把读锁。 释放锁有两种方式，只要事务结束，锁就会自动事务，包括提交事务和结束事务。 我们也来验证一下，看看共享锁是不是可以重复获取。

建表如下

CREATE TABLE `user` (
`id` int(11) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into user values(NULL,'zhangsan'); 
insert into user values(NULL,'lisi');

 

#Transaction 1 

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;


Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

#Transaction 2 

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;


Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

由上结果可以看出共享锁是可以重复获取的。

排他锁（X Lock）

允许事务删除或更新一行数据

第二个行级别的锁叫做 Exclusive Locks(排它锁)，它是用来操作数据的，所以又 叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。
排它锁的加锁方式有两种，第一种是自动加排他锁。我们在操作数据的时候，包括增删改，都会默认加上一个排它锁。
还有一种是手工加锁，我们用一个 FOR UPDATE 给一行数据加上一个排它锁，这个 无论是在我们的代码里面还是操作数据的工具里面，都比较常用。
释放锁的方式跟前面是一样的。

#Transaction 1 

begin; 
update user set name='zs' where id = 1;


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

#Transaction 2 

begin; 
select * from user where id = 1 LOCK IN SHARE MODE; //BLOCKED

SELECT * FROM user where id=1 FOR UPDATE; // BLOCKED 

DELETE FROM user where id=1 ; // BLOCKED

以上结果可以看出当一个事务用排他锁锁住了一行数据的时候，其他的事务不能操作这一行数据。

由此得出行级锁的共享锁(S Lock)和排他锁(X Lock)的兼容性如下：（需要注意的是，这里的S锁和X锁都是行级锁，兼容性是指对同一记录锁的兼容情况）

.	排他锁（X Lock）	共享锁（S Lock）
排他锁（X Lock）	不兼容	不兼容
共享锁（S Lock）	不兼容	兼容

意向锁

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突

意向锁之间是相互兼容的：

举个例子验证下

#Transaction 1

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;--这行加了S锁，这张表加了IS锁

select * from user where id = 1 for update;--这行加了X锁，这张表加了IX锁


Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
+----+------+
1 row in set (0.00 sec)

#Transaction 2

begin; 
select * from user where id = 2 LOCK IN SHARE MODE;--这行加了S锁，这张表加了IS锁

select * from user where id = 2 for update;--这行加了X锁，这张表加了IX锁


Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  2 | lisi |
+----+------+
1 row in set (0.00 sec)

由上可以看出事务1锁定了id为1的行(对这行加了S锁，或者X锁)，同时加了表的IS锁，或者IX锁。事务2当然也可以锁定id为2的行(对这行加了S锁，或者X锁，和前面的不是同一个行锁)，所以事务2肯定也是可以获得表的IS锁或者IX锁的。

意向锁和表锁(表级的X，S)兼容性如下

#Transaction 1

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;--这行加了S锁，这张表加了IS锁

Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
+----+------+
1 row in set (0.00 sec)

#Transaction 2

begin; 
select * from user LOCK IN SHARE MODE;--这张表加了IS锁

Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | lisi |
+----+------+
2 rows in set (0.00 sec)

事务1已经获得了IS锁，想要读取某行数据，事务2想要获得表的S锁，可以获得成功，因为读是兼容的.

第二个例子

#Transaction 1

begin; 
select * from user where id = 1 for update;--这行加了X锁，这张表加了IX锁

Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
+----+------+
1 row in set (0.00 sec)

#Transaction 2

begin; 
select * from user for update;--这张表尝试加X锁  BLOCKED

事务1获得了表的IX锁，想要修改某行数据，事务2想要获得表的X锁，但是由于IX锁已被获取走，证明有其他事务正在修改某行数据，所以事务2获得失败，只能被塞住…

以上就是 MySQL 里面的 4 种基本的锁的模式，或者叫做锁的类型。

到这里我们要思考两个问题，首先锁的作用是什么?它跟 Java 里面的锁是一样的， 是为了解决资源竞争的问题，Java 里面的资源是对象，数据库的资源就是数据表或者数 据行。
所以锁是用来解决事务对数据的并发访问的问题的。

2.2 多版本的并发控制(MVCC)

 

所以我们还有另一种解决方案，如果要让一个事务前后两次读取的数据保持一致， 那么我们可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照 就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control (MVCC)。

MVCC 的核心思想是: 我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

问题:这个快照什么时候创建?读取数据的时候，怎么保证能读取到这个快照而不 是最新的数据?这个怎么实现呢?

InnoDB 为每行记录都实现了3个隐藏字段:

DB_ROW_ID，6 字节：行标识,单调递增的行ID。（如果没有明确定义聚集索引，那么会自动生成一个聚集索引，这个时候自动生成的聚集索引的值就是DB_ROW_ID）

DB_TRX_ID，6 字节:插入或更新行的最后一个事务的事务 ID，事务编号是自动递 增的(我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事 务 ID)。

DB_ROLL_PTR，7 字节:回滚指针，指向回滚段中的一个undo log记录(我们把它理解为删除版本号，数据被删除或记录为旧数据的时候，记录当前事务 ID)。

id	name	DB_ROW_ID	DB_TRX_ID	DB_ROLL_PTR
1	zhangsan	1	01	undefined

我们把DB_TRX_ID, DB_ROLL_PTR理解为版本号。

下面来举个例子

1.第一个事务，初始化数据(检查初始数据)

#Transaction 1

begin;
insert into user values(NULL,'zhangsan'); 
insert into user values(NULL,'lisi'); 
commit;

此时的数据，创建版本是当前事务ID为01，删除版本为空:

id	name	DB_TRX_ID(创建版本)	DB_ROLL_PTR(删除版本)
1	zhangsan	01	undefined
2	lisi	01	undefined

2.第二个事务，执行第 1 次查询，读取到两条原始数据，这个时候事务ID是 02:

#Transaction 2

begin;
select * from user; -- (1) 第一次查询


mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

3.第三个事务，插入数据:

#Transaction 3

begin;
insert into user values(NULL,'wangwu');
commit;

此时的数据，多了一条 wangwu，它的创建版本号是当前事务ID为03:

id	name	DB_TRX_ID(创建版本)	DB_ROLL_PTR(删除版本)
1	zhangsan	01	undefined
2	lisi	01	undefined
3	wangwu	03	undefined

4.第二个事务，执行第 2 次查询:

#Transaction 2

select * from user; --(2) 第二次查询

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

MVCC 的查找规则:只能查找创建时间小于等于当前事务ID的数据，和删除时间大
于当前事务ID的行(或未删除)。 也就是不能查到在我的事务开始之后插入的数据，wangwu 的创建事务ID大于 02，所以还是只能查到两条数据。

5.第四个事务，删除数据，删除了 id=2 lisi 这条记录:

#Transaction 4

begin;
delete from user where id=2;
commit

此时的数据，lisi 的删除版本被记录为当前事务ID 04，其他数据不变:

id	name	DB_TRX_ID(创建版本)	DB_ROLL_PTR(删除版本)
1	zhangsan	01	undefined
2	lisi	01	04
3	wangwu	03	undefined

6.在第二个事务中，执行第 3 次查询:

#Transaction 2

select * from user; --(3) 第三次查询

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

查找规则:只能查找创建时间小于等于当前事务ID的数据，和删除时间大于当前事
务ID的行(或未删除)。
也就是，在我事务开始之后删除的数据，所以 lisi 依然可以查出来。所以还是这两
条数据。

7.第五个事务，执行更新操作，这个事务ID是05:

#Transaction 5

begin;
update user set name ='zhaoliu' where id = 1;
commit;

此时的数据，更新数据的时候，旧数据的删除版本被记录为当前事务ID 05(undo)， 产生了一条新数据，创建事务ID为当前事务ID 05:

id	name	DB_TRX_ID(创建版本)	DB_ROLL_PTR(删除版本)
1	zhangsan	01	05
2	lisi	01	04
3	wangwu	03	undefined
1	zhaoliu	05	undefined

8.第二个事务，执行第 4 次查询:

#Transaction 2

select * from user; --(4) 第四次查询
mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

查找规则:只能查找创建时间小于等于当前事务ID的数据，和删除时间大于当前事
务ID的行(或未删除)。
因为更新后的数据 zhaoliu 创建版本大于 02，代表是在事务之后增加的，查不出
来。
而旧数据 zhangsan 的删除版本大于 2，代表是在事务之后删除的，可以查出来。
通过以上演示我们能看到，通过版本号的控制，无论其他事务是插入、修改、删除， 第一个事务查询到的数据都没有变化。
在 InnoDB 中，MVCC 是通过 Undo log 实现的。
Oracle、Postgres 等等其他数据库都有 MVCC 的实现。
需要注意，在 InnoDB 中，MVCC 和锁是协同使用的，这两种方案并不是互斥的。

 

2.3行锁的原理

顾名思义，行锁就是一锁锁一行或者多行记录，mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引。

1 没有索引的表

t1表结构如下

CREATE TABLE `t1` ( 
`id` int(11), 
`name` varchar(255) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t1 values(1,'zhangsan'); 
insert into t1 values(2,'lisi'); 
insert into t1 values(3,'wangwu');

1.第一个事务里面，我们通过 where id =1 锁住第一行数据。

#Transaction 1

begin;
select * from t1 where id = 1 for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面，我们尝试给 id=3 的这一行数据加锁,也尝试新增一条记录。

#Transaction 2

begin; 

select * from t1 where id = 3 for update;--BLOCKED
insert into t1 values(4,'zhaoliu');--BLOCKED

结果是给 id=3 的这一行数据加锁,和新增一条记录都阻塞了。

备注原理：如果用没有索引的数据，其会对所有聚簇索引上都加上 next-key 锁。

平常开发的时候如果对查询条件没有索引的，一定进行一致性读，也就是加锁读，会导致全表加上索引，会导致其他事务全部阻塞，数据库基本会处于不可用状态。

2主键索引的表

t2表结构如下

CREATE TABLE `t2` ( 
`id` int(11) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t2 values(1,'zhangsan'); 
insert into t2 values(2,'lisi'); 
insert into t2 values(3,'wangwu');

1.第一个事务里面，我们通过 where id =1 锁住第一行数据。

#Transaction 1

begin;
select * from t2 where id = 1 for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面，我们尝试给 id=1 的这一行数据加锁,也尝试id=3的这一行数据加锁。

#Transaction 2

begin;
select * from t2 where id = 1 for update;--BLOCKED

select * from t2 where id = 3 for update;--OK

结果是给 id=1 的这一行数据加锁阻塞了,给 id=3 的这一行数据加锁正常。

3 唯一索引的表

t3表结构如下

CREATE TABLE `t3` ( 
`id` int(11),
`name` varchar(255) DEFAULT NULL,
 UNIQUE KEY nameindex(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t3 values(1,'zhangsan'); 
insert into t3 values(2,'lisi'); 
insert into t3 values(3,'wangwu');

1.第一个事务里面，我们通过 name = ‘zhangsan’锁住第一行数据。

#Transaction 1

begin;
select * from t3 where name = 'zhangsan' for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面，我们尝试给name = ‘zhangsan’的这一行数据加锁,也尝试id=1的这一行数据加锁。

#Transaction 2

begin;
select * from t3 where name = 'zhangsan' for update;
--BLOCKED

select * from t3 where id = 1 for update;--BLOCKED

结果是给name = ‘zhangsan’的这一行数据和id=1的这一行数据加锁都阻塞了。

备注原理：唯一索引由于明确了唯一的数据行，所以不需要添加间隙锁解决幻读。

4 非唯一索引的表

t4表结构如下

CREATE TABLE `t4` ( 
`id` int(11),
`name` varchar(255) DEFAULT NULL,
 KEY nameindex(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t4 values(1,'111');

1.第一个事务里面，我们通过 name = ‘zhangsan’锁住第一行数据。

#Transaction 1

begin;
select * from t4 where name = '111' for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | 111      |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面，我们尝试新增一条数据

#Transaction 2

begin;
insert into t4 values(2,'112'); --BLOCKED

结果是给name = ‘112’的这一行数据加锁阻塞了

备注原理：发现在事务 1 中给 111 加了 next-key 锁，事务 2插入112的时候会首先进行插入意向锁的插入。事务 2由于间隙锁和插入意向锁的冲突，导致了阻塞。

从上面3个例子可以看出InnoDB 的行锁，就是通过锁住索引来实现的。

2、为什么通过唯一索引给数据行加锁，主键索引也会被锁住?

大家还记得在 InnoDB 里面，当我们使用辅助索引的时候，它是怎么检索数据的吗? 辅助索引的叶子节点存储的是什么内容?

在辅助索引里面，索引存储的是二级索引和主键的值。比如name = ‘zhangsan’，存储的是 name 的索引和主键 id 的值 1。

而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。

现在我们已经搞清楚 4 个锁的基本类型和锁的原理了，在官网上，还有 3 种锁，我们把它理解为锁的算法。我们也来看下 InnoDB 在什么时候分别锁住什么范围。

 

2.4锁的算法

我们先来看一下我们测试用的表，t5，这张表有一个主键索引。
我们插入了 4 行数据，主键值分别是 1、4、7、10。

CREATE TABLE `t5` ( 
`id` int(11) PRIMARY KEY,
`name` varchar(255) DEFAULT NULL,
 UNIQUE KEY nameindex(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t5 values(1,'zhangsan'); 
insert into t5 values(4,'lisi'); 
insert into t5 values(7,'wangwu');
insert into t5 values(10,'zhaoliu');

为了让大家真正理解这三种行锁算法的区别，我们需要了解一下三种范围的概念。因为我们用主键索引加锁，我们这里的划分标准就是主键索引的值。

这些数据库里面存在的主键值，我们把它叫做记录(Record)，那么这里我们就有 4 个 记录(Record)。

根据主键，这些存在的 记录(Record) 隔开的数据不存在的区间，我们把它叫做间隙(Gap)，它是一个左开右开的区间。

最后一个，间隙(Gap)连同它左边的记录(Record)，我们把它叫做临键(Next-key)的区间， 它是一个左开右闭的区间。

t5 的主键索引，它是整型的，可以排序，所以才有这种区间。如果我的主键索引不 是整形，是字符怎么办呢?字符可以排序吗? 用 ASCII 码来排序。

我们已经弄清楚了三个范围的概念，这3个范围对应着3种锁的范围。

我们来看看3种锁的定义：

记录锁(Record Lock):单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

间隙锁(Gap Lock):在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。gap lock的机制主要是解决可重复读模式下的幻读问题。

临键锁(Next-Key Lock):就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

下面我们就来看一下在不同的隔离级别下范围下，这3种锁是怎么表现的。

2.4.1Read Committed隔离级别下

1.主键索引情况(RC)

TABLE定义：

CREATE TABLE `t6` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t6 values(1,'zhangsan'); 
insert into t6 values(4,'lisi'); 
insert into t6 values(7,'wangwu'); 
insert into t6 values(10,'zhaoliu');

1.第一个事务里面，我们通过 where id = 1 锁住id等于1的数据

#Transaction 1

begin;
select * from t6 where id = 1 for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面，我们尝试给 id=1 的这一行数据加锁，并尝试新增1条数据

#Transaction 2

begin;
select * from t6 where id = 1 for update; --BLOCKED

insert into t6 values(2,'wangba'); --OK

结果第二个事务 id=1 的这一行数据阻塞了。

所以在第一个事务里主键id = 1的记录已经加上Record锁。如下图所示：

结论：id是主键时，此SQL只需要在id=1这条记录上加Record锁即可。

2.唯一索引情况(RC)

TABLE定义：

CREATE TABLE `t7` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
 UNIQUE KEY index_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t7 values(1,'zhangsan'); 
insert into t7 values(4,'lisi'); 
insert into t7 values(7,'wangwu'); 
insert into t7 values(10,'zhaoliu');

1.第一个事务里面，我们通过 where name = ‘lisi’ 锁住name等于 ‘lisi’ 的数据

#Transaction 1

begin;
select * from t7 where where name = 'lisi' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面，我们尝试分别给name = ‘lisi’ 和id = 4的数据加锁

#Transaction 2

begin;
select * from t7 where name = 'lisi' for update; --BLOCKED

select * from t7 where id = 4 for update; --BLOCKED

结果第二个事务里name = ‘lisi’ 和id = 4都数据阻塞了。

所以在第一个事务里主键name = ‘lisi’的记录已经加上Record锁。如下图所示：

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

结论：若name上有unique索引。那么SQL需要加两个Record锁，一个对应于name字段unique索引上的name = ‘lisi’的记录，另一把锁对应于聚簇索引上的[id=4,name= ‘lisi’]的记录。

3.非唯一索引情况(RC)

TABLE定义：

CREATE TABLE `t8` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
 KEY index_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t8 values(1,'zhangsan'); 
insert into t8 values(2,'lisi'); 
insert into t8 values(4,'lisi'); 
insert into t8 values(7,'wangwu'); 
insert into t8 values(10,'zhaoliu');

1.第一个事务里面，我们通过 where name = ‘lisi’ 锁住name等于 ‘lisi’ 的数据

#Transaction 1

begin;
select * from t8 where where name = 'lisi' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面，我们尝试分别给name = ‘lisi’ 和id = 2，id = 4的数据加锁

#Transaction 2

begin;
select * from t8 where name = 'lisi' for update; --BLOCKED

select * from t8 where id = 2 for update; --BLOCKED
select * from t8 where id = 4 for update; --BLOCKED

结果第二个事务里name = ‘lisi’ 和id = 2，id = 4都数据阻塞了。

所以在第一个事务里主键name = ‘lisi’的记录已经加上Record锁。如下图所示：

与唯一索引唯一的区别在于，唯一索引最多只有一个满足等值查询的记录，而非唯一索引情况会将所有满足查询条件的记录都加锁。

结论：若name列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

4.无索引情况(RC)

TABLE定义：

CREATE TABLE `t9` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t9 values(1,'zhangsan'); 
insert into t9 values(4,'lisi'); 
insert into t9 values(7,'wangwu'); 
insert into t9 values(10,'zhaoliu');

1.第一个事务里面，我们通过 where name = ‘lisi’ 锁住name等于 ‘lisi’ 的数据

#Transaction 1

begin;
select * from t9 where where name = 'lisi' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面，我们尝试分别给name = ‘lisi’ 和id = 1，id = 4，id = 7，id = 10的数据加锁

#Transaction 2

begin;
select * from t9 where name = 'lisi' for update; --BLOCKED

select * from t9 where id = 1 for update; --BLOCKED
select * from t9 where id = 4 for update; --BLOCKED
select * from t9 where id = 7 for update; --BLOCKED
select * from t9 where id = 10 for update; --BLOCKED

结果第二个事务里name = ‘lisi’ 和id = 1，id = 4，d = 4，id = 10都数据阻塞了。

所以在第一个事务里所有主键的记录已经加上Record锁。如下图所示：

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

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

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

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

2.4.2Repeatable Read隔离级别下

1.主键索引情况(RR)

加锁情况和主键索引情况(RC)一致

2.唯一索引情况(RR)

加锁情况和唯一索引情况(RC)一致

3.非唯一索引情况(RR)

TABLE定义：

CREATE TABLE `t10` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
 KEY index_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t10 values(1,'a'); 
insert into t10 values(4,'d'); 
insert into t10 values(7,'g'); 
insert into t10 values(10,'j');

1.第一个事务里面，我们通过 where name = ‘d’ 锁住name等于 ‘d’ 的数据

#Transaction 1

begin;
select * from t10 where name = 'd' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面，我们尝试分别给name = ‘d’ ,id = 4的数据加锁

#Transaction 2

begin;
select * from t10 where name = 'd' for update; --BLOCKED
 
select * from t10 where id = 4 for update; --BLOCKED

insert into t10 values(2,'b'); --BLOCKED

insert into t10 values(3,'d'); --BLOCKED

insert into t10 values(5,'e'); --BLOCKED

insert into t10 values(8,'h'); --OK

结果第二个事务里不仅name = ‘d’ 和id = 4都数据阻塞了，而且[2,b],[3,d],[5,e]都不能插入。

所以在第一个事务里主键name = ‘d’的记录已经加上Record锁,ad之间,dj之间加入了gap锁如下图所示：

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

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

如图中所示，有哪些位置可以插入新的满足条件的项 (name = ‘d’)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[a,1]之前，不会插入记录；记录[a,1]与[d,4]之间，可能插入记录；记录[d,4]与[g,7]之间，可能插入记录； [g,7]之后不会插入记录。

因此，为了保证[a,1]与[d,4]间，d,4]与[g,7]间不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这2个GAP给锁起来。

结论：Repeatable Read隔离级别下，name列上有一个非唯一索引，对应SQL：select * from t10 where name = ‘d’ for update; 首先，通过name索引定位到第一条满足查询条件的记录，加记录上的Record锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录Record锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[g,7]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

4.无索引情况(RR)

TABLE定义：

CREATE TABLE `t11` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t11 values(1,'a'); 
insert into t11 values(4,'d'); 
insert into t11 values(7,'g'); 
insert into t11 values(10,'j');

1.第一个事务里面，我们通过 where name = ‘d’ 锁住name等于 ‘d’ 的数据

#Transaction 1

begin;
select * from t11 where name = 'd' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面，我们尝试分别给name = ‘d’ ,id = 4的数据加锁

#Transaction 2

begin;
select * from t11 where name = 'd' for update; --BLOCKED
 
select * from t11 where id = 4 for update; --BLOCKED

insert into t11 values(2,'b'); --BLOCKED

insert into t11 values(3,'d'); --BLOCKED

insert into t11 values(5,'e'); --BLOCKED

insert into t11 values(8,'h'); --BLOCKED

insert into t11 values(9,'i'); --BLOCKED

结果第二个事务里不仅name = ‘d’ 和id = 4都数据阻塞了，而且[2,b],[3,d],[5,e],[8,h],[9,i]都不能插入。

所以在第一个事务里主键name = ‘d’的记录已经加上Record锁。小于a,ad之间,dj之间,gj之间.大于j加入了gap锁如下图所示：

如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了Record锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有4条记录，一共需要4个记录锁，5个GAP锁。

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

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

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

2.4.3Serializable隔离级别下

针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL：delete from t9 where id = 1; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

Serializable隔离级别，影响的是SQL：select * from t9 where id = 1; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

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

2.5隔离级别的实现

 

所以，我们再回过头来看下这张图片，为什么 InnoDB 的 RR 级别能够解决幻读的 问题，就是用临键锁实现的。
我们再回过头来看下这张图片，这个就是 MySQL InnoDB 里面事务隔离级别的实现。

我们先了解2个概念 快照读和当前读：

快照读(snapshot read)：简单的select操作，属于快照读，不加锁，底层使用 MVCC 来实现。(当然，也有例外，下面会分析)
select * from table where ?;

当前读(current read))：特殊的读操作,官方定义为locking read，插入/更新/删除操作，属于当前读，需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

1.Read Uncommited (RU)  隔离级别下

快照读和当前读都不加锁

2.Read Commited(RC) 隔离级别下

普通select快照读不加锁。

当前读都使用记录锁(Record lock)。

除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁(Gap lock)封锁区间。
所以 RC 会出现幻读的问题。

3.Repeatable Read(RR) 隔离级别下

普通select快照读不加锁。

当前读底层使用记录锁、或者间隙锁、 临键锁。

4.Serializable隔离级别下

普通select快照读会转成和当前读一样底层使用记录锁、或者间隙锁、 临键锁。

2.6查看锁信息(日志)

SHOW STATUS 命令中，包括了一些行锁的信息:

mysql> show status like 'innodb_row_lock_%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 4416  |
| Innodb_row_lock_time_avg      | 4416  |
| Innodb_row_lock_time_max      | 4416  |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

Innodb_row_lock_current_waits:当前正在等待锁定的数量; Innodb_row_lock_time :从系统启动到现在锁定的总时间长度，单位 ms; Innodb_row_lock_time_avg :每次等待所花平均时间; Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间; Innodb_row_lock_waits :从系统启动到现在总共等待的次数。

SHOW 命令是一个概要信息。InnoDB 还提供了三张表来分析事务与锁的情况:

select * from information_schema.INNODB_TRX; -- 当前运行的所有事务 ，还有具体的语句

#当前出现的锁

mysql> select * from information_schema.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+
| 171801:424:4:1 | 171801      | X         | RECORD    | `test`.`t4` | nameindex  |        424 |         4 |        1 | supremum pseudo-record |
| 171799:424:4:1 | 171799      | X         | RECORD    | `test`.`t4` | nameindex  |        424 |         4 |        1 | supremum pseudo-record |
+----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

#锁等待的对应关系

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 171802            | 171802:424:4:1    | 171799          | 171799:424:4:1   |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

如果一个事务长时间持有锁不释放，可以 kill 事务对应的线程 ID，也就是 INNODB_TRX 表中的 trx_mysql_thread_id，例如执行 kill 4，kill 7，kill 8。
当然，死锁的问题不能每次都靠 kill 线程来解决，这是治标不治本的行为。我们应该 尽量在应用端，也就是在编码的过程中避免。