毛英东的个人博客
0%

面试官：MySQL的UPDATE语句会加哪些锁？

发表于 更新于 分类于

基于MySQL 8.0和Innodb存储引擎，总结update语句在不同情况下产生的锁，包括MDL锁、意向锁、行锁和间隙锁等。

测试环境

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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语句：

1
2
3
4
5
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增加索引将被阻塞：

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

意向写锁

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

事务1执行UPDATE语句：

1
2
3
4
5
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获取表级写锁将被阻塞：

1
2
mysql> lock tables t1 write;    
/* 被阻塞 */

行级锁

行级锁包括：

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

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

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

1
update t1 set age=222 where id=5;

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

mysql>

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

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

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

1
2
3
4
5
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)的记录将被间隙锁阻塞：

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

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

1
2
3
4
5
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语句都无法执行：

1
2
3
4
5
6
7
8
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条件使用唯一索引更新记录：

1
2
3
4
5
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类似）。

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

1
2
mysql> update t1 set age=111 where d>70;
Query OK, 3 rows affected (0.01 sec)

与例2类似，将会：

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

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

1
2
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查看：

1
2
3
4
5
6
7
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)

上面例子中where条件中同时包含唯一索引d、非唯一索引c、无索引的字段age，从explain的结果中可以看出，最终使用了索引d，则加锁情况参考例4：通过唯一索引更新。

update语句加锁总结

update语句可能加的锁包括：

  • MDL锁
  • 意向锁
  • 行锁
  • 间隙锁
  • Next-key Locking