MySQL 锁

全局锁

==对整个数据库加锁==

flush tables with read lock -- 添加全局锁
unlock tables -- 释放全局锁

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

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

当连接断开后，锁会自动释放。

使用场景：全库备份。当备份数据库的时候，会用全局锁。

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

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

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

表级锁

MySQL 里面表级别的锁有这几种：

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

表锁

lock tables user read; -- 读锁 user表
lock tables user write; -- 写锁
unlock tables

表共享读锁(read lock)：大家都能读，都不能写（包括自己）。

表独占写锁(write lock)：上锁的线程既能读又能写，其他线程不能读也不能写。

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

即自己加了读锁，那么自己接下来想对表写入也会阻塞，别人想写入也会阻塞，知道锁释放。

当连接断开后，锁会自动释放。

元数据锁（MDL）

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

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

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

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

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

线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上MDL读锁；接着，线程C修改了表字段，此时由于线程 A 的事务并没有提交，也就是MDL读锁还在占用着，这时线程C就无法申请到MDL写锁，就会被阻塞，那么在线程C阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

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

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

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

意向锁

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

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

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用MVCC实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

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

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁和独占表锁发生冲突。

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

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

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

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

AUTO-INC 锁

InnoDB 有自增主键，在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC锁 实现的。

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

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

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

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

因此，在MySQL 5.1.22版本开始，InnoDB存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被AUTO_INCREMENT修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

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

• 当innodb_autoinc_lock_mode = 0，就采用 AUTO-INC锁，语句执行结束后才释放锁；当innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
• 当innodb_autoinc_lock_mode = 1：
  • 普通insert语句，自增锁在申请之后就马上释放；
  • 类似insert...select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

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

举个🌰：

session A往表t中插入了4行数据，然后创建了一个相同结构的表七2，然后两个 session同时执行向表t2中插入数据。

如果innodb_autoinc_lock_mode = 2，意味着「申请自增主键后就释放锁，不必等插入语句执行完」。那
么就可能出现这样的情况：

• session B先插入了两个记录，（1，1，1）、（2，2，2）；
• 然后，session A来申请自增id得到id=3，插入了（3，5，5）；
• 之后，session B继续执行，插入两条记录（4，3，3）、（5，4，4）。

可以看到，session B的insert语句，生成的id不连续。

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

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

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

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

行级锁

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

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

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

• for update：独占锁；
• in share mode：共享锁；

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

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

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

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

Next-Key Lock

==行锁+间隙锁==

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

InnoDB执行行级锁的方式是，当它搜索或扫描一个表索引时，它会在遇到的索引记录上设置共享锁或排他锁。行级锁实际上是索引记录锁。索引记录上的 next-key 锁也会影响该索引记录之前的“间隙”。也就是说，next-key锁是索引记录锁加上索引记录前面的间隙锁。

间隙锁是左开右开，而临键锁是左开右闭。

==临键锁 是默认的行锁。==

在默认情况下，行锁 就是 临键锁，它会锁自己以及附近的数据，但是：

• 如果是主键或者唯一索引，会退化成记录锁，也就是我们习惯说的那个“行锁”；
• 如果是普通索引，间隙空值操作也会退化为间隙锁，只有在一些条件下才会产生临键锁。

Record Lock

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

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

• 当一个事务对一条记录加了X型记录锁后，其他事务既不可以对该记录加S型记录锁（S型与X锁不兼容），也不可以对该记录加X型记录锁（X型和X型不兼容）；

Gap Lock

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

间隙锁锁得是两个索引记录之间的“间隙”，即两个值之间不存在的实际记录空间。确保了在事务执行期间，其他事务无法在这个范围内插入新的记录。

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

临键锁、间隙锁、记录锁

• 唯一索引：由于唯一性，查找所有满足条件的索引；
• 普通索引：查到满足条件的还不能停，需要找到下一个不满足的为止；
• 条件里仅等值查询（例如id = 20）且该值不存在（20 不存在），此时会增加间隙锁；
• 普通索引 + 排它锁的情况下，会回表增加符合条件的主键索引上的行锁（共享锁+覆盖索引时不会回表）；

1. 索引上的等值查询 (唯一索引，例如主键索引)

   • 给 ==不存在的记录== 加锁时，优化为间隙锁。

     示例：查询 id=3，间隙锁：(1, 5)，双开区间。

     示例：查询 id > 12，间隙锁：(10.15) (15,20) (20,25) (25,+)；行锁：15 20 25。

   • 查询的索引存在，优化为 行锁（记录锁）

     示例：查询 id=5，记录锁：5。

2. 索引上的范围查询(唯一索引或者主键索引)，会访问到不满足条件的第一个值为止。

   示例：查询 id < 15：

   • 间隙锁：(-,1)(1,5) (5,10) (10,15)
   • 记录锁：1,5,10

3. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

   普通索引不唯一，所以等值查询的时候会找到一个不满足条件的索引。

根据普通索引搜索。

普通索引存在时：

• age = 15，先找到 15，临键锁：(10, 15]，接着找，找到不满足的 age = 25，临键锁：（15， 20]，

  优化：age=15--id=15 的记录锁，(15,20) 的间隙锁。

• age>15，结果：间隙锁 (15,20) (20,25) (25,+)，记录锁：20 25。

普通索引不存在时：

• age = 18：间隙锁：(15,20)
• age > 18：间隙锁：(15,20) (20, 25) (25, +)

插入意向锁

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

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

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

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

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

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