youyichannel

志于道，据于德，依于仁，游于艺！

0%

MySQL 锁的那些事

发表于 更新于 分类于
本文字数： 5.9k 阅读时长 ≈ 5 分钟

全局锁

使用

在数据库中执行：

FLUSH TABLES WITH READ LOCK;

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

  • 对数据的增删改操作，比如 INSERT、DELETE、UPDATE等语句；
  • 对表结构的更改操作，比如 ALTER TABLE、DROP TABLE 等语句。

释放全局锁：

UNLOCK TABLES;

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

应用场景

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

缺点

加上全局锁，意味着整个数据库都是只读状态。那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份时，业务只能够读取数据，而不能够更新数据，这样会造成业务停滞。

有什么其他方式来避免？

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

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

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 -single-transaction 参数时，就会在备份数据库之前开启事务。

📢注意：这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

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

表级锁

表锁

如果我们想要对某张表加表锁，可以使用如下命令：

LOCK TABLES `table_name` READ; # 表级别的共享锁，读锁

LOCK TABLES `table_name` WRITE; # 表级别的独占锁，写锁

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

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

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

UNLOCK TABLES;

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

元数据锁

首先我们是不需要显式地使用元数据锁（MDL）的，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

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

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

当有线程在执行 SELECT 语句时，此时加的是 MDL 读锁，如果有其他线程要更改该表的结构，这个时候是要申请 MDL 写锁的，那么该线程会被阻塞，直到执行完 SELECT 语句，释放 MDL 读锁之后。

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

MDL 不需要显式调用，什么时候释放呢？

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

那如果数据库有一个长事务，在对表结构做变更操作时，会发生一些极端的情况：

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

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

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

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

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

在 MySQL 中查看正在执行的事务，可以使用 InnoDB 状态报告命令「SHOW ENGINE INNODB STATUS」。该命令会返回 InnoDB 引擎的详细信息，其中包含正在执行的事务的信息。

正在执行的事务的信息位于「TRANSACTIONS」部分。具体的，「ACTIVE TRANSACTIONS」一行下面的内容即为正在执行的事务的信息。

意向锁

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

即当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。普通的 SELECT 语句是不会加行级锁的，普通的 SELECT 语句是利用 MVCC 实现一致性读的，是无锁的。

SELECT 也是可以对记录加共享锁和独占锁的：

SELECT ... LOCK IN SHARE MODE; # 共享锁

SELECT ... FOR UPDATE; # 独占锁

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁「LOCK TABLES ... READ」和独占表锁「LOCK TABLES ... WRITE」发生冲突。

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

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

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

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

AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 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 一起使用时，在「主从复制的场景」中会发生数据不一致的问题。

【🌰栗子】

mysql-auto-inc-binlog.drawio

session A 往表 t 中插入了 4 行数据，然后 session B 创建了一个相同结构的表 t2，然后两个 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 面对 t2 表的更新只会记录这两个 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，既能提升并发性，又不会出现数据一致性问题。

行级锁

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

Record Lock

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

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

【🌰栗子】当一个事务执行了下面这条语句：

BEGIN;

SELECT * FROM tb_test WHERE id = 1 FOR UPDATE;

就是对 tb_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。

当事务执行 commit 后，事务过程中生成的锁都会被释放。

Gap Lock

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

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

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

Next-Key Lock

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

【🌰栗子】表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

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

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

【🌰栗子】一个事务持有了范围为 (1, 10] 的 X 型的 Next-Key Lock，那么另外一个事务在获取相同范围的 X 型的 Next-Key Lock 时，就会被阻塞。

📢注意：虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（Next-Key Lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

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

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态，此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

MySQL 加锁时，先生成锁结构，然后设置锁的状态，如果锁的状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁的状态为正常时，才代表事务成功获取到了锁。

插入意向锁并不是意向锁，而是一种特殊的间隙锁，属于行级别锁。

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

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

相关文章