MySQL锁总结

有哪些锁？分别是什么时候的？什么时候会释放的？意义分别是什么？

什么时候会发生死锁？什么时候会进行死锁检测？

 

一、有哪些锁？

Server层：

1. FLUSH TABLE WITH READ LOCK  /  UNLOCK TABLES
2. LOCK TABLES ... READ/WRITE  /  UNLOCK TABLES
3. Metadata Lock（MDL锁）

 

InnoDB引擎层：

1. Shared and Exclusive Locks：共享锁 和 独占锁
2. Intention Locks：意向锁
3. Record Locks：记录锁
4. Gap Locks：间隙锁
5. Next-Key Locks：临键锁
6. Insert Intention Locks：插入意向锁
7. AUTO-INC Locks：自增锁

 

锁的符号表示：共享锁（S）独占锁（X）。所有的数据库操作，数据库默认都会加上 MDL 锁的，包括获取锁操作，只是不同的操作对应着不同的 MDL 锁类型。

 

二、Server层的锁

1、FLUSH TABLE WITH READ LOCK

关闭所有打开的表，然后使用全局读锁锁定所有数据库中的所有表。此时，所有的数据更新语句（包括增删改）、数据定义语句（建表、修改表结构等）操作会被堵塞。

 

FTWRL 使用了全局读锁，而不是表锁，它与 LOCK TABLES 和 UNLOCK TABLES 表现出来的行为不一样：

1. UNLOCK TABLES 命令配合 LOCK TABLES 使用时会隐式的提交事务，而与 FTWRL 配合使用时不会发生提交，因为没有获取到表锁。
2. 开始一个事务时，使用 LOCK TABLES 命令会自动的释放已经获得的表锁，就像你主动执行了 UNLOCK TABLES 命令一样，但是使用 FTWRL 并不会自动释放现有的全局读锁。

 

执行 FLUSH TABLE WITH READ LOCK 时 MDL 锁的类型是 SHARE。

 

1）、如何使用

# 1. session A

mysql> flush table with read lock; # 获取全局锁，使整个数据库实例处于只读状态

 

# 2. session B

mysql> insert into t_2(id, name) value(15, 'Wu’); # 视图插入一条数据，但是被堵塞住了

 

# 3. session A

mysql> unlock tables; # 释放全局锁

 

# 4. session B

# 从堵塞状态变为执行成功了。

注意：在锁定期间，如果客户端断开了连接，则锁会自动释放。

 

2）、使用场景

做全库的逻辑备份。

 

3)、问题

Q1：为什么不适用 MVCC 提供的一致性读操作读取数据呢？

A1：因为这个 MVCC 是 InnoDB 引擎实现的，而 FTWRL 是在 Server 层实现的，如果使用了 MyISAM 引擎就只能使用 FTWRL 命令了。

 

Q2：使用 set global readonly=true 的方式怎么样？

A2：虽然这种方式也可以使整个数据库实例处于只读状态，但是这个变量一般用来判断一个库是主库还是从库，还有就是使用 FTWRL时客户端发生异常断开，MySQL 会自动释放这个锁，如果使用 readonly，则整个库会一直处于不可写的状态。

 

 

2、LOCK TABLES

MySQL 允许客户端显示的获取表锁，会话只能为自己获取锁或释放自己的锁，不能获取另一个会话的锁以及释放另一个会话的锁。LOCK TABLES 在获取锁之前会隐式的释放当前会话中的所有表锁，UNLOCK TABLES 也是一样的。

 

表锁除了限制别的会话的读写外，也限制了本会话接下来的操作。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

 

注意：不能使用 COMMIT 释放 LOCK TABLES 获取的锁，只能使用 UNLOCK TABLES。

 

执行 LOCK TABLES table_name READ 时 MDL 锁的类型是 SHARED_READ_ONLY。

执行 LOCK TABLES table_name WRITE 时 MDL 锁的类型是 SHARED_NO_READ_WRITE。

 

1）、如何使用

LOCK TABLES 

    tbl_name [[AS] alias] lock_type 

    [, tbl_name [[AS] alias] lock_type] …

 

lock_type: { 

    READ [LOCAL] 

    | [LOW_PRIORITY] WRITE

}

 

UNLOCK TABLES

 

2）、使用场景

一般情况下不会使用，因为表锁的粒度太大了。在 MyISAM 引擎中会使用，但是在 InnoDB 中基本上用不到，因为这是在 Server 层实现的锁。

 

 

3、Metadata Locks（MDL 锁）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

 

因此，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

 

注意：事务中的 MDL 锁，在语句开始执行的时候申请，但是语句结束后并不会马上释放，而是要等到整个事务提交后再释放。

 

MDL 锁类型有：

1. INTENTION_EXCLUSIVE
2. SHARED
3. SHARED_HIGH_PRIO
4. SHARED_READ
5. SHARED_WRITE
6. SHARED_UPGRADABLE
7. SHARED_NO_WRITE
8. SHARED_NO_READ_WRITE
9. EXCLUSIVE

 

Q1：MDL 锁与 LOCK TABLES 的区别？

1. LOCK TABLES 会使用 MDL 锁，同样 FTWRL 也会使用 MDL 锁，
2. MDL 锁的作用在于保证 DML 语句和 DDL 语句不冲突，而 LOCK TABLES 只能保证读写不冲突。

 

三、InnoDB的锁

1、共享锁（Shared Locks）和独占锁（Exclusive Locks）

这两个锁是InnoDB引擎实现的行级锁，共享锁允许事务读取数据，独占锁允许事务更新或删除数据，读读是兼容的，读写与写写是互斥的。

 

可以发现，读与写时互斥的，为了解决这个问题，引入了 MVCC，同时带来了一些问题（有哪些问题呢？）。

 

1）、都在什么时候加上的以及什么时候释放的？

• 共享锁
• • 获取：在事务中执行 SELECT 时、SELECT … LOCK IN SHARE MODE。
  • 释放：事务提交完成后
• 独占锁
• • 获取：在事务中执行 UPDATE/DELETE、SELECT … FOR UPDATE。
  • 释放：事务提交完成后

2）、意义

保证了数据读写的正确性。

 

 

2、意向锁（Intention Locks）

InnoDB 支持多重粒度的锁定，支持行锁和表锁并存。例如 LOCK TABLES table_name WRITE 语句会表加上独占锁，为了使多个粒度的锁可行，InnoDB 引入了意向锁，它是表级锁，表示在事务中稍后对表中的行采用哪种锁（共享锁 或 独占锁）。

 

取得行的 S 锁和 X 锁之前需要先获得表的 IS 锁和 IX 锁，IS 锁和 IX 锁都是系统自动添加和释放的，主要用于辅助表级和行级锁的冲突判断。

 

意向锁分为两种：

1. 共享意向锁（IS）：事务打算对表中的个别行设置共享锁。
2. 独占意向锁（IX）：事务打算对表中的个别行设置独占锁。

例如：SELECT … LOCK IN SHARE MODE 会设置 IS 锁，SELECT … FOR UPDATE 会设置 IX 锁。

 

意向锁的协议：

1. 事务在获取到行级的共享锁（S）之前，必须先获取到意向共享锁（IS）。
2. 事务在获取到行级的排他锁（X）之前，必须先获取到意向排它锁（IX）。

 

表级别的锁的兼容性如下：

	X（独占锁）	IX（意向独占锁）	S（共享锁）	IS（意向共享锁）
X（独占锁）	冲突	冲突	冲突	冲突
IX（意向独占锁）	冲突	兼容	冲突	兼容
S（共享锁）	冲突	冲突	兼容	兼容
IS（意向共享锁）	冲突	兼容	兼容	兼容

注意：这里是表级的，不是行级的。IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突，只要是写操作不是同一行，就不会冲突。

 

意向锁除了锁定全表请求（比如 LOCK TABLES table_name WRITE）外，不阻止任何其它内容，意图锁定的主要目的是表名有人正在锁定表中的行，或将要锁定表中的行。

 

1）为什么引入意向锁？

场景：会话A想给表加 X 锁，但是并不知道表中是否有行的 X 锁，所以我得需要遍历整个表进行判断，效率比较低。

解决方法：InnoDB 引擎加入了意向锁，只需要判断要加的锁是否与已有的意向锁类型兼容即可。

结论：1、提高了加锁效率；2、实现了行锁和表锁的多粒度锁机制，使得表锁和行锁可以共存。

 

 

3、记录锁（Record Locks）

记录锁总是锁定的是索引。如果一条语句操作的是主键索引，MySQL就会锁定这条主键索引，如果一条语句操作的是非主键索引，MySQL会先锁定该非主键索引，然后再锁定主键索引。

 

语句：SELECT * FROM table_name WHERE id = 5 FOR UPDATE; // 加 X 锁，与 S 锁和 X 锁冲突。

语句：SELECT * FROM table_name WHERE id = 5 LOCK IN SHARE MODE; // 加 S 锁，与 X 锁冲突。

关于 -infinity 和 +infinity 可以理解为 -infinity 比任何记录都要小，+infinity 比任何记录都要大。

 

1）、什么时候加行锁，什么时候释放？

• 共享锁
• • SELECT
  • SELECT LOCK IN SHARE MODE
• 独占锁
• • UPDATE
  • DELETE
  • SELECT FOR UPDATE

都是在事务提交的时候进行释放。

 

2）、行锁的意义？

保持数据的一致性，避免多个会话同时修改一条数据，造成更新丢失等情况。

 

 

4、间隙锁（Gap Locks）

间隙锁锁住的是索引之间的间隙，或者锁住的是第一个元素之前的间隙，或者锁住的是最后一个元素之后的间隙。比如 SELECT id FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE; 会阻止其它事务向 10 到 20 这个间隙中插入数据（比如 15 就不能插入）。

 

间隙锁可能跨一个索引、多个索引、或者是空的。间隙区间是左闭右开。

 

InnoDB 中的间隙锁纯粹是抑制性的，它们存在的唯一目的就是防止其它事务向间隙中插入记录，间隙锁可以共存的，一个事物的间隙锁定不会阻止其它事务对相同间隙的锁定，共享间隙锁和排它间隙锁之间没有区别，彼此不冲突，功能也是相同的。

 

间隙锁可以禁用，将事务隔离级别改为 RC 或启用 innodb_locks_unsafe_binlog（现已弃用）时，这时间隙锁会被禁用做搜索和索引扫描，仅仅用于外键约束检查和重复键检查。

执行一下语句是分别会锁住：

1. SELECT * FROM table_name WHERE id = 4 FOR UPDATE; 锁住间隙 Gap2，间隙是 [3, 5)
2. SELECT * FROM table_name WHERE id = 5 FOR UPDATE; 锁住间隙 Gap2，间隙是 [3, 7)
3. SELECT * FROM table_name WHERE id < 2 FOR UPDATE; 锁住间隙 Gap1，间隙是 (-infinity, 3)
4. SELECT * FROM table_name WHERE id > 10 FOR UPDATE; 锁住间隙 Gap4，间隙是 [20, +infinity)

注意：1、如果查询条件不是索引，则会锁住整个表的间隙（也就是整个表），2、也可以使用 SELECT LOCK IN SHARE MODE。

 

1）、如何加锁，什么时候释放？

使用 SELECT LOCK IN SHARE MODE 或 SELECT FOR UPDATE 进行加锁。当事务提交后会释放。

注意：使用 LOCK IN SHARE MODE 的时候，如果使用普通索引进行查找，并且覆盖索引生效，则只会锁定普通索引，并不会锁定主键索引。

 

2）、为什么需要？

防止在执行事务期间，其它事务向间隙中插入数据，造成不一致的问题。

 

3）、总结

个人觉得间隙锁一般也用不着，在 RR 隔离级别下，使用快照读而不使用当前读的时候，幻读的问题通过 MVCC 机制就可以解决了。如果你使用了当前读，那么就得需要使用 FOR UPDATE 或 LOCK IN SHARE MODE 语句加上间隙锁，防止出现幻读的情况。

 

产生间隙锁的条件（RR事务隔离级别下）：

1. 使用普通索引锁定；
2. 使用多列唯一索引；
3. 使用唯一索引锁定多行记录。

 

5、临键锁

临键锁（next-key locks）可以看做是记录锁（record locks）和间隙锁（gap locks）的组合，是一个左开右闭的区间。

 

临键锁不仅仅是在索引记录上加锁，而且还会锁定这个索引记录前面的间隙，如果一个会话拥有一个索引记录的 S 锁或 X 锁，另一个会话则不能在这个间隙中插入一个新的索引记录。

 

假设一个索引包含 10、11、13、20，则临键锁如下：

(negative infinity, 10] 

(10, 11] 

(11, 13] 

(13, 20] 

(20, positive infinity)

可以发现，对于最后一组，实际上仅仅只有间隙锁，没有记录锁的存在了。

 

 

6、插入意向锁（insert intention locks）

它是一种间隙锁形式的意向锁，在真正执行 insert 操作之前设置。当执行插入操作时，总会检查当前插入操作的下一条记录（已存在的主索引节点）上是否存在锁对象，判断是否锁住了 gap，如果锁住了，则判定和插入意向锁冲突，当前插入操作就需要等待，也就是配合上面的间隙锁或临键锁一起防止幻读的发生。

 

因为插入意向锁是一种意向锁，只表示一种意向，所以插入意向锁之间不会相互冲突，多个插入操作同时插入同一个 gap 时，无需互相等待。

 

比如当前索引上有记录4和8，两个并发事务同时插入记录6和7，他们会分别为（4，8）加上gap锁，但相互之间并不冲突，在插入前会判断是否与已存在的锁存在冲突。

 

 

7、自增锁

自增锁是一种特殊的表级锁，如果一个表的某个行具有 AUTO_INCREMENT 的列，则一个事务在插入记录到这个表的时候，会先获取自增锁，如果一个事务持有自增所，则会堵塞其它事务对该表的插入操作，保证自增连续，MySQL中定了不同的自增算法，由变量 innodb_autoinc_lock_mode 指定，版本5.7中的默认值是1。

自增算法可以参考官网文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html