•Database

Mysql锁

Mysql InnoDB的锁可以从两个维度来分，分别是粒度和模式

MySQL

Mysql InnoDB的锁可以从两个维度来分，分别是粒度和模式

按粒度分：可以分为行锁和表锁。

按模式分：可以分为共享锁和独占锁。

行锁又细分为三种：

  • 记录锁锁住具体那一行
  • 间隙锁锁住两条记录之间的空隙防止插入
  • 临键锁是记录锁加间隙锁的组合。

还有几个辅助性质的锁：

  • 意向锁用来标记表里有没有行锁，加表锁的时候不用遍历；
  • 元数据锁 MDL 保护表结构，DDL 和 DML 不能同时跑；
  • 插入意向锁表示有事务在等着往某个间隙插数据；
  • 自增锁保证 AUTO_INCREMENT 分配不重复。

共享锁和排他锁

在 MySQL 中锁可以分为两大类，分别是 shared (S) locks 和 exclusive (X) locks。

  • S锁，称为共享锁，事务在读取记录的时候获取 S 锁，它允许多个事务同时获取 S 锁，互相之间不会冲突。
  • X锁，称为独占锁（排他锁），事务在修改记录的时候获取 X 锁，且只允许一个事务获取 X 锁，其它事务需要阻塞等待。

S 锁之间不冲突，X 锁则为独占锁，所以 X 之间会冲突， X 和 S 也会冲突。

冲突SX
S不冲突冲突
X冲突冲突

SELECT ... LOCK IN SHARE MODE; 这种读取需要对记录上 S 锁。

SELECT ... FOR UPDATE; 需要对记录上 X 锁。

MyISAM 引擎仅支持表锁，而 Innodb 即支持表锁也支持行锁。

  • LOCK TABLES yes READ 是对 yes 这个表上 S 锁。
  • LOCK TABLES yes WRITE 是对 yes 这个表上 X 锁。

不过一般情况下，我们不会使用表锁，因为平日的 UPDATE 、SELECT 要用也是用行锁了，不可能用粒度粗的表锁，不然性能太低。

唯一能想到用上表锁的就是 DDL 语句了，比如 ALTER TABLE 的时候，应该锁定整个表，防止查询和修改。除此之外 MySQL 还提供了一个叫 MDL 的锁，即 Metadata Locks（元数据锁）。

元数据锁

元数据锁也分为读锁和写锁：

1）读锁 (MDL_SHARED)：

当一个事务需要读取表的元数据时（如执行 SELECT 操作），会获取读锁。 多个事务可以同时持有读锁，不会互相阻塞。

2）写锁 (MDL_EXCLUSIVE)：

当一个事务需要修改表的元数据时（如执行 ALTER TABLE 操作），会获取写锁。 写锁会阻塞其他任何读锁和写锁，确保独占访问。

元数据锁的主要作用如下：

1）防止并发的 DDL 操作和 DML 操作冲突：

  • 当一个事务对表进行结构性更改（如 ALTER TABLE），元数据锁（写锁）会阻止其他事务对该表进行操作，直到结构更改完成。
  • 当一个事务对表进行数据操作（如 SELECT、INSERT、UPDATE、DELETE），元数据锁（读锁）会阻止其他事务对该表进行结构性更改。

2）保护元数据一致性：

  • 确保在执行 DDL 操作（如 CREATE TABLE、DROP TABLE、ALTER TABLE）时，元数据不会被其他事务同时修改。

总得来说，只要执行DDL语句或者是DML语句，就需要获取MDL。

至于获取的是MDL的共享锁还是独占锁，需要分情况：

  • 对于DML语句，如果是读操作，则获取共享MDL，如果是写操作则获取独占MDL（加锁的读比如select .. for update也是独占）
  • 对于DDL语句也是差不多，对于show create table或者是describe等就是共享，对于alter table，drop table就是独占

共享MDL和独占MDL是互斥的，。

需要额外注意的是MDL是表级的，他保护的一直是表的元数据，也就是表的结构。也就是说它针对的是DDL语句。为了确保DML在执行增删改查时不会因为并发的DDL语句修改了表结构导致出错，进而在执行DML语句时会获取MDL。如果在执行DML期间，只要执行获取互斥MDL的DDL语句就会被阻塞。

此外MDL的互斥，只针对DDL和DDL之间，以及DDL和DML之间。DML和DML之间怎样都不互斥

请求方当前持有 MDL阻塞情况说明
DML（共享 S）DML（共享 S）❌ 不阻塞普通 SELECT 可以并发执行
DML（共享 S）DML（独占 X）❌ 不阻塞DML 写操作不会被读阻塞
DML（独占 X）DML（共享 S）❌ 不阻塞DML 写不会阻塞其他 DML 读
DML（独占 X）DML（独占 X）❌ 不阻塞不同事务更新不同数据行可以并行
DML（S/X）DDL（独占 X）✅ 阻塞DDL 需要独占 MDL，等待 DML 完成
DDL（X）DML（S/X）✅ 阻塞DML 在执行，DDL 必须等待 MDL 释放
DDL（X）DDL（X）✅ 阻塞排他 DDL 互斥
DDL（S）DDL（X）✅ 阻塞查询表结构（共享 MDL）阻塞修改表结构（排他 MDL）
DDL（S）DDL（S）❌ 不阻塞查询表结构可以并行

还有一个比较关键的问题：mysql是如何知道当前的MDL是否发生冲突呢？

首先的话，每张表都会有一个Metadata Lock Manager 也就是 MDL锁管理器，他内部维护了两个链表：

  • 已持有锁的链表 granted list
  • 等待锁的链表 waiting list

当一个线程请求 MDL 锁时：

  1. 去 MDL_lock 查当前 granted list
  2. 判断是否冲突
  3. 不冲突 → 直接加入 granted
  4. 冲突 → 加入 waiting 队列

更完整一点其实就是，当 ALTER 执行时：

  1. 找到表对应的 MDL_lock
  2. 查看 granted list
  3. 发现有 MDL_SHARED_READ 存在
  4. 因为 SHARED 和 EXCLUSIVE 冲突
  5. → 进入 waiting list

内部其实是维护一个FIFO的waiting list，确保公平，避免DDL“饿死”

那为什么DML和DML之间不会因为MDL互斥呢？

核心原因在于DML语句获取的本质上都是共享锁，这个共享锁分为MDL_SHARED_READ和MDL_SHARED_WRITE，只有DDL的部分写语句和手动执行的LOCK TABLE user READ或者是FLUSH TABLE user WITH READ LOCK才会获取MDL_EXCLUSIVE。

而最终是否互斥，也是看的MDL_EXCLUSIVE和MDL_SHARED_XXX

意向锁

假设业务上真用到了表锁，那么表锁和行锁之间肯定会冲突，当 InnoDB 加表锁的时候，如何判断表里面是否已经有行锁？难道得一条记录一条记录遍历过去找吗？显然这样的效率太低了！

因此引入了意向锁的机制。

  • IS（Intention Shared Lock），共享意向锁
  • IX（Intention Exclusive Lock），独占意向锁。

这两个锁是表级别的锁，当需要对表中的某条记录上 S 锁的时候，先在表上加个 IS 锁，表明此时表内有 S 锁。当需要对表中的某条记录上 X 锁的时候，先在表上加个 IX 锁，表明此时表内有 X 锁。

这样操作之后，如果要加表锁，就不需要遍历所有记录去找了，直接看看表上面有没有 IS 和 IX 锁。

比如，此时要上表级别的 S 锁，如果表上没有 IX ，说明表中没有记录有独占锁，其实就可以直接上表级 S 锁。

如果此时要上表级别的 X 锁，如果表上没有 IX 和 IS ，说明表中的所有记录都没加锁，其实就可以直接上表级 X 锁。

因此 IS 和 IX 的作用就是在上表级锁的时候，可以快速判断是否可以上锁，而不需要遍历表中的所有记录。

所以 IS 和 IX 互相之间是不会冲突的，因为它们的作用只是打个标记，来丰富一下上面的表格：

冲突SXISIX
S不冲突冲突不冲突冲突
X冲突冲突冲突冲突
IS不冲突冲突不冲突不冲突
IX冲突冲突不冲突不冲突

无论是IS还是IX都是在执行DML语句加的，IS和IX都只是表示当前表中有S锁或者是X锁。因此IS和IX之间并不冲突。

  • 如果是IS则只能插入表级共享锁，比如LOCK TABLE table READ;
  • 如果是IX则共享和独占的表级锁都不能加

正常来说InnoDB的表锁，只能显示添加：

  • LOCK TABLE user READ; -- 表S
  • LOCK TABLE user WRITE; -- 表X

对于DDL语句插入的则是MDL。

记录锁

记录锁顾名思义就是锁住当前的记录，它是作用到索引上的。我们都知道 innodb 是肯定有索引的，即使没有主键也会创建隐藏的聚簇索引，所以记录锁总是锁定索引记录。

比如，此时一个事务 A 执行 SELECT * FROM yes WHERE name = 'xx' FOR UPDATE; 那么 name = xx 这条记录就被锁定了，其他事务无法插入、删除、修改 name = xx 的记录。

此时事务 A 还未提交，另一个事务 B 要执行 insert into yes (name) values ('xx')，此时会被阻塞，这个很好理解。

但是，如果另一个事务 C 执行了 insert into yes (name) values ('aa')，这个语句会被阻塞吗？

看情况。

在可重复读隔离级别下，如果 name 没有索引。前面提到记录锁是加到索引上的，但是 name 没索引啊，那只能去找聚簇索引，但聚簇索引上面能通过 name 快速找到数据吗？它只能通过全表扫描一个一个看过去，所以咋办？都锁了呗！

因此，如果 name 没有索引，那么事务 C 会被阻塞，如果有索引，则不会被阻塞！

所以这里要注意，没索引的列不要轻易的锁，不要以为有行锁就可以为所欲为，并不是这样的。

间隙锁和临键锁

前面说了，记录锁需要加到记录上，但是如果要给此时还未存在的记录加锁怎么办？也就是要预防幻读的出现！

这时候间隙锁就派上用场了，它是给间隙加上锁。

比如此时有 1、3、5、10 这四条记录，数据页中还有两条虚拟的记录，分别是 Infimum 和 Supremum。

记录之前都有间隙，那间隙锁呢，锁的就是这个间隙！

比如我把 3 和 5 之间的间隙锁了，此时要插入 id = 4 的记录，就会被这个间隙锁给阻塞了，这样就避免了幻读的产生！也就实现了锁定未插入的记录的需求！

还有个 Next-Key Locks 就是记录锁+间隙锁，像上面间隙锁的举例，只能锁定(3,5) 这个区间，而 Next-Key Locks 是一个前开后闭的区间(3,5]，这样能防止查询 id=5 的这个幻读。

间隙锁之间不会冲突，间隙锁的唯一目的就是防止其他事务插入数据到间隙中 ，所以即使两个间隙锁要锁住相同的间隙也没有关系，因为它们的目的是一致的。

间隙锁可以显式禁用，它是在事务隔离级别为可重复读的时候生效的，如果将事务隔离级别更改为 READ COMMITTED，就会禁用了，此时，间隙锁对于搜索和索引扫描是禁用的，仅用于外键约束检查和重复键检查。

插入意向锁

插入意向锁，即 Insert Intention Locks，它也是一类和间隙相关锁，但是它不是锁定间隙，而是等待某个间隙。比如上面举例的 id = 4 的那个事务 C ，由于被间隙锁给阻塞了，所以事务 C 会生成一个插入意向锁，表明等待这个间隙锁的释放。

并且插入意向锁之间不会阻塞，因为它们的目的也是只等待这个间隙被释放，所以插入意向锁之间没有冲突。

它的目的不在于锁定资源防止别人访问，我个人觉得更像是为了遵循 MySQL 的锁代码实现而为之。

锁其实就是内存里面的一个结构，每个事务为某个记录或者间隙上锁就是创建一个锁对象来争抢资源。

如果某个事务没有抢到资源，那也会生成一个锁对象，只是状态是等待的，而当拥有资源的事务释放锁之后，就会寻找正在等待当前资源的锁结构，然后选一个让它获得资源并唤醒对应的事务使之得以执行。

所以按照这么个逻辑，那些在等待间隙锁的插入事务，也需要对应的建立一个锁结构，然后锁类型是插入意向锁。

这样一来，间隙锁的事务在释放间隙锁的时候，才能得以找到那些等待插入的事务，然后进行唤醒，而由锁的类型也可以得知是插入意向锁，之间不需要阻塞，所以可以一起执行插入。

在执行插入时比如INSERT，REPLACE，INSERT … SELECT，UPDATE 修改索引列（内部是 delete + insert）等等都会申请“插入意向锁”，插入意向锁之间不冲突，因此可以并发执行，但如果遇到了gap lock或者是next-key lock这些吧gap锁住的lock，插入意向锁就会被阻塞。

Mysql是如何知道插入意向锁和gap lock以及next-key lock发生冲突的？

这里顺便讲一下InnoDB的一个关键结构lock sys全局行锁管理器

他负责管理所有 record lock，gap lock，next-key lock，insert intention lock的管理，并且还会做死锁分析，以及等待队列管理等等（表锁在MDL那里）

大致的结构长这样

lock_sys_t
│
├── rec_hash           // 行锁 hash 表（核心）
├── prdt_hash          // predicate 锁 hash（间隙扫描）
├── wait_mutex         // 等待队列保护
├── timeout_thread     // 超时线程
└── deadlock detector  // 死锁检测

这个rec_hash相当重要，他存储了当前所有的行锁。这个hash的key是由（index_id + page no）组成及索引和页号，也就是说同一个索引页的锁会被放入同一个bucket

这里的index_id不是索引名，而是InnoDB引擎内部为每个索引分配的唯一ID

比如说在这表里

CREATE TABLE t (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    KEY idx_age(age)
);

主键索引id和二级索引age都会有一个index id。

每个索引独立管理锁。其核心原因在于lock sys是管理行锁的，行锁是加载索引记录上的，而且二级索引和主键索引的记录是不同的 B+Tree

而page则是提高并发，将检查的范围从同一个索引，变成了同一页的同一个索引

每个bucket又是有一个lock t节点组成的双向链表构成，节点内部记录了当前是什么锁，属于哪个事务，哪个索引，哪一页。需要注意的是它不仅挂载获取了锁的节点，他还挂在未获取到锁，等待锁的节点。

前半部分是granted list后半部分是waiting list

因此回到最开始的话题，当插入insert intention lock时，就会通过index id + page no查看当前索引记录是否有对应的next key lock或者是gap lock存在。如果有就说明有冲突，就会加入waiting list 等待被唤醒。

Auto-Inc Lock

Auto-Inc Lock 是一个特殊的表级锁，用于自增列插入数据时使用。 在插入一条数据的时候，需要在表上加个 Auto-Inc Lock，然后为自增列分配递增的值，在语句插入结束之后，再释放 Auto-Inc Lock。

在 MySQL 5.1.22 版本之后，又弄了个互斥量来进行自增减的累加。互斥量的性能高于 Auto-Inc Lock，因为 Auto-Inc Lock是语句插入完毕之后才释放锁，而互斥量是在语句插入的时候，获得递增值之后，就可以释放锁，所以性能更好。

但是我们还需要考虑主从的情况，由于并发插入的情况，基于 statement -based binlog 复制时，自增的值顺序无法把控，可能会导致主从数据不一致。

所以 MySQL 有个 innodb_autoinc_lock_mode 配置，一共有三个值：

  • 0，只用 Auto-Inc Lock，在插入执行完后才释放。
  • 1，默认值，对于插入前已知插入行数的插入，用互斥量，只在分配ID瞬间加锁，很快释放。对于插入前不知道具体插入数的插入，用 Auto-Inc Lock，这样即使基于 statement -based binlog 复制也是安全的。
  • 2，只用互斥量。

Q&A

提问：什么情况下会触发表锁而不是行锁？

回答：三种情况。一是查询没走索引，InnoDB 只能全表扫描，锁就加到所有扫描到的记录上，效果等于表锁。二是显式用 LOCK TABLES 语句加表锁。三是 DDL 语句比如 ALTER TABLE，会加 MDL 写锁。

提问：两个事务都用间隙锁锁住同一个间隙，会死锁吗？

回答：单纯持有间隙锁不会，因为间隙锁之间不冲突。但如果两个事务各自持有间隙锁，然后都想往对方锁住的间隙里插数据，就会死锁。比如事务 A 持有 (3,5) 的间隙锁想插 (5,10)，事务 B 持有 (5,10) 的间隙锁想插 (3,5)，互相等着对方释放，死锁触发。

提问：为什么说间隙锁是可重复读级别特有的？

回答：可重复读需要防止幻读，间隙锁就是干这个的。读已提交级别不需要防幻读，所以间隙锁关闭只保留记录锁，锁范围小死锁概率也低。这也是很多互联网公司选择读已提交级别的原因之一。

提问：线上加字段导致查询全部超时，原因是什么？

回答：ALTER TABLE 需要拿 MDL 写锁，如果有长事务持有 MDL 读锁没释放，ALTER TABLE 就一直等。更惨的是后面的查询也要拿 MDL 读锁，但写锁请求优先级更高，所以后面的查询全都堵在 ALTER TABLE 后面。解决办法是先干掉长事务，或者用 pt-online-schema-change 这类工具做无锁 DDL。

post.comments