关于mysql:浅谈MySQL的InnoDB引擎锁

前言

浏览本文后你将播种：

• 1. 对 MySql 中的锁有更加全面的意识。
• 2. 理解什么是幻读，以及如何防止幻读。
• 3.InnoDb 引擎对于行级锁的实现形式。
• 4. 死锁产生的条件、实例及如何防止死锁。
• 5. 本文中的 sql 语句均可间接在 MySql 中执行，不便本人做试验，这点很重要，只有本人入手试验过才会记忆更加粗浅。

另外，因为自己程度及工夫无限，文中若有纰漏，欢送批评指正，感激不尽，当然有任何疑难也欢送评论区留言，一起学习探讨共同进步。

置信许多同学对于 MySQL 锁的概念并不生疏，但又感觉了解地不是很透彻，总像蒙着一层纱。那么明天咱们就一起通过实际操作来捋一捋 Mysql 的锁，读本文之前倡议你先理解 隔离级别、以后读、快照读 等概念。注：本文的操作均是基于 Mysql 8.0.22 版本 InnoDB 引擎进行的 ，在其余大版本下执行，后果可能存在差别。（留神📢：本文均是在（repeatable-read）隔离级别下进行的操作）
接下来咱们将从 MySQL 有哪些锁以及为什么要加锁展开讨论。

1.MySql 有哪些锁？

MySQL 不同的存储引擎反对不同的锁机制，所有的存储引擎都以本人的形式实现锁，服务器层无需理解存储引擎锁的具体实现。依据锁的不同粒度，MySQL 的锁可分为：全局锁、表级锁、行级锁（InnoDB）及其他（自增 ID 锁）。本文将重点探讨行级锁。其余只做简要形容，感兴趣的可自行搜寻。

1.1 全局锁

Flush tables with read lock ：
顾名思义是锁整个数据库，全局锁的典型应用场景是数据库备份时，为了保持数据的一致性，会对数据库加全局锁，然而当数据库表应用的引擎为 InnoDB 时，个别应用mysqldump …… –single-transaction。如果应用 MyISAM 引擎进行数据备份时，则只能加全局锁了。

如果数据备份时不加全局锁，会产生怎么的数据不统一呢？这里举个栗子：数据库中含有：Wallet（用户钱包）表 及 stock（商品库存）表，当购买商品时须要同时扣减 用户钱包 及 商品库存。在数据库备份过程中，恰好产生了商品购买。而且恰好是在 wallet 表备份实现后 及 stock 备份前，产生了商品购买。因为 wallet 表曾经实现了备份，所以此次的钱包扣减操作并没有被记录到 wallet 的备份文件中。但扣减库时，stock 表还未备份，所以此次库存扣减记录在备份文件中，这就导致应用备份文件复原进去的数据库数据不统一了，钱包没有扣钱，然而库存扣减了。

因为 InnoDB 引擎反对快照读，所以如果在数据库备份时打一个快照（–single-transaction），则就算不加全局锁，也不会有数据不统一的问题。

1.2 表级锁：

表锁（Lock tables read）：
个别 MyISAM 和 MEMORY 存储引擎会采纳表锁解决并发（这两个引擎不反对行级锁），而 InnoDB 引擎则同时反对表锁与行锁，并通过行级锁来进步并发性（另外，行锁应用不过后，InnoDB 的行锁也会进化成表级锁，前面会介绍）。另外 BDB 存储引擎应用的是页面锁，页面锁的粒度介于表级锁与行级锁之间。

MDL 锁（meta data lock）：
在 MySQL 5.5 版本之后引入了 MDL 锁，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做构造变更操作 DDL 的时候，加 MDL 写锁。以避免在数据查问或数据更新时有表构造的变更，进而导致查问或更新的后果与预期的不统一。

构想一下，如果 DDL 操作 与 CURD 操作之间不加锁，能够同时进行，那么在查问数据时，你的查问条件列被删除了，或者你要更新的列被删除了，是不是不管从 MySql 的语句执行方面 及业务查问数据的后果来看，都会怪怪的。所以 MySql 引入了 MDL 锁的概念。

意向锁（Intention Locks）：
为了 容许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种外部应用的意向锁（Intention Locks），这两种意向锁都是表级别锁：

• 动向共享锁（IS）：事务在给数据行加读锁前，必须先获取该表的 IS 锁。
• 动向排他锁（IX）：事务在给数据行加写锁前，必须先获取该表的 IX 锁。

意向锁与锁之间的兼容关系如下：

正文：X：eXclusive 排它锁，S: Share 共享锁，IX : Intention eXclusive 动向排它锁，IS：Intention Share 动向共享锁。

一句话：意向锁的作用是 在加表锁时，能够疾速的发现，是否存在行锁，而无需遍历整个索引后才晓得表曾经加了行锁，已无奈再加表级锁。举个例子：

CREATE TABLE `wallet` (`user` VARCHAR(32) NOT NULL ,
  `balance` int(11) DEFAULT 0,
  PRIMARY KEY (`user`)
) ENGINE=InnoDB;
 
INSERT INTO wallet VALUES ("Tom",1000);

session 1 在 T1 时刻加行锁时，引擎主动在 wallet 表上加了 IX。session 2 在 T2 在表读锁之前，引擎主动加了 IS 通过，但在加 S 锁时 与 sesion1 加的 IX 抵触了，所以进入阻塞状态。通过这种形式无需遍历所有索引是否存在行级锁，大幅提高了 MySql 并发的效率。

1.3 行级锁：

InnoDB 存储引擎为了进步并发度，引入了行级锁概念，行级锁有波及 排他锁、共享锁、行锁、距离锁、The next key lock 等概念，后续会一一介绍。留神📢：后文中提到的行锁实则为行级锁。而将只锁一行的锁叫做 典型行锁。

1.4 自增 ID 锁

当表含有自增 ID 字段时（AUTO_INCREMENT），为了避免并发事务申请到雷同的自增 ID，所以 MySQL 在事务申请自增 ID 时加了锁。但自增 ID 锁不是事务性（换句话说就是锁不会在整个事务过程当中始终持有），而是在获取到自增 ID 后立刻开释。

2. 为什么要加锁？

2.1 保证数据一致性

上面举一个 因不加锁而导致数据不统一的场景。假如有一个存储用户钱包余额的 wallet 表，建表语句如下：

CREATE TABLE `wallet` (`user` VARCHAR(32) NOT NULL ,
  `balance` int(11) DEFAULT 0,
  PRIMARY KEY (`user`)
) ENGINE=InnoDB;
-- 插入记录
INSERT INTO wallet VALUES ("Tom",1000);

场景：用户 Tom 钱包中有 1000 元。现要购买一个 1000 元的商品，但因为某种原因（客户端、网关重试或歹意攻打 等起因），服务器 同时收到了 两条 Tom 的购买申请 。
假如业务的执行逻辑是：

• 1. 查询数据库；
• 2. 验证余额是否短缺；
• 3. 如果短缺，则执行扣款；
• 4. 如果不短缺，则返回扣款失败；

（留神📢：本文均是在（repeatable-read）隔离级别下进行的操作）

假如业务逻辑是依照如下时序执行的，那么会呈现什么问题呢？能够先思考下标红处的执行后果如何？

session1session2T1BEGIN;select from wallet where user = ‘tom’;T2 业务代码 校验余额是否短缺余额为 1000 短缺 BEGIN;select from wallet where user = ‘tom’;T3 业务代码 校验余额是否短缺余额为 1000 短缺 T4UPDATE wallet set balance = balance – 1000 WHERE user = ‘tom’;T5select from wallet where user = ‘tom’;balance = ?T6UPDATE wallet set balance = balance – 1000 WHERE user = ‘tom’; 是否会 block？T7select from wallet where user = ‘tom’;balance = ?T8commit;T9select from wallet where user = ‘tom’;balance = ?T10select from wallet where user = ‘tom’;balance = ?T11commit
下表是执行后果：
session1session2T1BEGIN;select from wallet where user = ‘tom’;T2 业务代码 校验余额是否短缺余额为 1000 短缺 BEGIN;select from wallet where user = ‘tom’;T3 业务代码 校验余额是否短缺余额为 1000 短缺 T4UPDATE wallet set balance = balance – 1000 WHERE user = ‘tom’;T5select from wallet where user = ‘tom’;balance = 1000 如果不理解为何是 1000，可自行搜寻 可反复读隔离级别相干内容 T6UPDATE wallet set balance = balance – 1000 WHERE user = ‘tom’; 是否会 block？是，因为 update 语句会默认加写锁，因为 session1 曾经加了锁，所以 session 2 要期待 T7select from wallet where user = ‘tom’;balance = 0T8commit; 当 session 1 提交后，update 语句将不再阻塞，并执行胜利，此时 balance 变为 -1000T9select from wallet where user = ‘tom’;balance = -1000T10select from wallet where user = ‘tom’;balance = -1000 即便在可反复读隔离级别下，也是能够看到本人的提交的，所以是 -1000，而不是 0T11commit
大家能够看到，如果不加锁的状况下会呈现余额为负的状况，导致数据不统一。
同样的场景，如果加了锁会怎么呢？
session1session2T1BEGIN;select from wallet where user = ‘tom’ for update;T2 业务代码 校验余额是否短缺余额为 1000 短缺 BEGIN;select from wallet where user = ‘tom’ for update; 阻塞 UPDATE wallet set balance = balance – 1000 WHERE user = ‘tom’;T3commit;session 1 提交后，session 2 不再阻塞，读到余额为 0 业务代码 校验余额是否短缺余额为 0 返回失败

如上，同样的场景下，加锁能够防止数据的不统一。

2.2 防止产生幻读
现有表 student。表构造与表中记录如下所示：

CREATE TABLE student (
student_id int NOT NULL,
name VARCHAR(32) NOT NULL ,
age int(11) DEFAULT 0,
PRIMARY KEY (student_id),

KEY `age`(`age`) USING BTREE

) ENGINE=InnoDB;
INSERT INTO student VALUES(6,’Tom’,6),(11,’Jerry’,11),(18,’Jamey’,18),(25,’Marvin’,25);

上面的操作与演示何为幻读。
session1session2T1BEGIN;SELECT from student where student_id > 6 and student_id < 20;rows 211 Jerry18 JameyT2INSERT into student VALUES(16,’Lucy’,16);T3SELECT from student where student_id > 6 and student_id < 20;rows 211 Jerry18 JameyT4INSERT into student VALUES(16,’Lucy’,16);Duplicate entry ’16’ for key ‘student.PRIMARY’T5SELECT * from student where student_id > 6 and student_id < 20;rows 211 Jerry18 JameyT7commit;

在可反复读（RR）隔离级别下，执行 select 为快照读，所以在 T3 时刻并无奈读取到 T2 时刻插入的记录，但 T4 时刻插入 student_id=16 的记录时，却又呈现了反复，而且就算 T5 时刻再次读取也无奈读取数据库中的 student_id=16 的记录。就像产生了幻觉一样，明明查不到数据，但插入时却抵触，这种景象就称为幻读。解决幻读须要设置 Serializable 隔离级别或读数据时应用锁（以后读）。上面将探讨如何通过加锁解决幻读。

3 行级锁
3.1 For Update 锁
本节将探讨，为何行级锁能够解决幻读？又是如何解决的？上面先看一下如下语句的执行后果。仍然是应用 student 表，表中有 4 行数据，建表及插入语句如下。
CREATE TABLE student (
student_id int NOT NULL,
name VARCHAR(32) NOT NULL ,
age int(11) DEFAULT 0,
PRIMARY KEY (student_id),

KEY `age`(`age`) USING BTREE

) ENGINE=InnoDB;
INSERT INTO student VALUES(6,’Tom’,6),(11,’Jerry’,11),(18,’Jamey’,18),(25,’Marvin’,25);

session1session2T1BEGIN;SELECT from student where student_id > 6 and student_id < 20 FOR UPDATE;T2SELECT from student where age > 6 and age < 20;block？noT3INSERT into student VALUES(17,’Lily’,17);block?yesT4INSERT into student VALUES(21,’Lily’,21);block?yesT5NSERT into student VALUES(26,’Lily’,26);block?No

行锁是通过对索引项加锁，而不是作用于实在记录的。所以只有通过索引查问才会应用行锁（The next key lock），没有索引则会是锁全表（进化成表级锁）。

为什么 T3 与 T4 会阻塞，而 T5 不会？这就就波及到了一个新概念 gap lock（注：gap lock 是 RR 级别独有的。RC 级别只锁行，不锁空隙，文末会简略介绍）。间隙锁，锁的是两个值之间的空隙。如
SELECT * from student where student_id > 6 and student_id < 20 FOR UPDATE 不仅会锁满足条件的行（student_id = 11 与 student_id = 18）, 而且还会锁住他们之间空隙。如下图所示：

上面是 InnoDB 加锁的两个准则与两个优化（此处借鉴丁奇老师的 MySQL 45 讲）。这里波及到 Next-key lock 概念，Next-key lock 是由 gap lock 与 典型行锁组成的。如上图 在 索引 11 处 加的 Next-key lock 就是由 6-11 的 gap lock 加上 11 的典型行锁 组成的，留神 next-key lock 是左开右闭的，用数学标识法 相似：(6 , 11]。上面来看一下 RR 级别下 InnoDb 引擎加锁的准则是：
准则 1：加锁的根本单位是 next-key lock。next-key lock 是左开右闭的。
准则 2：查找过程中拜访到的对象才会加锁。
优化 1：索引上的等值查问，给惟一索引加锁的时候，next-key lock 进化为行锁（典型行锁）。
优化 2：索引上的等值查问，向右遍历时且最初一个值不满足等值条件的时候，next-key lock 进化为间隙锁。
咱们能够参考 2 个准则与 2 个优化来验证下上图的加锁逻辑。
student_id = 6 未加锁 的起因是准则 2。因为条件是 >6，所以并未拜访行 6，所以行 6 并未加锁。且也满足准则 1，即 next-key lock 是左开右闭的。
student_id = 25 未加锁的起因是优化 2。next-key lock 进化为 gap lock，所以 25 并未加锁。
student_id = 11 与 student_id = 18 加锁也是因为 准则 1 与准则 2。

当初咱们能够答复大节开篇的问题了，为何行级锁能够解决幻读？

1. 因为加了锁（for update 或 lock in share mode）之后，是以后读，会读取最新的已提交的记录，所以不会产生幻觉。
2. 因为在索引间加了间隙锁，所以其余事物无奈插入新的记录，也就不会产生幻读了。

补充常识 1：只有执行打算真正应用了索引，能力应用行锁：即使在条件中应用了索引字段，但是否应用索引来检索数据是由 MySQL 通过判断不同执行打算的代价来决定的，如果 MySQL 认为全表扫描效率更高，比方对一些很小的表，它就不会应用索引，这种状况下 InnoDB 将应用表锁，而不是行锁。因而，在剖析锁抵触时，别忘了查看 SQL 的执行打算（能够通过 explain 查看 SQL 的执行打算），以确认是否真正应用了索引。
补充常识 2：更新语句默认加锁，且是以后读。在 InnoDB 事务中，行锁是在须要的时候才加上的，但并不是不须要了就立即开释，而是要等到事务完结时才开释。这就是两阶段锁协定。

3.2 Lock In share mode 锁
Lock In share mode 锁也能够实现以后读，且加锁后其余 session 不能够写。但以后 session 能够写。Lock In share mode 与其余事务的 for update 锁是互斥的，但与其余事务的 Lock In share mode 是共享的。
如下语句的执行后果解释了上述论断。
session1session2T1BEGIN;T2INSERT into student VALUES(10,’Lily’,17);T3SELECT from student where student_id > 6 and student_id < 20 是否查到方才插入的数据？能，因为仅仅是 begin，并未真的去获取快照，而是执行 sql 之后，才会生成快照。（感兴趣的能够理解下快照读）T4INSERT into student VALUES(12,’Lily’,17);T5SELECT from student where student_id > 6 and student_id < 20 是否查到方才插入的数据？不能，在 RR 级别下，此刻是快照读 T6SELECT * from student where student_id > 6 and student_id < 20 LOCK IN SHARE MODE; 是否查到方才插入的数据？能，因为 share mode 是以后读 T7INSERT into student VALUES(19,’Lucy’,21)是否 block？是，与 Session 1 的锁抵触。T8INSERT into student VALUES(15,’Lucy’,21)是否 block？否，同一 session 申请写锁时，不会与本人申请的读锁抵触，但个别不这样做，这样容易产生死锁，上面内容会形容

4 死锁
4.1 死锁的产生与防止
既然加了锁，就不免产生死锁，上面回顾下产生死锁的四个必要条件？
（1）互斥条件：一个资源每次只能被一个过程应用。
（2）申请与放弃条件：一个过程因申请资源而阻塞时，对已取得的资源放弃不放。
（3）不剥夺条件: 过程已取得的资源，在末应用完之前，不能强行剥夺。
（4）循环期待条件: 若干过程之间造成一种头尾相接的循环期待资源关系。
只有下面四个必要条件任一被突破，死锁就不会产生。在咱们日常做开发应用锁时，防止死锁的形式通常是突破条件 2 与条件 4。
突破 2：申请锁失败后，要将之前申请的锁开释掉。不能够期待锁的同时再申请锁。
突破 4：防止造成环路期待，一种简略的做法是将锁进行编号。如：锁 1、锁 2、锁 3。申请锁时秉承的准则是 按序号递增的去申请锁，比方 线程 A 须要 锁 1 与 锁 3，要先申请 1 再申请 3，而不能申请 3 再 申请 1，线程 B 须要 锁 1 与锁 2，要先申请锁 1 再申请锁 2，这样 线程 A 与 线程 B 申请的锁就不会造成环路了。
那么 MySql 是如何解决死锁问题的呢，实际上 MySql 是有死锁检测的，也就是说当产生死锁时 MySql 是能够检测进去的，当多个事务产生死锁时，MySql 会抉择一个代价较小的事务进行回滚。死锁检测会耗费一部分 Mysql 的性能，能够通过参数设置关掉，但不倡议这样做。死锁检测关掉后，MySql 能够通过锁超时机制变向解决死锁问题，当锁等待时间超出设定的阈值时会主动回滚事务。锁超时设置办法见附录。

上面就简略列举几种 MySql 会产生死锁的场景。
4.2 死锁的几种场景
4.2.1 因为 Share Mode 使用不当导致的死锁
如下语句应用的仍然是 student 表。
session1session2T1BEGIN;SELECT from student where student_id > 6 and student_id < 20 LOCK IN SHARE MODE;BEGIN;T2SELECT from student where student_id > 6 and student_id < 20 LOCK IN SHARE MODE;T3INSERT into student VALUES(19,’Lucy’,21);T4INSERT into student VALUES(19,’Lucy’,21);dead lock
死锁的起因：
session 1 在 T1 时刻申请了读锁，session 2 在 T2 时刻也申请了读锁。session 2 在 T3 时刻申请写锁时，要期待 session 1 的读锁。session 1 在 T4 时刻申请写锁时，要期待 session2 的读锁。造成了环路期待，导致了死锁。

所以 应用 Lock in share mode 时，不倡议更新记录，很容易造成死锁并产生回滚。

4.2.2 因为更新程序不当引起的死锁
CREATE TABLE t (
id int(11) NOT NULL AUTO_INCREMENT,
v int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

session1session2T1BEGIN;update t set v = v+1 where id = 1;T2begin;update t set v = v+1 where id = 2;T3update t set v = v+1 where id = 2;T4update t set v = v+1 where id = 1;deadlock

死锁的起因：
session 1 在 T1 时刻 将表 t 的 id= 1 行加了 典型行锁。session 2 在 T2 时刻 将表 t 的 id = 2 行加了典型行锁。T3 时刻 session 1 要申请 id= 2 行的写锁，此事因为 id= 2 行的写锁曾经被 session 2 申请了，所以 session 1 要期待 session 2 开释 id= 2 的写锁。T4 时刻 session 2 要申请 id= 1 的行锁，但 id= 1 的行锁被 session 1 占用着。所以 session 1 与 session2 造成了环路期待，进而导致了死锁。

4.2.3 并发争抢产生的死锁
CREATE TABLE t (
c int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
PRIMARY KEY (c)
) ENGINE=InnoDB;

session 1
session1session2session3T1BEGIN;INSERT into t VALUES(5,5);T2INSERT into t VALUES(5,5);blockT3INSERT into t VALUES(5,5);blockT4ROLLBACK;Affected rows: 1Deadlock found

对于这种状况的死锁，说实话，我并未找到正当的起因解释。如果你有正当的解释，欢送评论区解惑。

5 总结
回顾下本篇内容，首先介绍了 MySql 中有哪些锁及这些锁的基本概念，让大家对锁有个全局的认知。其次，通过数据一致性与幻读场景引出为什么要加锁，以及 InnoDB 中的行级锁是如何工作的。最初介绍了几种产生死锁的场景。本文中的 Sql 语句均可间接拷贝至 MySql 运行，所以倡议大家都能亲自动手执行下，这样印象会更加粗浅。

最初再次强调，以上试验均为 RR 级别。如果隔离级别改为 读已提交 RC（read committed），则 for update，in share mode 等行级锁，则进化成典型行锁，即不会加 gap lock。如果在 RC 隔离级别下执行 3.1 节例子，则会由不一样的后果，感兴趣的你无妨试一下。
session1session2T1BEGIN;SELECT from student where student_id > 6 and student_id < 20 FOR UPDATE;T2SELECT from student where age > 6 and age < 20;block？noT3INSERT into student VALUES(17,’Lily’,17);block?noT4INSERT into student VALUES(21,’Lily’,21);block?noT5NSERT into student VALUES(26,’Lily’,26);block?No

附录
查看隔离级别
show variables like ‘transaction%’;
设置隔离级别
– 可反复读
set global transaction isolation level repeatable read;
– 读已提交
set global transaction isolation level read committed;
查看 mysql 版本
select version();
设置锁超时工夫
SHOW GLOBAL VARIABLES LIKE ‘innodb_lock_wait_timeout’;
SET GLOBAL innodb_lock_wait_timeout=3600;