MySQL 事务和锁

事务隔离级别

行为解释

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

读行为

RC 总是读取记录的最新版本，如果该记录被锁住，则读取该记录最新的一次快照，而 RR 是读取该记录事务开始时的那个版本。虽然这两种读取方式不一样，但是它们读取的都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read）。

除了快照读 ，MySQL 还提供了另一种读取方式：当前读（Current Read），有时候又叫做加锁读（Locking Read） 或者阻塞读（Blocking Read），这种读操作读的不再是数据的快照版本，而是数据的最新版本。

MySQL 隔离级别

可以通过查看 MySQL 中的系统变量 tx_isolation 的值来确定当前 MySQL 正在使用什么隔离级别。

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

另外可以使用 SET TRANSACTION 命令修改 MySQL 的隔离级别：

mysql> set session transaction isolation level read committed;

示例

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

• 读未提交：V1 = V2 = V3 = 2，事务 B 虽然还没有提交，但是结果已经被 A 看到了。
• 读提交：V1 = 1，V2 = V3 = 2，事务 B 的更新在提交后才能被 A 看到。
• 可重复读：V1 = V2 = 1，V3 = 2，事务在执行期间看到的数据前后必须是一致的。
• 串行化：在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。

隔离级别是如何实现的

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。

SELECT 的一致性视图

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

UPDATE 的一致性逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。 其实，除了 update 语句外，select 语句如果加锁，也是当前读。

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

幻读介绍及原因

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。幻读仅专指“新插入的行”。

INSERT INTO T VALUES (1, 1, 5);

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。顾名思义，间隙锁，锁的就是两个值之间的空隙。

这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

事务相关的 Log

四个属性

• A: 要么不执行，要么完全执行，宕机重启已执行的要回滚
• C: 主键不为空、参照完整性等
• I: 如果全是串行执行，也不需要隔离，所以和并发有关
• D: 数据不能丢

Redo Log

redo log 是 InnoDB 引擎特有的日志。

Write-Ahead Log

先在内存中提交事务，然后写 Write-Ahead Log，然后后台把数据异步刷到磁盘。在 InnoDB 中，Write-Ahead Log 是 Redo Log。事务提交之后，Redo Log 先写入到内存的 Redo Log Buffer 中，然后异步刷到磁盘上的 Redo Log 。

InnoDB 关键参数 innodb_flush_log_at_trx_commit 控制刷盘策略：

• 0: 每秒刷一次 (默认)
• 1: 提交一个事务，就刷一次
• 2: 不刷盘，根据 innodb_flush_log_at_timeout 设置的值决定刷盘频率

Redo Log 刷脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

刷脏页时机：

• redo log 写满了，要 flush 脏页
• 内存不够用了，要先将脏页写到磁盘

InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：第一种是，还没有使用的；第二种是，使用了并且是干净页；第三种是，使用了并且是脏页。

• MySQL 认为**系统“空闲”**的时候。
• MySQL 正常关闭。

Redo Log 逻辑与物理结构

为什么 Redo Log Block 是 512 字节?

因为早期的磁盘，一个扇区是存储 512 字节数据。

为什么 Redo Log 循坏使用?

Redo Log 是固定大小的，循环使用，一旦 Page 数据刷到磁盘上，日志数据就没有存在的必要了。

LSN (Log Sequence Number) 是按照时间顺序从小到大的编号，记录了从安装到现在为止，写入的总的字节数。

binlog

MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。

Undo Log

为什么需要 Undo Log ?

基于现代数据库两个设计的事实：

• 已经提交的事务，可以继续保留在内存
• 未提交的事务，也可以写入磁盘，如果需要回滚，再更改磁盘上的数据

Undo Log 结构

Undo Log 维护了数据从旧到新的各个版本，各个版本通过链表串联。事务如果想要实现隔离性，只能读取 (select 快照读) 历史版本，不能读取正在修改的数据。

与快照读对应的是当前读:

Undo Log 不是 Log

• 无序随机写入
• 事务 commit 之后，就可以删除掉 Undo Log

加锁规则

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

常见语句加锁规则

• 常见语句的加锁
  • SELECT ... 语句正常情况下为快照读，不加锁；
  • SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；
  • SELECT ... FOR UPDATE 语句为当前读，加 X 锁；
  • 常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；
  • 常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚；
• 表锁
  • 表锁（分 S 锁和 X 锁）
  • 意向锁（分 IS 锁和 IX 锁）
  • 自增锁（一般见不到，只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 时才可能有）
• 行锁
  • 记录锁（分 S 锁和 X 锁）
  • 间隙锁（分 S 锁和 X 锁）
  • Next-key 锁（分 S 锁和 X 锁）
  • 插入意向锁
• 行锁分析
  • 行锁都是加在索引上的，最终都会落在聚簇索引上；
  • 加行锁的过程是一条一条记录加的；
• 锁冲突
  • S 锁和 S 锁兼容，X 锁和 X 锁冲突，X 锁和 S 锁冲突；
• 不同隔离级别下的锁
  • 上面说 SELECT ... 语句正常情况下为快照读，不加锁；但是在 Serializable 隔离级别下为当前读，加 S 锁；
  • RC 隔离级别下没有间隙锁和 Next-key 锁（特殊情况下也会有：purge + unique key）；

全局锁

Flush tables with read lock;

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

-- 第一个窗口执行
LOCK TABLE t2 READ; -- 加读锁

-- 第二个窗口执行
SELECT * FROM t2; -- 不会加锁
UPDATE t2 SET name = 'g1' WHERE id = 7; -- 锁住

-- 第一个窗口执行，可以看到被等待锁的语句
SHOW PROCESSLIST;

-- 第一个窗口执行，第二个窗口的语句才会返回
UNLOCK TABLES;

行锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

-- 首先看一下我们的表
CREATE TABLE t (
  id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL，
  
  --           ↓ (id 字段是索引字段)
  PRIMARY KEY(id)
);

-- 窗口一、窗口二依次执行
BEGIN;

-- 窗口一
--                              ↓ (索引条件)
UPDATE t SET name = 'd1' WHERE id = 4;

-- 窗口二
--                              ↓ (索引条件)
UPDATE t SET name = 'd2' WHERE id = 4; -- 陷入锁等待
UPDATE t SET name = 'd2' WHERE id = 5; -- 不是同一行，不会陷入锁等待

-- 大量事务无法获取锁会挂起，造成严重性能问题
-- INNODB_LOCK_WAIT_TIMEOUT = 100 秒

行锁一共有四种：

• 记录锁 (LOCK_REC_NOT_GAP)：只锁记录，最简单的行锁，记录锁永远是加在索引上的。
• 间隙锁 (LOCK_GAP)：锁两个记录之间的 GAP，防止记录插入
• Next-key 锁 (LOCK_ORNIDARY)：锁一条记录及之前的间隙，是记录锁和间隙锁的组合，RR 隔离级别使用的最多的锁
• 插入意向锁 (LOCK_INSERT_INTENTION)：插入记录时使用，是 LOCK_GAP 的一种特例

行锁转为表锁

id 不是索引了。

-- 首先看一下我们的表
CREATE TABLE t (
  -- ↓ (id 不是索引字段)
  id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL，
);

-- 窗口一、窗口二依次执行
BEGIN;

-- 窗口一
--                              ↓ (非索引条件)
UPDATE t SET name = 'd1' WHERE id = 4;

-- 窗口二
--                              ↓ (非索引条件)
UPDATE t SET name = 'd2' WHERE id = 5; -- 陷入表锁等待

乐观锁

update table set x = 1, version = #{version} where id = #{id} and version = #{version}

悲观锁

-- (写锁，排他锁) 必须要在事务中才可以起作用
select * from table for update

S/X 锁

• Shared Lock(S) 也叫读锁
• Exclusive Lock(X) 也叫写锁

• S 锁：SELECT ... LOCK IN SHARE MODE
• X 锁：SELECT ... FOR UPDATE
• X 锁：INSERT / UPDATE / DELETE
• RC 隔离级别只加记录锁
• RR 隔离级别除了加记录锁，还会加间隙锁，用于解决幻读问题

死锁

（1）产生死锁的四个必要条件：

• 互斥条件
• 不可剥夺条件
• 请求与保持条件
• 循坏等待条件

（2）避免死锁：

• 有序资源分配法
• 银行家算法
• 加锁顺序、加锁时限、死锁检测

（3）MySQL 产生死锁

不同的事务互相持有了对方所需要的锁，它们互相等待对方释放。

（4）减少死锁

• 减少死锁的主要方向，就是控制访问相同资源的并发事务量。
• 减少大事务：事务执行 INSERT 或者 UPDATE 的数据尽可能的少，这样事务不需要维持太长的时间。
• 死锁不受到隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是因为写操作引起的。
• 设置锁等待超时参数：innodb_lock_wait_timeout

（5）查看死锁

• 查看 InnoDB 引擎最近一次的死锁日志：

SHOW ENGINE INNODB STATUS;

（6）死锁示例

Client A:

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES (1);
START TRANSACTION;
SELECT * FROM t WHERE i = 1 FOR SHARE;

Client B:

START TRANSACTION;
DELETE FROM t WHERE i = 1;

Client A:

DELETE FROM t WHERE t = 1;
ERROR 1213 (40001): Deadlockfound when trying to get lock;
try restarting transaction

解释：

Client A 持有 S 锁，所以 Client B 尝试获取 X 锁删除的时候无法获取到锁，所以这个操作会阻塞在锁请求队列中，此时，Client A 也想要获取 X 锁来删除记录，然而 Client B 已经有一个对于 X 锁的请求，并且在等待 Client A 释放掉 S 锁，而因为 Client B 想要获取到 X 锁，所以 Client A 也无法自动将 X 锁升级为 S 锁。于是死锁发生了。

参考

• Difference between Shared Lock and Exclusive Lock
• 15.7.5.1 An InnoDB Deadlock Example
• 死锁，死锁的四个必要条件以及处理策略
• 解决死锁之路 - 了解常见的锁类型
• 解决死锁之路（终结篇） - 再见死锁
• aneasystone/mysql-deadlocks
• 解决死锁之路 - 学习事务与隔离级别
• 解决死锁之路 - 常见 SQL 语句的加锁分析