图解MySQL锁篇

MySQL有哪些锁？

大纲

全局锁

全局锁是这么用的？

要使用全局锁，则要执行这条命令：

flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

• 对数据的增删改操作，比如insert、delete、update等语句；
• 对表结构的更改操作，比如alter table、drop table等语句。

如果要释放全局锁，则要执行这条命令：

unlock tables

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

全局锁应用场景是什么？

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

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

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

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

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。
但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

表级锁

MySQL表级锁有哪些？具体怎么用的？

MySQL里面表级别的有：

• 表锁
• 元数据锁(MDL)
• 意向锁
• AUTO-INC锁

表锁

使用下面的命令：

// 表级别的共享锁，也就是读锁；
// 允许当前会话读取被锁定的表，但阻止其他会话对这些表进行写操作。
lock tables t_student read;

// 表级别的独占锁，也就是写锁；
// 允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作(读或写)
lock tables t_student write;

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

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式，不过尽量避免在使用 InnoDB 引擎

元数据锁

我们不需要显示的使用MDL，因为当我们对数据库表进行操作时，会自动给这个表加上MDL：

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

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

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

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

那如果数据库有一个长事务(所谓的长事务，就是开启了事务，但是一直还没提交)，那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

1. 首先，线程A先启用了事务(但是一直不提交)，然后执行一条select 语句，此时就先对该表加上MDL读锁；
2. 然后，线程B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
3. 接着，线程C修改了表字段，此时由于线程A的事务并没有提交，也就是MDL读锁还在占用着，这时线程C就无法申请到MDL写锁，就会被阻塞。

那么在线程C阻塞后，后续有对该表的select语句，就都会被阻塞，如果此时有大量该表的select语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程C因为申请不到MDL写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现写锁等待，会阻塞后续该表的所有CRUD操作。
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的常长事务，是否有事务已经对表加上了MDL读锁，如果可以考虑kill掉这个长事务，然后再做表结构的变更。

意向锁

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
• 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」。

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。
而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。
不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

// 先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

// 先在表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁(lock tables ... read)和独占表锁(lock table ... write)发生冲突。

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

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

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

AUTO-INC锁

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

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

在插入数据时，会加一个表级别的AUTO-INC锁，然后为被AUTO_INCREMENT修饰的字段赋值递增的值，等插入语句执行完成后，才会把AUTO-INC 锁释放掉。
但是， AUTO-INC 锁对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

在MySQL 5.1.22版本开始，InnoDB存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候，会为被AUTO_INCREMENT修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB存储引l擎提供了个innodb_autoinc_lock_mode的系统变量，是用来控制选择用AUTO-INC锁，还是轻量级的锁。

• 配置为0，采用 AUTO-INC 锁，语句执行结束后才释放锁；
• 配置为1：

• 普通insert语句，自增锁在申请之后就马上释放；
• 类似insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

• 配置为2：采用轻量级锁，申请自增主键后就释放锁

当配置为是性能最高的方式，但是当搭配binlog的日志格式是statement一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。

会发生session B的insert语句，生成的id不连续(其他session A在次期间添加了一条记录)。

当「主库」发生了这种情况，binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句，如果binlog_format=statement，记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句，要么先记 session B 的 insert 语句。

但不论是哪一种，这个binlog 拿去「从库」执行，这时从库是按「顺序」执行语句的，只有当执行完一条SQL语句后，才会执行下一条SQL。因此，在从库上「不会」发生像主库那样两个session「同时」执行向表t2中插入数据的场景。所以，在备库上执行了sessionB的insert语句，生成的结果里面，id都是连续的。这时，主从库就发生了数据不一致。

要解决这问题，binlog 日志格式要设置为row，这样在binlog里面记录的是主库分配的自增值，到备库执行的时候，主库的自增值是什么，从库的自增值就是什么。

所以，当innodb_autonic_lock_mode=2时，并且 binlog_format =row，既能提升并发性，又不会出现数据一致性问题。

行级锁

InnoDB引擎时支持行级锁的，而MyISAM引擎并不支持行级锁。
普通的select语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

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

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

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

共享锁满足读读共享，读写互斥；独占锁满足写写互斥，读写互斥。

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

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

Record(Record Lock)

Record Lock称为记录锁，锁住的是一条记录。而且记录锁是有S锁和X锁之分的：

• 当一个事务对一条记录加了S型记录锁后，其他事务也可以继续对该记录加S型记录锁(S型与S锁兼容)，但是不可以对该记录加X型记录锁(S型与X型锁不兼容)；
• 当一个事务对一条记录加了X型记录锁后，其他事务既不可以对该记录加S型记录锁(S 型与 X 锁不兼容)，也不可以对该记录加 X 型记录锁(X 型与 X 锁不兼容)。

对t_test表主键为1的这条记录加X型的记录锁：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

Gap(Gap Lock)

Gap Lock 称为间隙锁，存在于可重复读隔离级别和串行化隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

Next-Key(Next-Key Lock)

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

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

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁(next-keylock 也包含间隙锁)。
如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止(释放间隙锁的时刻)，在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

举个例子，假设事务 A 已经对表加了一个范围 id 为(3，5)间隙锁。

当事务A还没提交的时候，事务B向该表插入一条id=4的新记录，这时会判断到插入的位置已经被事务A加了间隙锁，于是事务B会生成一个插入意向锁，然后将锁的状态设置为等待状态(PS：MySQL加锁是，是先生成锁架构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁)，此时事务B就会发生阻塞，直到事务A提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。(？？？)
插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁(当然，插入意向锁如果不在间隙锁区间内则是可以的)。

MySQL是怎么加锁的？(？？？)

什么SQL语句会加行级别锁？

如果要在查询时对记录加行级锁，中查询会加锁的语句称为锁定读。

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

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

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

除了上面这两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。

// 对操作的记录加独占锁(x型锁)
update table ... where id = 1;

// 对操作的记录加独占锁(x型锁)
delete from table where id = 1;

行级锁有哪些种类？

不同隔离级别下，行级锁的种类是不同的。
在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。
在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁(目的是为了避免幻读)，所以行级锁的种类主要有三类：

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

Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

• 当一个事务对一条记录加了S型记录锁后，其他事务也可以继续对该记录加 S 型记录锁(S 型与 S 锁兼容)，但是不可以对该记录加 X 型记录锁(S 型与 X 锁不兼容);
• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁(S 型与 X 锁不兼容)，也不可以对该记录加 X 型记录锁(X 型与 X 锁不兼容)。

举例：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

Gap Lock

Gap Lock 称为间隙锁，存在于可重复读隔离级别和串行化隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为(3，5)间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

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

Next-Key Lock

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

所以，next-key lock 即能保护该记录，又能阻止其他事务将新记录插入到被保护记录前面的间隙中。

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

MySQL是怎么加行级锁的？

行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是有记录锁和间隙锁组合而成的，next-key lock是前开后闭区间，而间隙锁是钱凯后开区间。
但是。next-key lock在一些场景下会退化成记录锁或间隙锁。

在能使用记录锁或者间隙锁就能避免幻读现象的场景下，next-key lock 就会退化成记录锁或间隙锁。

唯一索引(主键索引)等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的next-key lock会退化成「记录锁」。
• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的next-key lock会退化成「间隙锁」。

1、记录存在的情况

假设事务 A 执行了这条等值查询语句，查询的记录是「存在」于表中的。

那么，事务 A 会为 id 为 1 的这条记录就会加上 X 型的记录锁。

接下来，如果有其他事务，对 id 为 1 的记录进行更新或者删除操作的话，这些操作都会被阻塞，因为更新或者删除操作也会对记录加 X 型的记录锁，而 X 锁和 X 锁之间是互斥关系。

有什么命令可以分析加了什么锁？

我们可以通过select * from performance_schema.data_locks\G;这条语句，查看事务执行SQL进程中加了什么锁。

我们以前面的事务 A 作为例子，分析下下它加了什么锁。

通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

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

从这里我们也可以得知，加锁的对象是针对索引，因为这里查询语句扫描的B+树是聚簇索引I树，即主键索引树，所以是对主键索引加锁。将对应记录的主键索引加记录锁后，就意味着其他事务无法对该记录进行更新和删除操作了。

为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的next-key lock会退化成记录锁？

原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。

幻读的定义就是，当一个事务前后两次查询的结果集，不相同时，就认为发生幻读。所以，要避免幻读就是避免结果集某一条记录被其他事务删除，或者有其他事务插入了一条新记录，这样前后两次查询的结果集就不会出现不相同的情况。

• 由于主键具有为唯一性，所以其他事务插入id=1的时候，会因为主键冲突，导致无法插入id=1的新纪录。这样事务A在多次查询id=1的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。
• 由于对 id = 1 加了记录锁，其他事务无法删除该记录，这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。

2、记录不存在的情况

此时事务 A 在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是 (1, 5)。

间隙锁的范围(1, 5)，是怎么确定的？

如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围「右边界」，此次的事务 A 的 LOCK_DATA 是 5。
然后锁范围的「左边界」是表中 id 为 5 的上一条记录的 id 值，即 1。
因此，间隙锁的范围(1, 5)。

为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的next-key lock会退化为「间隙锁」？

原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。

• 为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？如果是 next-key lock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了，所以即使 id =5 被删除，也不会有什么影响，那就没必须加 next-key lock，因此只需要在 id = 5 加间隙锁，避免其他事务插入 id = 2 的新记录就行了。
• 为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录。

唯一索引(主键索引)范围查询(？？？)

范围查询和等值查询的加锁规则是不同的。
当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

• 情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的next-key锁会退化成记录锁。
• 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：

• 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
• 条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

非唯一索引等值查询(？？？)

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引(二级索引)，所以在加锁时，同时会对这两个索引都加锁，是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引范围查询(？？？)

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

没有加索引的查询

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

update没加索引会锁全表？

在线上执行一条 update 语句修改数据库数据的时候，where 条件没有带上索引，导致业务直接崩了，被老板教训了一波

为什么会发生这种的事故？

当我们执行 update 语句时，实际上是会对记录加独占锁(X 锁)的，如果其他事务对持有独占锁的记录进行修改时是会被阻塞的。另外，这个锁并不是执行完 update 语句就会释放的，而是会等事务结束时才会释放。
在 InnoDB 事务中，对记录加锁带基本单位是 next-key 锁，但是会因为一些条件会退化成间隙锁，或者记录锁。加锁的位置准确的说，锁是加在索引上的而非行上。

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key锁(记录锁 + 间隙锁)，相当于把整个表锁住了。

可以看到，这次事务B的 update 语句被阻塞了。

这是因为事务A的update 语句中 where 条件没有索引列，触发了全表扫描，在扫描过程中会对索引l加锁，所以全表扫描的场景下，所有记录都会被加锁，也就是这条update语句产生了4个记录锁和5个间隙锁，相当于锁住了全表。

因此，当在数据量非常大的数据库表执行 update 语句时，如果没有使用索引，就会给全表的加上 next-key 锁， 那么锁就会持续很长一段时间，直到事务结束，而这期间除了 select … from语句，其他语句都会被锁住不能执行，业务会因此停滞。

那 update 语句的 where 带上索引就能避免全表记录加锁了吗？
关键还得看这条语句在执行过程中，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

如何避免这种事故的发生？

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。
大致的意思是，当 sql_safe_updates 设置为 1 时。
update语句必须满足如下条件之一才能执行成功：

• 使用where，并且where条件中必须有索引列；
• 使用limit；
• 同时使用where和limit，此时where条件中可以没有索引列。

delete语句必须满足以下条件能执行成功：

• 同时使用where 和 limit，此时 where 条件中可以没有索引列；

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

MySQL记录锁+间隙锁可以防止删除操作而导致的幻读吗？

总结

在 MySQL 的可重复读隔离级别下，针对当前读的语句会对索引加记录锁+间隙锁，这样可以避免其他事务执行增、删、改时导致幻读的问题。

这样可以避免其他事务执行增、删、改时导致幻读的问题。一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

MySQL死锁了，怎么办？

死锁的发生

两个事务都陷入了等待状态(前提没有打开死锁检测)，也就是发生了死锁，因为都在相互等待对方释放锁。

为什么会产生死锁？

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

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

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

为什么间隙锁与间隙锁之间是兼容的？

间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁。

这里的共同间隙包括两种场景：

• 其一是两个间隙锁的间隙区间完全一样；
• 其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。

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

Insert语句时怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

什么是隐式锁？

当事务需要加锁时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁。隐式锁是InnoDB实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。
隐式锁就是在Insert过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁，这里列举两个场景：

• 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的；
• 如果Insert的记录和已有记录存在唯一键冲突，此时也不能插入记录。

1、记录之间加有间隙锁

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态(PS：MySQL加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁)，现象就是Insert语句会被阻塞。

2、遇到唯一键冲突

如果在插入新记录时，插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录(不过可以有多条记录的唯一二级索引列的值同时为NULL，这里不考虑这种情况)，此时插入就会失败，然后对于这条记录加上了S型的锁。

• 如果主键索引重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加S型记录锁。
• 如果唯一二级索引重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加S型next-key锁。

如何避免死锁？

死锁的四个必要条件：互斥、持有且等待、不可剥夺、循环等待。只要系统发生死锁，这些条件必然成立，但是只有破坏任意一个条件就死锁就不会成立。
在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在InnoDB中，参数innodb_lock_wait_timeout是用来设置超时时间的，默认值是50秒。
  当发生超时后，就出现下面这个提示：
• 开启主动死锁检测。主动死锁检测在发生死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect设置为on，表示开启这个逻辑，默认就开启。
  当检测到死锁后，就会出现下面这个提示：
  上面这个两种策略是「当有死锁发生时」的避免方式。

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

字节面试：加了什么锁，导致死锁的？

启动两个事务，按照题目的 SQL 执行顺序，过程如下表格：

可以看到，事务 A 和 事务 B 都在执行 insert 语句后，都陷入了等待状态(前提没有打开死锁检测)，也就是发生了死锁，因为都在相互等待对方释放锁。

为什么会发生死锁？

我们可以通过select * from performance_schema.data_locks\G;这条语句，查询事务执行SQL过程中加了什么锁。
接下来，针对每一条 SQL 语句分析具体加了什么锁。

Time 1阶段加锁分析

Time 1 阶段，事务 A 执行以下语句：

# 事务A
mysql> begin;
uery OK, 0 rows affected (0.00 sec)

mysql> update t_student set score = 100 where id = 25;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

然后执行select * from performance_schema.data_locks\G; 这条语句，查看事务 A 此时加了什么锁。

从上图可以看到，共加了两个锁，分别是：

• 表锁：X类型的意向锁；
• 行锁：X类型的间隙锁。

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

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

因此，此时事务A在主键索引(INDEX_NAME：PRIMARY)上加的是间隙锁，锁范围是(20， 30)。

间隙锁的范围是(20, 30) ，是怎么确定的？

根据作者的经验，如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围最右值，此次的事务 A 的 LOCK_DATA 是 30。
然后锁范围的最左值是 t_student 表中 id 为 30 的上一条记录的 id 值，即 20。
因此，间隙锁的范围(20, 30)。

Time 2阶段加锁分析

Time 2 阶段，事务 B 执行以下语句：

# 事务 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_student set score = 100 where id = 26;
Query OK, 0 rows affected (0.01 sec)
s matched: 0  Changed: 0  Warnings: 0

查看事务 B 此时加了什么锁。

从上图可以看到，行锁是 X 类型的间隙锁，间隙锁的范围是(20, 30)。

事务A和事务B的间隙锁范围都是一样的，为什么不会冲突？

两个事务的间隙锁之间是相互兼容的，不会产生冲突。

在MySQL官网上还有一段非常关键的描述：
间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取一个间隙范围的间隙锁，，共享(S型)和排他(X型)的间隙锁是没有区别的，他们相互不冲突，且功能相同。

Time 3阶段加锁分析

事务A插入了一条记录：

# Time 3 阶段，事务 A 插入了一条记录
mysql> insert into t_student(id, no, name, age,score) value (25, 'S0025', 'sony', 28, 90);
    /// 阻塞等待......

此时，事务 A 就陷入了等待状态。

查看事务 A 在获取什么锁而导致被阻塞。

可以看到，事务 A 的状态为等待状态(LOCK_STATUS: WAITING)，因为向事务 B 生成的间隙锁(范围(20, 30))中插入了一条记录，所以事务 A 的插入操作生成了一个插入意向锁(LOCK_MODE:INSERT_INTENTION)。

插入意向锁是什么？

插入意向锁名字里虽然有意向锁这三个字，但是它并不是意向锁，它属于行级锁，是一种特殊的间隙锁。

MySQL的官方文档描述：尽管插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。
如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁(当然，插入意向锁如果不在间隙锁区间内则是可以的)。所以，插入意向锁和间隙锁之间是冲突的。

另外，作者补充一点，插入意向锁的生成时机：

• 每插入一条新纪录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态(PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁)，现象就是Insert语句会被阻塞。

Time 4阶段加锁分析

Time 4，事务 B 插入了一条记录：

# Time 4 阶段，事务 B 插入了一条记录
mysql> insert into t_student(id, no, name, age,score) value (26, 'S0026', 'ace', 28, 90);
    /// 阻塞等待......

此时，事务B就键入了等待状态。

查看事务B在获取什么锁而导致被阻塞。

可以看到，事务 B 在生成插入意向锁时而导致被阻塞，这是因为事务 B 向事务 A 生成的范围为 (20, 30) 的间隙锁插入了一条记录，而插入意向锁和间隙锁是冲突的，所以事务 B 在获取插入意向锁时就陷入了等待状态。

最后回答，为什么会发生死锁?

本次案例中，事务A和事务B在执行完后update语句后都持有范围为(20， 30)的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对象事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可抢占性、循环等待，因此发生了死锁。

总结

两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。

在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系。

如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。