MySql之锁的详解（干货请备好咖啡）

1.锁的概念

在数据库中，除传统的计算资源（如 CPU 、 RAM 、 I/O 等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制 ，因此产生了数据库锁 。

加锁的目的是什么？

数据库的锁是为了解决事务的隔离性问题，为了让事务之间相互不影响。

如果你想一个人静一静，不被别人打扰，那么请在你的房门上加上一把锁。

2.锁的分类

类型	说明
全局锁	对整个数据库加锁
表级锁	每次操作锁住整张表，分为表锁、意向锁、元数据锁以及AUTO-INC锁
行级锁	每次操作锁住对应的行数据，分为记录锁、间隙锁以及临键锁

3.全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

• 数据更新语句MDL（数据的增删改）；
• 数据定义语句DDL（包括建表、修改表结构等）；
• 更新类事务的提交语句。

全局锁的命令：

Flush tables with read lock

执行后，整个数据库就处于只读状态了。如果需要释放锁请执行命令：

unlock tables

当然，当会话断开了，全局锁会被自动释放。

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

加全局锁又会带来什么缺点呢？

加上全局锁，意味着整个数据库都是只读状态。

那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

• 案例一：

-- 会话窗口1，操作如下：
-- 1.打开一个数据库连接，加全局锁
flush tables with read lock;
-- 4.释放锁
unlock tables;
-- 会话窗口2，操作如下：
-- 2.查询表
select * from mylock;
-- 3.执行删除语句，失败
delete from mylock where id = 1;
-- 5.再次执行删除语句，成功
delete from mylock where id = 1;

注意：上述案例请按照序号执行。

4.表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、

InnoDB、BDB等存储引擎中。MySQL 里面表级别的锁有这几种：

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

4.1.表锁

表锁由 MySQL Server 实现，一般在执行DDL语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作。在执行 SQL 语句时，也可以明确指定对某个表进行加锁。命令如下：

-- 表级别的共享锁，也就是读锁；
lock tables 表名 read;
-- 表级别的独占锁，也就是写锁；
lock tables 表名 write;

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

也就是说如果本线程对某表加了「共享表锁」，那么本线程接下来如果要对某表执行写操作的语句，是会被阻塞的，当然其他线程对某表进行写操作时也会被阻塞，直到锁被释放。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

• 案例一：表级共享读锁

-- 会话1窗口，操作如下：
-- 1.加表级共享读锁
lock tables mylock read;
-- 2.查询数据
select * from mylock;
-- 3.插入数据
-- 1099 - Table 'mylock' was locked with a READ lock and can't be updated
insert into mylock values(1,'小强');
-- 9.释放锁
unlock tables;
-- 会话2窗口，操作如下：
-- 4.在会话2窗口再次加表级共享读锁，成功
lock tables mylock read;
-- 5.查询数据
select * from mylock;
-- 6.新增数据
-- 1099 - Table 'mylock' was locked with a READ lock and can't be updated
insert into mylock values(1,'小明');
-- 7.释放锁
unlock tables;
-- 8.再次新增数据，由于会话1窗口有表级读锁，所以处于等待状态
insert into mylock values(1,'小明');

注意：上述案例请按照序号执行。

• 案例二：表级独占写锁

-- 会话1窗口，操作如下：
-- 1.加表级排他锁
lock tables mylock write;
-- 2.当前会话被锁定的表执行查询操作被阻塞
-- select * from mylock;
-- 3.执行修改操作，OK
update mylock set name = '小红' where id = 2;
-- 5.释放锁
unlock tables;
-- 会话2窗口，操作如下：
-- 4.进行查询（修改/新增）时被阻塞，需要等待锁的释放
select * from mylock;

注意：上述案例请按照序号执行。

4.2.意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

意向锁是一个比较抽象的表锁，它会在我们加表锁或行锁的时候自动加上意向锁，所以它的作用主要是减少我们去检查表中是否有锁的查询资源，意向锁人为不可控（不可自行添加意向锁）

InnoDB 支持多粒度锁（ multiple granularity locking ） ，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁 。意向锁分为两种：

• 意向共享锁（intention shared lock，IS）：事务有意向对表中的某些行为加共享锁（S锁）

select column from table ... lock in share mode;

• 意向排他锁（intention exclusive lock，IX）：事务有意向对表中的某些行为加排他锁（X锁）

select column from table ... for update;

虽然意向锁可以被称为表锁，但是我更倾向于把它跟表锁和行锁区分开来，将它理解为一个标记会更好（标记本表中已经添加锁）

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

• 案例一：

-- 会话1窗口，操作如下：
-- 1.开启事务，方便观察加锁情况
begin;
-- 2.加行级独占锁（X），同时mylock表自动加上表级意向独占锁（IX），即对表也上了锁
select * from mylock where id = 3 for update;
-- 5.提交事务即释放锁
commit
-- 会话2窗口，操作如下：
-- 3.开启事务
begin;
-- 4.尝试对mylock表加读锁失败，被阻塞；等待会话1窗口第5步释放锁后才能成功
lock table mylock read;

意向共享锁IS和意向独占锁IX是表级锁，不会和行级的X和S发生冲突。只会和表级的X和S 发生冲突。

4.3.元数据锁

meta data lock , 元数据锁，简写MDL。我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL（即：MDL元数据锁是隐含的锁）：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 不需要显示调用，那它是在什么时候释放的?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

• 案例一：

-- 会话1窗口，操作如下：
-- 1.开启事务
begin;
-- 2.查询数据，加MDL锁
select * from mylock;
-- 4.通过commit或者rollback释放MDL锁
commit;
-- 会话2窗口，操作如下：
-- 3.修改mylock表的结构，被阻塞；当会话1窗口中的commit或者rollback后执行成功
alter table mylock add age int;

4.4.AUTO-INC锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

所以AUTO-INC锁的意义就是保证同一时间只有一个一个事务进行插入表的操作，同时主键自增也有AUTO-INC的功劳哦

5.行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读（MVCC）。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

行级锁的类型主要有三类：

• Record Lock：记录锁，也就是仅仅把一条记录锁上；
• Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

5.1.记录锁

唯一性索引（唯一/主键）等值精确查询匹配。

Record Lock 称为记录锁

记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列 
UPDATE SET age = 50 WHERE id = 1;

Record Lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

• 案例一：

-- 会话1窗口，操作如下：
-- 1.开启事务
begin;
-- 2.通过主键id=1实现行级记录锁，锁住id=1
update mylock set name = '小张' where id = 1;
-- 3.查询记录，发现id=1的name名称被修改成功
select * from mylock;
-- 8.提交事务
commit;
-- 会话2窗口，操作如下：
begin;
-- 4.使用id为非1的条件加锁成功
select * from mylock where id = 2 lock in share mode;
-- 5.继续使用id=1加行级共享锁，在RR级隔离级别下加共享锁失败
select * from mylock where id = 1 lock in share mode;
-- 6.修改id非1的name名称，修改成功
update mylock set name = '小鱼鱼' where id = 2;
-- 7.修改id为1的name名称，处于阻塞状态。此时可以再开一个会话窗口执行：select * from information_schema.INNODB_LOCKS; 查看结果。
update mylock set name = '小月月' where id = 1;
-- 9.提交事务
commit;

5.2.间隙锁

查询条件需记录不存在。

Gap Lock 称为间隙锁，只存在于可重复读（RR）隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

• 区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
• 在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
• 间隙锁可用于防止幻读，保证索引间的不会被插入数据。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

创建数据库并初始化：

create table t_student(
   id int not null primary key,
	 name varchar(20) not null,
	 class varchar(20) not null
) comment '学生信息表';
insert into t_student values(1,'zs','一年级一班'),(3,'ls','二年级一班'),(9,'ww','三年级一班'),(14,'xq','四年级一班'),(20,'ll','五年级一班');

总结： 保证某个间隙内的数据在锁定情况下不会发生任何变化。

• 案例一：

-- 会话1窗口，操作如下：
-- 1.开启事务
begin;
-- 2.在会话1窗口中针对(3,9)之间的进行行级间隙锁加锁，确保在此期间再次加入新数据时产生幻读问题
select * from t_student where id = 5 lock in share mode;
-- 间隙锁，锁住(3,9)
select * from t_student where id > 5  and id < 9 lock in share mode;
-- 间隙锁，锁住(20,正无穷大)
select * from t_student where id > 25 lock in share mode;
-- commit;
-- 会话2窗口，操作如下：
-- 3.开启事务
begin;
-- 4.会话2窗口中仍然可以对id=5这条记录间隙锁
select * from t_student where id = 5 for update;
-- commit;
-- 会话3窗口，操作如下：
-- 5.开启事务
begin;
-- 6.会话3窗口新增一条id=6的记录，由于会话1和会话2窗口有间隙锁，此时新增操作将被阻塞
insert into t_student values(6,'haha','一年级二班');
-- commit;

5.3.临键锁

查询条件采用范围查询，可以包含记录和区间。

有时候我们既想锁住某条记录 ，又想阻止其他事务在该记录前边的间隙插入新记录 ，所以 InnoDB 就提出了一种称之为 Next - Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为 next- key 锁 。

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（4，7] 的 next-key lock，那么其他事务即不能插入 id = 6 记录，也不能修改 id = 5 这条记录。所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

• 案例一：

-- 会话1窗口，操作如下：
-- 1.开启事务
begin;
-- 2.临键锁，锁住(9,14]
select * from t_student where id > 9 and id <=14 for update;
-- 提交事务
commit;
-- 会话2窗口，操作如下：
-- 3.开启事务
begin;
-- 4.尝试对id=14的记录加共享锁，被阻塞
select * from t_student where id = 14 lock in share mode;
-- 5.尝试插入id=12的记录，被阻塞
insert into t_student values(12,'yy','二年级三班');

6.死锁

有个业务主要逻辑就是新增订单、修改订单、查询订单等操作。然后因为订单是不能重复的，所以当时在新增订单的时候做了幂等性校验，做法就是在新增订单记录之前，先通过 select ... for update 语句查询订单是否存在，如果不存在才插入订单记录。

而正是因为这样的操作，当业务量很大的时候，就可能会出现死锁。说个有意思的段子：

面试官: 解释下什么是死锁?
应聘者: 你录用我,我就告诉你
面试官: 你告诉我,我就录用你
应聘者: 你录用我,我就告诉你
面试官: 卧槽滚！
...........

6.1.死锁的发生

本次案例使用存储引擎 Innodb，隔离级别为可重复读（RR）。

我建了一张订单表，其中 id 字段为主键索引，order_no 字段普通索引，也就是非唯一索引：

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order` (`order_no`) USING BTREE
) ENGINE=InnoDB;

往数据表中添加数据，如下：

insert into t_order(order_no,create_date) values(1001,now());
insert into t_order(order_no,create_date) values(1002,now());
insert into t_order(order_no,create_date) values(1003,now());
insert into t_order(order_no,create_date) values(1004,now());
insert into t_order(order_no,create_date) values(1005,now());
insert into t_order(order_no,create_date) values(1006,now());

假设这时有两事务，一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录，过程如下：

事务A	事务B
–1.开启事务 begin;
– 2.检查1007的订单是否存在 select id from t_order where order_no = 1007 for update;
	– 3.开启事务 begin;
	– 4.查询1008的订单是否存在 select id from t_order where order_no = 1008 for update;
– 5.如果没有，则插入订单记录，阻塞等待 insert into t_order(order_no,create_date) values(1007,now());
	– 6.如果没有，则插入订单记录 – 提示：1213 - Deadlock found when trying to get lock; try restarting transaction insert into t_order(order_no,create_date) values(1008,now());

可以看到，事务A陷入了等待状态（前提没有打开死锁检测），而事务B则直接报错提示出现死锁。

这里在查询记录是否存在的时候，使用了 select ... for update 语句，目的为了防止事务执行的过程中，有其他事务插入了记录，而出现幻读的问题。

6.2.为什么会产生死锁？

可重复读隔（RR）离级别下，是存在幻读的问题。

Innodb 引擎为了解决「可重复读」隔离级别下的幻读问题，就引出了 next-key 锁，它是记录锁和间隙锁的组合。

• Record Lock，记录锁，锁的是记录本身；
• Gap Lock，间隙锁，锁的就是两个值之间的空隙，以防止其他事务在这个空隙间插入新的数据，从而避免幻读现象。

事务 A 在执行下面这条语句的时候：

insert into t_order(order_no,create_date) values(1007,now());

可以通过 select * from information_schema.INNODB_LOCKS;这条语句，查看事务执行 SQL 过程中加了什么锁。

图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思，通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

• 如果 LOCK_MODE 为 X，说明是 X 型的 next-key 临键锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是 X 型的记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是 X 型的间隙锁；

因此，此时事务 A 在二级索引（INDEX_NAME : index_order）上加的是 X 型的 next-key 锁，锁范围是(1006, +∞]。

next-key 锁的范围 (1006, +∞]，是怎么确定的？

• 表中最后一个记录的order_no为 1006，那么等值查询 order_no = 1007（不存在），就是next key lock锁。

• 表中最后一个记录的order_no为 1010，那么等值查询 order_no = 1007（不存在），就是间隙锁

select id from t_order where order_no = 1008 for update;

因为当我们执行以上插入语句时，会在插入间隙上获取插入意向锁，而插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以两个事务中 select … for update 语句并不会相互影响。

案例中的事务 A 和事务 B 在执行完后 select ... for update 语句后都持有范围为(1006,+∞]的next-key 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

6.3.如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

  当发生超时后，就出现下面这个提示：

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

  当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。