Mysql数据库锁

• 全局锁
  • 全局锁的应用场景
  • 全局锁的缺点与改进方式
• 表级锁
  • 表锁
  • 元数据锁（MDL）
    • MDL 锁的问题
  • 意向锁
  • AUTO-INC 锁
• 行锁
  • 共享锁和排他锁
  • 间隙锁
    • 间隙锁的范围
    • 间隙锁的注意点
    • 不同线程同时加间隙锁的影响
    • 插入意向锁

全局锁

添加全局锁的语句为：

flush tables with read lock

全局锁的作用是将整个数据库锁定为只读状态，这时候其他线程执行以下操作都会被阻塞：

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

对于当前线程来说，当执行上述的语句时不会进入阻塞状态，而是会产生锁冲突报错。

mysql> select * from test_table where id = 1 for update;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

要释放全局锁，则需要执行：

unlock tables

全局锁的应用场景

全局锁主要用于做全库逻辑备份，在数据的备份期间，就不会因为数据或者表结构修改而使数据备份失败。

全局锁的缺点与改进方式

在添加全局锁之后，整个数据将会陷入只读状态，如果数据库的数据过多，则整个耗时将会花费很多的时间。为了避免这种性能低下的先加全局锁后再复制数据库的方式，如果数据库引擎支持Repeatable Read隔离级别，则可以在备份数据库之前先开启事务，会先创建Read View，然后数据库的备份就可以直接使用Read View的数据，数据库数据也可以继续执行了。常用的数据库备份工具mysqldump可添加-single-transaction参数即可在备份开启时自动开启事务进行数据备份。

注意 InnoDB 引擎才支持Repeatable Read隔离级别，而 MyISAM 不支持开启事务

表级锁

表锁

表锁是针对单个表的锁定，分为读锁和写锁

lock tables xxx read;
lock tables xxx write;

他的表现和添加了全局锁之下的对单个表的读写的表现是一致的，即不同线程的增删改 DML 和 DDL 语句都会被阻塞，而当前线程的表现则是锁冲突。解锁的语句为：

unlock tables;

元数据锁（MDL）

当我们对数据库表进行操作时，会自动给这个表加上 MDL 锁：

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

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。当事务执行完毕之后，系统会自动释放锁。

MDL 锁的问题

当系统在不同线程对同一个表添加 MDL 锁时，容易造成冲突阻塞的问题。

• 假设一个线程先添加了 DML 读锁而事务迟迟不结束，即对该表的 MDL 读锁将不会释放
• 此时另外的线程也进行了读操作，这时也会添加 MDL 读锁，因为读锁不会冲突，因此可以顺利读取到数据
• 然后又一个线程发起了写操作，这时会尝试添加 MDL 写锁，因为读写锁会冲突，因此当前线程会阻塞
• 在上面的线程被阻塞之后，后续其他线程的读操作、写操作都会被阻塞

对于申请的 MDL 锁，系统会形成一个等待队列，而该队列的写锁优先级高于读锁，所以这就造成当队列中有写锁存在时，会使其他读写操作都被阻塞。

意向锁

意向锁存在于 InnoDB 引擎中：

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

意向锁的意义在于，当需要对数据表添加表级锁时，不需要遍历所有数据表中的所有行，去判断是否存在行锁，因此提升了性能。

AUTO-INC 锁

该锁是当某个字段设置了AUTO_INCREMENT属性时，在插入数据时将会通过AUTO-INC锁保证字段增长的原子性。 该锁在语句执行完之后会立即释放。

行锁

共享锁和排他锁

对行记录加的锁分为了共享锁（S）和排他锁（X）：

• select：快照读，依赖MVCC机制，不会加锁
• select ... lock in share mode：当前读，加共享锁
• select ... for update：当前读，加排他锁
• update、insert、delete：加排他锁

对行记录的加锁操作都会使对表添加意向锁（IS、IX），而行锁是有作用范围的，这是为了防止出现幻读现象，限制行锁作用范围的锁就称为间隙锁。对于快照读，因为有MVCC机制的保证，因此也不会有幻读现象，所以 InnoDB 在RR隔离级别下就可以同时解决脏读、不可重复读、幻读三种问题。

间隙锁

根据间隙锁的类型，主要分为了三种锁形式：

• Record Lock：记录锁，仅把当前记录加锁
• Gap Lock：间隙锁，锁定一个范围，但是不包括当前记录
• Next-Key Lock：Record Lock 和 Gap Lock 的结合，同时锁定当前记录和锁定一个范围

间隙锁的范围

假设当前表中有索引列：3、5、8、9四个值，则三种间隙锁的范围分别是：

• Record Lock：3、5、8、9
• Gap Lock：(-∞, 3)、(3, 5)、(5, 8)、(8, 9)、(9, +∞)
• Next-Key Lock：(-∞, 3]、(3, 5]、(5, 8]、(8, 9]、(9, +∞)

如果当前查询的的值不在上面四个值中，如查询索引值为4，则使用间隙锁的范围将会是包含该值的区间。 对于间隙锁范围的确认，在加锁时首先都会先尝试添加Next-Key Lock，然后再根据具体的情况判断是否需要退化，具体规则如下：

• 唯一索引等值查询：
  • 如果查询的记录存在，Next-Key Lock 退化为Record Lock，即只会锁住当前记录。因为索引具有唯一性，所以只要在当前事务锁住当前行即可其他事务无法对其进行修改，可确保不会有幻读问题。
  • 如果查询的记录不存在，Next-Key Lock 退化为Gap Lock，锁住包含该记录值的范围区间。比如查询值为4，那么锁住的范围就是(3, 5)。
• 非唯一索引等值查询：

  • 如果查询的记录存在，除了加Next-Key Lock之外，还额外添加Gap Lock，而 Gap Lock 的区间为当前记录值的下一个范围区间，所以会添加两把锁。和唯一索引的差别在于当值存在时 Next-Key Lock 不会退化为记录锁。以上面所示的四个索引列为例，查询值为5时，将会锁住 (3, 5] 和 (5, 8)，不然可能会有其他的事务在 (3, 5] 和 (5,8) 这两个区间内插入了新的相同的索引值，造成幻读问题。

  • 如果查询的记录不存在，Next-Key Lock 会退化为Gap Lock，锁住包含该记录值的范围区间。比如查询值为4，那么锁住的范围就是(3, 5)，和唯一索引等值查询的处理一致

• 唯一索引范围查询：
  • 范围查询会逐个范围内的索引进行判断，判断的依据和唯一索引等值查询一致。以上面所示的四个索引列为例，查询id >= 4 and id < 9：
    • 首先对 4 进行判断，在索引中没有 4，因此使用的是Gap Lock：(3, 5)
    • 索引中有 5、8，因此使用Record Lock：5、8
    • 最后判断 9，因为不满足判断条件，所以使用的是Gap Lock：(8, 9)
• 非唯一索引范围查询：
  • 范围查询会逐个范围内的索引进行判断，但是判断依据不和非唯一索引等值查询一致，在这里 Next-Key Lock 不会退化为 Gap Lock。以上面所示的四个索引列为例，查询id >= 4 and id < 9：
    • 首先对 4 进行判断，在索引中没有 4，因此使用的是Gap Lock：(3, 5)
    • 索引中有 5、8，因此使用Next-Key Lock：(3, 5]、(5, 8]
    • 最后判断 9，虽然这里不满足条件，但是使用的依然为Next-Key Lock：(8, 9]

间隙锁的注意点

因为 InnoDB 会自动添加间隙锁，因此在使用时要注意为查询添加范围，否则可能就会造成对全表或者大范围加上了间隙锁，导致数据库并发能力下降。比方说，当执行update语句时，一定要加上where语句，否则就会给全表的数据都加上X锁和间隙锁。

不同线程同时加间隙锁的影响

不同线程是可以同时对同个表加相同范围的间隙锁的，因为间隙锁的意义在于阻止区间被插入/删除（这会造成幻读问题），因此可以共存。

插入意向锁

虽然名字叫插入意向锁，但是实际上数据一种特殊的间隙锁，这个锁只用于并发插入操作。

插入意向锁和间隙锁的主要区别在于：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。插入意向锁的生成时机是每当使用insert插入一条语句时，都会判断当前位置在其他事务是否已经被加上了间隙锁，如果已经加了间隙锁，则insert语句被阻塞，并生成一个插入意向锁。所以，以下应用场景会变成死锁：

CREATE TABLE `t_student` (
  `id` int NOT NULL,
  `no` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

分析：

1. 阶段1，加入了间隙锁Gap Lock：(20, 30)
2. 阶段2，加入了间隙锁Gap Lock：(20, 30)
3. 阶段3，由于已经事务B已经加了间隙锁，因此会生成插入意向锁，进入阻塞状态，等待事务B执行完毕
4. 阶段4，由于已经事务A已经加了间隙锁，因此会生成插入意向锁，进入阻塞状态，等待事务A执行完毕
5. 进入死锁