面试官：说一下MySQL中的锁机制吧

5. 1MySQL有哪些锁？

为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。 锁冲突 也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

按照数据操作的类型，可以分为读锁、写锁。

• 读锁 ：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。

• 写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁

使用全局锁，整个数据库就处于只读状态了。

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

• 缺点：业务只能读数据，而不能更新数据，这样会造成业务停滞。

• 避免方法：使用可重复读的隔离级别，在备份数据库之前先开启事务，整个数据库的数据就都是可重复读的，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

  # 加全局锁 FTWRL
  flush tables with read lock 
  

表级锁

MySQL 里面表级别的锁有：表锁、元数据锁（MDL）、意向锁、AUTO-INC 锁。

• 表锁

  表锁也有表级别的共享锁，表级别的独占锁。

  表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。表锁的颗粒度太大，会影响并发性能，应该尽量避免使用表锁。

• 元数据锁（Meta Datebase Lock，MDL）

  对一张表进行 CRUD 操作时，加的是 MDL 读锁；

  • 只允许读，不能做结构的修改，比如修改表字段等。

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

  • 只允许写，修改表结构时不能通过CRUD读取数据。

  MDL 不需要显示调用，在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

  申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待（线程申请不到 MDL 写锁），会阻塞后续该表的所有 CRUD 操作（MDL读锁）。

  • 所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

• 意向锁

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

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

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

  普通的SELECT语句利用MVCC实现一致性读，是无锁的，但是可以使用以下方式加锁：

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

  意向锁之间不冲突，也不会和行级的共享锁和独占锁发生冲突，只会和共享表锁（lock tables … read）或独占表锁（lock tables … write）发生冲突。

• AUTO-INC 锁（自动增长）

  表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

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

  • InnoDB 存储引擎提供了一种轻量级的锁来实现自增。只是在赋值完成后，就把该锁释放。

行级锁

对于表锁和行锁，满足读读共享、读写互斥、写写互斥的。

不同隔离级别下，行级锁的种类不同。

• 在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。

• 在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（目的是为了避免幻读）。

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

• 记录锁（Record Lock），也就是仅仅把一条记录锁上；记录锁是有 S 锁和 X 锁之分的。

  • 共享锁（S锁）指的就是对于多个不同的事务，对同一个资源共享同一个锁。 相当于对于同一把门，它拥有多个钥匙一样。
  • 独占锁（X锁）也叫排他锁，是指该锁一次只能被一个线程所持有。

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

• 间隙锁（Gap Lock），锁定一个范围，但是不包含记录本身；只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

  • 间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁仅仅是为了防止插入幻影记录而提出的。

• 临键锁（Next-Key Lock），Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。next-key lock 即能保护记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

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

• 插入意向锁：插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。存在间隙锁时，执行Insert语句时会用到。

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

5.2 MySQL是怎么加锁的？

1. 从语句角度来看

普通的SELECT默认是不加锁的，属于快照读，是使用MVCC的方式实现的。但是可以在查询时对记录加行级锁，查询会加锁的语句称为锁定读。锁定读的语句必须在事务中，因为当事务提交了，锁就会被释放。而update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。

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

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

而Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。但此时记录之间加有间隙锁，隐式锁会转换为显示锁。

如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态，现象就是Insert语句被阻塞。

• 因为插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。

如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的，因为插入意向锁会被设置为等待状态；

如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录，会对这条记录加上S型的锁；

• 至于是记录锁，还是 next-key 锁，跟是「主键冲突」还是「唯一二级索引冲突」有关系。
  • 如果主键冲突：给已存在的主键索引记录添加S型记录锁。
  • 如果唯一二级索引冲突：给已存在的二级索引记录添加S型next-key锁。

2. 从MySQL角度来看

MySQL加锁的对象是索引，加锁的基本单位是 next-key lock。

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

唯一索引等值查询，加锁情况分析：（主键索引为例）

• 当查询的记录是存在的，在索引树上定位到这一条记录后，该记录的索引中的 next-key lock 会退化成「记录锁」。
  • 等值查询唯一索引，只需要加锁一条记录，并且加记录锁就可以避免幻读。
• 当查询的记录是不存在的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」，因为仅靠间隙锁就可以避免幻读。

唯一索引范围查询，加锁情况分析：

• 首先会对每一个扫描到的索引加 next-key 锁，如果遇到下面这些情况，会退化成记录锁或者间隙锁：

  • 对大于的范围查询，next-key锁不会退化。

    select * from user where id > 15 for update;
    

    

  • 对大于等于的范围查询，如果“等于”的等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。

    select * from user where id >= 15 for update;
    

    

  • 对小于/小于等于的范围查询，扫描到终止范围查询的记录时，next-key锁就会退化为间隙锁。小于等于时，等值查询的记录在表中，next-key不会退化，因为next-key本就是左开右闭，避免幻读。

    select * from user where id < 6 for update;
    

    

• 非唯一索引等值查询

  • 当查询的记录存在时，扫描到的二级索引记录加的是next-key lock，扫描到的第一个不符合条件的二级索引记录，next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

    select * from user where age = 22 for update;
    

    

  • 当查询的记录不存在时，扫描到第一条不符合条件的二级索引记录， next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

    select * from user where age = 25 for update;
    

    

• 非唯一索引范围查询

  • 对扫描到的二级索引记录加锁都是加 next-key 锁，主键索引加记录锁。

• 没有加索引的查询

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

5.3 update没加索引会锁全表？

当我们执行 update 语句时，实际上是会对记录加独占锁（X 锁）的，此时其他事务对持有独占锁的记录进行修改时是会被阻塞的。另外，这个锁并不是执行完 update 语句就会释放的，而是会等事务结束时才会释放。

• 在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。
• 即使where条件使用了索引，还得看这条语句在执行过程中，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

5.4 MySQL死锁了，怎么办？

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

我们可以执行 select * from performance_schema.data_locks\G; 语句 ，确定事务加了什么类型的锁。

• 为什么会出现死锁？

  0. 建了一张订单表，其中 id 字段为主键索引，order_no 字段普通索引，也就是非唯一索引（二级索引）

     # 插入六条记录，id 1-6 、order_on 1001-1006
     

  1. 事务A要插入1007订单记录：在插入之前，给订单做幂等性校验，目的是为了保证不会出现重复的订单。

     SELECT id FROM t_order WHERE `order_no` = 1007 for UPDATE;
     # 需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录
     

     执行该语句，事务A在二级索引加了X型next-key锁，范围是(1006 ， +∞)。

  2. 事务B也做幂等性校验：

     SELECT id FROM t_order WHERE `order_no` = 1008 for UPDATE;
     

     事务B在二级索引加了X型next-key锁，范围也是(1006 , +∞)。

  3. 事务A、B执行insert语句，插入1007、1008。

     Insert into t_order (order_no, create_date) values (1007, now());
     Insert into t_order (order_no, create_date) values (1008, now());
     

     此时两个事务都陷入了等待状态，也就是发生了死锁，因为都在相互等待对方释放锁。

     • 因为当我们执行insert语句时，会在插入间隙上获取插入意向锁，而插入意向锁与间隙锁是冲突的，所以当其它事务持有间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。

       而间隙锁与间隙锁之间是兼容的，并且两个事务中 select ... for update 语句并不会相互影响。

       • 因为间隙锁的意义只在于阻止区间被插入，一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁。
       • next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务再获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。但是，对于这种范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。

• 如何避免死锁？

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

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

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

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

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

创建一张学生表：其中id为主键索引，其他都是普通字段。

事务 A 和 事务 B 都在执行 insert 语句后，都陷入了等待状态，也就是发生了死锁，因为都在相互等待对方释放锁。

• Time1阶段：此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加的是间隙锁，锁范围是(20, 30)。（唯一索引等值查询，查询id不在索引中，退化成间隙锁）
• Time2阶段：此时事务B在主键索引上加的是也是间隙锁，和事务A相同。
• Time3阶段：事务 A 的状态为等待状态（LOCK_STATUS: WAITING），因为向事务 B 生成的间隙锁（范围 (20, 30)）中插入了一条记录，所以事务 A 的插入操作生成了一个插入意向锁（LOCK_MODE:INSERT_INTENTION）。
• Time4阶段：与Time3阶段相同。

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

整理自：小林Coding