MySQL Update锁机制探究【转】

基于MySQL-8.0 with Innodb存储引擎，通过观察总结出UPDATE语句在各种情况下产生的锁，包括MDL锁、意向锁、行锁和间隙锁等。

测试环境

MySQL的Update语句会使用什么锁，取决于很多因素。本文基于MySQL-8.0 with Innodb存储引擎、可重复读隔离级别，具体版本和建表语句如下：

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec);

mysql> SHOW CREATE TABLE t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`),
  UNIQUE KEY `d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1 row in set (0.00 sec)

MDL读锁

MDL是指Metadata Locking，中文叫元数据锁，它是一个表级锁，用来保护表结构、触发器等并发访问的一致性。UPDATE语句产生的mdl读锁会阻塞表结构修改语句，例如：

事务1执行UPDATE语句：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t1 SET age=222 WHERE id=5;
Query OK, 1 row affected (0.02 sec)

事务2增加索引将被阻塞

mysql> alter table t1 add index index_age(`age`);
/* 被阻塞 */

意向写锁

意向锁也是一个表级锁，执行UPDATE语句将会产生一个意向写锁。意向写锁只会阻塞LOCK TABLES xxx WRITE语句，例如：

事务1执行UPDATE语句：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t1 SET age=222 WHERE id=5;
Query OK, 1 row affected (0.02 sec)

事务2获取表级写锁将被阻塞：

mysql> LOCK TABLES t1 WRITE;
/* 被阻塞 */

行级锁

行级锁包括：

记录锁 Record Lock
间隙锁 Gap Lock
Next-Key Lock （记录锁和间隙锁的组合）

行级锁怎么加，会受到索引访问方式的影响，下面通过例子说明。

例1: 通过主键更新1条记录

UPDATE t1 SET age=222 WHERE id=5;

这是一条最常见的UPDATE语句，WHERE条件使用主键索引来更新数据，加锁分为2种情况：

id=5的记录存在，只会在id=5的主键索引加上一个行锁。
id=5的记录不存在，会在主键索引上加上一个间隙锁。

间隙锁会阻止其它事务往这个间隙中插入数据。事务1执行UPDATE不存在的记录：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id from t1;
+----+
| id |
+----+
|  1 |
|  5 |
|  6 |
+----+
3 rows in set (0.00 sec)

mysql> UPDATE t1 SET age=444 WHERE id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

表格中id列只有1、5、6，执行UPDATE ... WHERE id=2将产生一个间隙锁(1, 5)。例如插入id=3的记录将被阻塞：

mysql> INSERT into t1 (id,age) values (3, 80);
/* 被阻塞 */

例2: 通过主键更新一个范围

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t1 SET age=444 WHERE id>=5;
Query OK, 2 rows affected (0.02 sec)

当WHERE条件使用主键的范围来更新时，会同时加上行锁和间隙锁。例如表格中id列只有1、5、6，上述UPDATE语句将会：

给id=5加上行锁
给id=6加上行锁
加上间隙锁(6, +∞)

插入(id=8)的记录将被间隙锁阻塞：

mysql> INSERT into t1 (id,age) values (8, 80);
/* 被阻塞 */

例3：通过无索引的字段更新

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t1 SET age=100 WHERE age=200;
Query OK, 0 rows affected (0.00 sec)

上面例子中，使用WHERE age=200条件，其中age字段没有索引，上述UPDATE语句将会锁住表格中的每一条记录和每个间隙，其它UPDATE、INSERT和DELETE语句都无法执行：

mysql> INSERT into t1 (id,age) values (8, 80);  /* INSERT被阻塞 */ 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> UPDATE t1 SET age=300 WHERE id=1;  /* UPDATE被阻塞 */ 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> DELETE from t1  WHERE id=1;  /* DELETE被阻塞 */ 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

例4：通过唯一索引更新

表格中有2个辅助索引c和d，其中d是唯一索引。WHERE条件使用唯一索引更新记录：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t1 SET age=111 WHERE d=80;
Query OK, 1 row affected (0.04 sec)

当d=80记录存在时，将会：

给唯一索引d=80加上行锁（没有间隙锁）
d=80对应记录的主键索引也会加上行锁（没有间隙锁）
没有用到的辅助索引c不受影响

当d=80的记录不存在时，将会在唯一索引d上加上间隙锁（与例1类似）。

使用唯一索引进行范围更新时：

mysql> UPDATE t1 SET age=111 WHERE d>70;
Query OK, 3 rows affected (0.01 sec)

与例2类似，将会：

给匹配到的主键索引记录加上行锁
给匹配到的唯一辅助索引记录加上行锁
给辅助索引加上间隙锁

例5：通过(非唯一)二级索引更新

mysql> UPDATE t1 SET age=111 WHERE c=300;
Query OK, 1 row affected (0.00 sec)

表格中c字段有非唯一辅助索引，以上SQL将：

在辅助索引上加上Next-key锁
给WHERE条件匹配到的记录加上行锁

例6：多个条件更新

WHERE中包含多个条件时，加什么锁取决于使用了哪个索引，可以使用explain查看：

mysql> explain UPDATE t1 SET age=222 WHERE age=111 and c=100 and d=200;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| id | SELECT_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t1    | NULL       | range | d,c           | d    | 5       | const |    1 |   100.00 | Using WHERE |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.06 sec)