https://at.itzhai.com/itzhai_logo_sm.jpg IT宅

MySQL精进之路

MySQL相关问题一网打尽
帅旋
关注
充电
IT宅站长，技术博主，架构师，全网id：arthinking。
返回专题首页

阿里二面：间隙锁和临键锁的经典考题

发布于 2024-09-01 | 更新于 2024-09-01
MySQL
34

各位同学大家好，我是帅旋，今天分享一道经典的MySQL锁的面试题。是某个小伙伴面试的时候遇到的，虽然没有回答好，面试官还是放了他一马，看来眼缘对上了。

面试官
接下来你对考考MySQL锁的掌握程度。
  1. 有如下数据库表和数据：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE t_user (
  id BIGINT NOT NULL,
  username VARCHAR(100) NOT NULL DEFAULT '',
  age TINYINT NOT NULL DEFAULT 0,
  invite_code BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_invite_code (invite_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表';

insert into t_user(id ,username, age, invite_code) values(10, 'Pony', 20, 10000);
insert into t_user(id ,username, age, invite_code) values(20, 'Tim', 23, 11000);
insert into t_user(id ,username, age, invite_code) values(30, 'Jack', 20, 12000);
insert into t_user(id ,username, age, invite_code) values(40, 'Tom', 22, 13000);
insert into t_user(id ,username, age, invite_code) values(50, 'xiaobo', 39, 14000);
insert into t_user(id ,username, age, invite_code) values(60, 'dingding', 32, 15000);

事务A和事务B同时开启，执行以下SQL：

事务A执行以下SQL：

1
2
select * from t_user where invite_code=11001 for update;
insert into t_user(id ,username, age, invite_code) values(1, '水果老板', 20, 11001);

事务B执行以下SQL：

1
2
update t_user set username='批发总监' where invite_code=11002;
insert into t_user(id ,username, age, invite_code) values(1, '批发总监', 20, 11002);
面试官
能够正常执行成功吗？为什么？每条sql都加了什么锁？

我们来验证下这道题。

先给下运行环境：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.04 sec)

表中的数据如下：

1
2
3
4
5
6
7
8
9
10
+----+----------+-----+-------------+
| id | username | age | invite_code |
+----+----------+-----+-------------+
| 10 | Pony     |  20 |       10000 |
| 20 | Tim      |  23 |       11000 |
| 30 | Jack     |  20 |       12000 |
| 40 | Tom      |  22 |       13000 |
| 50 | xiaobo   |  39 |       14000 |
| 60 | dingding |  32 |       15000 |
+----+----------+-----+-------------+

两个事务分别执行如下SQL：

事务 事务A 事务B
步骤一 set autocommit=0;
begin;		 set autocommit=0;
begin;
步骤二 select * from t_user where invite_code=11001 for update;
步骤三 update t_user set username=‘批发总监’ where invite_code=11002;
步骤四 insert into t_user(id ,username, age, invite_code) values(1, ‘水果老板’, 20, 11001);
步骤五 commit;

MySQL 5.7查询锁相关命令：

  • 查看事务：select * from INFORMATION_SCHEMA.INNODB_TRX;
  • 查看锁(包含正在锁定的事务，和想获取锁的事务)：select * from INFORMATION_SCHEMA.INNODB_LOCKS;
  • 查看锁等待（向获取锁的事务）：select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
  • 释放锁：kill trx_mysql_thread_id

MySQL 8.0查看上了哪些锁：select * from performance_schema.data_locks\G;

查看当前会话的线程id：

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID();

查看当前会话的事务id：

SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();

执行步骤分析

步骤一

设置为手动提交事务，分别各自开启事务。

步骤二

事务A执行第一个SQL：

1
select * from t_user where invite_code=11001 for update;

查看上锁情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:1079:140691375161792
ENGINE_TRANSACTION_ID: 23360
            THREAD_ID: 58
             EVENT_ID: 48
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140691375161792
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:17:5:4:140691376364576
ENGINE_TRANSACTION_ID: 23360
            THREAD_ID: 58
             EVENT_ID: 48
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_invite_code
OBJECT_INSTANCE_BEGIN: 140691376364576
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 12000, 30
2 rows in set (0.01 sec)

从执行结果可知，加了两个锁：

  • 表锁：X类型的意向锁：
    • LOCK_TYPE: TABLE
    • LOCK_MODE: IX
  • 行锁：X类型的间隙锁，在idx_invite_code索引上面加了间隙锁，范围：(11000, 12000)
    • LOCK_TYPE: RECORD 表示行级锁
    • LOCK_MODE: X,GAP 表示间隙锁

解释：尝试通过索引去更新的记录不存在时，会尝试加上间隙锁，避免其他事务插入数据造成幻读。

意向锁本身不阻止其他事务读取表，但是它会阻止其他事务获得对整个表的共享锁。

步骤三

事务B执行第一个SQL：

1
update t_user set username='批发总监' where invite_code=11002;

查看上锁情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:1079:140691375161792
ENGINE_TRANSACTION_ID: 23360
            THREAD_ID: 58
             EVENT_ID: 48
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140691375161792
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:17:5:4:140691376364576
ENGINE_TRANSACTION_ID: 23360
            THREAD_ID: 58
             EVENT_ID: 48
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_invite_code
OBJECT_INSTANCE_BEGIN: 140691376364576
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 12000, 30
4 rows in set (0.00 sec)

从执行结果可知，加了两个锁：

  • 表锁：X类型的意向锁：
    • LOCK_TYPE: TABLE
    • LOCK_MODE: IX
  • 行锁：X类型的间隙锁，在idx_invite_code索引上面加了间隙锁，范围：(11000, 12000)
    • LOCK_TYPE: RECORD
    • LOCK_MODE: X,GAP

解释：虽然事务A也加了间隙锁(11000, 12000)，但是由于这里是更新记录，不是插入记录，所以并不会受到间隙锁的影响。间隙锁是可以重复添加的。

步骤四

事务A执行第二个SQL：

1
insert into t_user(id ,username, age, invite_code) values(1, '水果老板', 20, 11001);

执行之后，发现卡住了，我们看看锁的情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:17:5:4:140691376365952
ENGINE_TRANSACTION_ID: 23360
            THREAD_ID: 58
             EVENT_ID: 51
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_invite_code
OBJECT_INSTANCE_BEGIN: 140691376365952
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: 12000, 30
7 rows in set (0.01 sec)

这里生成了一个插入意向锁的间隙锁。

解释：事务A插入的invite_code=11001位置被事务B的间隙锁(11000, 12000)锁住了，所以只能等待，生成了一个等待状态的插入意向间隙锁。

注意：在使用InnoDB存储引擎时，事务在请求锁时首先生成一个锁结构，然后设置该锁的状态。锁的状态可以是等待状态（如果与现有锁冲突）或正常状态（即已成功获得锁）。如果一个锁处于等待状态，它实际上并没有被事务成功获取；只有当锁的状态变为正常状态时，才表示事务成功获取了该锁。[1]

关于行级锁：

LOCK_TYPE: RECORD代表行级锁，包括了：记录锁，间隙锁，临键锁（Next-key Lock），区分：

  • LOCK_MODE: X, REC_NOT_GAP 记录锁，锁定丹田记录，阻止其他事务插入，更新，删除这行数据；
  • LOCK_MODE: X, GAP 间隙锁，对间隙加锁，避免间隙之间被插入数据

步骤五

事务B执行commit。

此时发现事务A插入成功了：

1
2
mysql> insert into t_user(id ,username, age, invite_code) values(1, '水果老板', 20, 11001);
Query OK, 1 row affected (9.92 sec)

解释：事务B提交事务之后，就释放了间隙锁(11000, 12000)，最终事务A继续执行。

追问

面试官
既然都答上来了，那我换一种执行方式，还是这个表，我直接执行这个SQL，会产生哪些锁呢？
1
update t_user set username='批发总监' where invite_code=12000;

我们查看锁情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:1079:140691375161792
ENGINE_TRANSACTION_ID: 23369
            THREAD_ID: 62
             EVENT_ID: 31
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140691375161792
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:17:5:4:140691376364576
ENGINE_TRANSACTION_ID: 23369
            THREAD_ID: 62
             EVENT_ID: 31
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_invite_code
OBJECT_INSTANCE_BEGIN: 140691376364576
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 12000, 30
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:17:4:9:140691376364920
ENGINE_TRANSACTION_ID: 23369
            THREAD_ID: 62
             EVENT_ID: 31
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140691376364920
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 30
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140691618442376:17:5:5:140691376365264
ENGINE_TRANSACTION_ID: 23369
            THREAD_ID: 62
             EVENT_ID: 31
        OBJECT_SCHEMA: test
          OBJECT_NAME: t_user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_invite_code
OBJECT_INSTANCE_BEGIN: 140691376365264
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 13000, 40
4 rows in set (0.00 sec)

可以发现加了四个锁：

  • 表级意向排他锁（IX）

    • 锁类型 (LOCK_TYPE): TABLE

    • 锁模式 (LOCK_MODE): IX

    • 作用：这是意向锁，表明有事务意图在表的行上进行更具体的锁定操作。这种锁本身不锁定任何特定行，而是表示对表的操作意图；

  • 记录锁
    • 锁类型 (LOCK_TYPE): RECORD
    • 锁模式 (LOCK_MODE): X
    • 数据 (LOCK_DATA): 12000, 30
    • 作用：直接在符合invite_code=12000条件的记录上加排他锁，这意味着该记录不能被其他事务读取或修改；
  • 主键索引上的记录锁（不包括间隙）
    • 锁类型 (LOCK_TYPE): RECORD
    • 锁模式 (LOCK_MODE): X, REC_NOT_GAP
    • 数据 (LOCK_DATA): 30
    • 作用：这是针对主键的排他锁，锁定了特定的记录，避免通过主键对这条记录进行变更；
  • 间隙锁
    • 锁类型 (LOCK_TYPE): RECORD
    • 锁模式 (LOCK_MODE): X, GAP
    • 数据 (LOCK_DATA): 13000, 40
    • 作用：锁定invite_code=12000与下一记录（invite_code=13000）之间的间隙。这防止了其他事务在这两个invite_code值之间插入新记录。

总结

这道题重点是考察对间隙锁和意向锁的掌握程度。需要注意的是：

  • 间隙锁是可以重复添加的；
  • 间隙锁是为了避免其他事务插入数据，但是可以修改间隙内的数据；
  • 如果插入操作被间隙锁阻塞了，会生成一个插入意向间隙锁，并且为等待状态。

References

  1. 17.7.1 InnoDB Locking. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html ↩︎

为什么Mark Word中的指针是30位？...
说说JVM对象的内存布局？

本文作者： 帅旋

本文链接： https://www.itzhai.com/columns/faqs/mysql/classic-questions-on-gap-lock-and-next-key-lock.html

版权声明： 版权归作者所有，未经许可不得转载，侵权必究！联系作者请加公众号。

×
IT宅

关注公众号及时获取网站内容更新。

请帅旋喝一杯咖啡

咖啡=电量，给帅旋充杯咖啡，他会满电写代码！

IT宅

关注公众号及时获取网站内容更新。