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语句将会锁住表格中的每一条记录和每个间隙,其它UPDATEINSERTDELETE语句都无法执行:

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个辅助索引cd,其中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