聊聊 mysql 中的加锁
1、环境说明
- mysql 版本：5.7.36
- 数据库隔离级别：RR
- 数据库表引擎：Innodb
2、加锁规则
加锁单位是 next-key lock ( 间隙锁 + 行锁)
- 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
- 原则2：查找过程中访问到的对象才会加锁。
- 原则3：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
- 原则4：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
- 原则5：唯一索引上的范围查询会访问到不满足条件的第一个值为止。(在 mysql 45 讲中有说明，丁老师认为这是一个 bug,且在 8 版本的时候已经修复，待会也会举例说明)
3、创建表语句
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t
values
(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25)
4、知识点
4.1 关键锁变化的规则
4.1.1 普通索引
4.1.1.1 等值查询，会退化为间隙锁
begin;
delete from t where c = 15;
-- next-key lock 增加范围锁（10,15]，（15，20]
-- 因为是等值查询故退化为（10,15]，（15，20)
-- 所以最终的加锁范围就是：(10,20)
4.1.1.2 不等值查询(范围查询)，并不会退化为间隙锁
begin;
delete from t where c > 15;
-- next-key lock 增加范围锁（15,20]
-- 因为是不等值查询所以并不会退化为间隙锁
-- 所以最终的加锁范围就是：(15,20]
4.1.2 唯一索引
4.1.2.1 等值查询，满足条件会退化为行锁，否则会退化为间锁锁
begin;
delete from t where id = 5;
-- 只会对 5 加行锁
begin;
delete from t where id = 6;
-- 加锁区间是 (5,10],因为是等值查询，且不满足索引条件，所以退化为间隙锁，所以就是 (5,10)
4.1.2.2 不等值查询(范围查询)，会访问到不满足条件的第一个值为止
4.2 分析使用语句
4.2.1 分析锁的语句
-- \G 只是为了格式化输出，不加也是可以的
show engine innodb status\G;
-- 这个是摘取输出中的一段关键的信息
MySQL thread id 159, OS thread handle 2700, query id 1786 localhost ::1 root update
insert into t values (6,6,6)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60 page no 3 n bits 80 index PRIMARY of table `eshop`.`t` trx id 8762
lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL
RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 0000000021c4; asc ! ;;
2: len 7; hex b4000001280128; asc ( (;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
对以上信息的说明：
- 1、index PRIMARY of table
eshop.ttrx id 8762 lock_mode X locks gap before rec insert intention waiting Record lock
1)、index PRIMARY of tabletest.t，表示这个语句被锁住是因为表 t 主键上的某个锁。(这里是主键索引，如果是普通索引的锁，那就是普通索引的信息。)
2)、insert intention 表示当前线程准备插入一个记录，这是一个插入意向锁。为了便于理解，你可以认为它就是这个插入动作本身。
3)、gap before rec 表示这是一个间隙锁，而不是记录锁。- 2、n_fields 5;
也表示了，这一个记录有 5 列：- 3、0: len 4; hex 8000000a; asc ;;
第一列是主键 id 字段，十六进制 a 就是 id=10。所以，这时我们就知道了，这个间隙就是 id=10 之前的，它表示的就是 (5,10)。- 4、1: len 6; hex 0000000021c4; asc ! ;;
第二列是长度为 6 字节的事务 id，表示最后修改这一行的是 trx id 为 8644 的事务。- 5、2: len 7; hex b4000001280128; asc ( (;;
第三列长度为 7 字节的回滚段信息。
2: len 7; hex b4000001280128; asc ( (;;- 6、 3: len 4; hex 8000000a; asc ;; 4: len 4; hex 8000000a; asc ;;
后面两列是 c 和 d 的值，都是 10。
4.2.2 查询事务执行详细信息
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 3341
trx_state: RUNNING
trx_started: 2022-02-18 01:28:13
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 13
trx_query: SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 3340
trx_state: RUNNING
trx_started: 2022-02-18 01:27:51
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 11
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
关于这个表中字段的说明可以参考官网的说明：
mysql 事务执行详情表
4.2.2 查询当前事务 id
mysql> SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
+--------+
| TRX_ID |
+--------+
| 3341 |
+--------+
1 row in set (0.00 sec)
4.3 说明
4.3.1 锁标识的说明
- lock_mode X [waiting] 表示next-key lock；
- lock_mode X locks rec but not gap 是只有行锁；
- locks gap before rec，就是只有间隙锁；
4.4 加锁分析要考虑的点
4.4.1 索引类型以及有无
如果没有索引，那就会锁表。如果有索引就会大大减少这样的几率。
4.4.2 分析 sql 语句(有的时候有索引，但是并没有走索引，也会导致加锁不一样)
比如下面的例子：
begin;
select * from t where c >= 10 and c < 21 for update;
-- 如果正常分析，锁应该是：(5,25]，但是你在 5.7 版本情况下实验应该会发现结果很意外
-- 从下面的分析可以看出来，并没有走索引。所以这也是导致他加锁不一样的原因
mysql> explain select * from t where c >= 10 and c < 21 for update;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | c | NULL | NULL | NULL | 6 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
4.4.3 注意覆盖索引
因为如果用到覆盖索引了，可能有的时候加锁也会不一样。
一会重点关注一下 [5.4 等值查询—普通索引](#5.4 等值查询—普通索引
) 的例子就知道了。
4.4.4 锁是加在索引上，而不是数据上
一会可以看一下 [5.6 范围查询—普通索引](#5.6 范围查询—普通索引) 的例子就会有所感悟
4.4.5 lock in share mode与for update的区别
- 区别一：
lock in share mode加的是读锁
for update 加的是写锁
- 区别二：
在非主键索引上通过两种方式加锁是有区别的。
lock in share mode 锁覆盖索引以及可能访问到的主键索引(因为 select * 就用不到覆盖索引，所以他就可能也会把可能访问到的主键索引锁住)，也就说：lock in share mode只锁非主键索引对应的B+树中的索引内容。
for update：如果对非主键索引使用 for update加锁就不一样了。 执行 for update 时，mysql会认为你接下来要更新数据，因此会通过非主键索引中的主键值继续在主键索引对应的b+数上查找到对应的主键索引项进行加锁，也就是说：for update的加锁内容是非主键索引树上符合条件的索引项，以及这些索引项对应的主键索引树上相应的索引项。在两个索引上都加了锁。
4.5 当前读、快照读
4.5.1 当前读
4.5.1.1 当前读形式
select...lock in share mode //(共享读锁)
select...for update
update , delete , insert
当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问>题。
例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产>生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。
4.5.1.1 当前读的实现方式：next-key锁(行记录锁+Gap间隙锁)
间隙锁：只有在Read Repeatable、Serializable隔离级别才有，就是锁定范围空间的数据
4.5.1 快照读
4.5.1.1 快照读形式
单纯的select操作，不包括上述 select … lock in share mode, select … for update。
Read Committed隔离级别：每次select都生成一个快照读。
Read Repeatable隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读。
4.5.1.2 快照读的实现方式：undolog和多版本并发控制MVCC
5、案例数据
5.1 间隙锁简单案例
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | insert into t value(9,9,9) //ok |
事务A会对数据库表增加（10，15]这个区间锁，经过原则4，退化为间隙锁(10,15)。
这时insert id = 12 的数据的时候就会因为区间锁（10，15)而被锁住无法执行。
这时insert id = 10 和 insert id = 15的数据是未被锁定，报错：Duplicate entry ‘15’ for key ‘PRIMARY’
5.2 间隙锁死锁问题
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | insert into t value(9,9,9) begin; |
| 3 | - | insert into t value(7,7,7) //blocked |
| 4 | insert into t value(7,7,7) //Deadlock found when trying to get lock; try restarting transaction | - |
不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的。
如果一个事务A获取到了（5,10)之间的间隙锁，另一个事务B也可以获取到（5,10)之间的间隙锁。这时就可能会发生死锁问题。
5.3 等值查询—唯一索引
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | insert into u (8,8,8); //blocked |
1.加锁的范围是（5,10] 的 next-key lock
2.由于数据是等值查询，并且表中最后数据 id = 10 不满足 id = 7的查询要求，故id = 10 的行级锁退化为间隙锁，(5,10)
执行说明：
1.事务B中id=8会被锁住，而 update id=10的时候不都会被锁住。
5.4 等值查询—普通索引
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | update t set d = d + 1 where id = 5;//ok |
1.初步加锁，加锁的范围是 (0,5]，(5,10]的 next-key lock
2.由于 c 是普通索引，根据原则 4，搜索到 5 后继续向后遍历直到搜索到 10 才放弃，故加锁范围为 (5,10]
3.根据原则 4，由于查询是等值查询，并且最后一个值不满足查询要求，故间隙锁退化为 (5,10)
4.最终的加锁范围是：(0,10)
执行说明：
1.因为加锁是对普通索引 c 加锁，而且因为索引覆盖，没有对主键进行加锁，所以事务 B 执行正常。
2.因为加锁范围（0,10），故insert c = 4 和 insert c = 7 执行阻塞,但是 insert id = 4,c = 11 的时候正常，此处又可以证明了锁加的是在索引上，因为 11 并不位于 (0,10) 所以可以成功
3.需要注意的是:
- lock in share mode 因为覆盖索引故没有锁主键索引，如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住。
- 事务 A 中的查询字段如果是 * 的话，也会锁住主键索引，那么 insert into 的主键在 (0,10) 之间也不会成功，就是事务 B 中的第 1 个语句就不会成功。(因为 0-10 的二级索引中会查询到 5 的记录，所以也会把此条记录的主键锁住)
5.5 范围查询—唯一索引
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | update t set d = d + 1 where id = 10;//blocked |
加锁说明
- next-key lock 增加范围锁（5,10]
- 根据原则 5，唯一索引的范围查询会到第一个不符合的值位置，故增加 (10，15]
- 因为等值查询有 id =10，根据原则 3 间隙锁升级为行锁，故剩余锁 [10,15]
- 因为查询并不是等值查询，故 [10,15] 不会退化成 [10,15)
- 最终的加锁范围是：[10,15]
执行说明：
1.insert id = 8 和 update id = 16，执行 ok。
2.update id = 15、insert id = 10 和 insert id = 13 和 insert id = 15， 执行阻塞。
查询阻塞锁的信息
mysql> show engine innodb stats\G;
-- 输出信息如下：
Spin rounds per wait: 272.00 RW-shared, 11.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3420
Purge done for trx's n:o < 3416 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 3419, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11, OS thread handle 139825693812480, query id 265 localhost
root updating
update t set d = d + 1 where id = 10
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `eshop`.`t`
trx id 3419 lock_mode X locks rec but not gap waiting Record lock, heap no 4
PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000000d4f; asc O;;
2: len 7; hex 3000000181022d; asc 0 -;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000b; asc ;;
5.6 范围查询—普通索引
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | update t set d = d+ 1 where id = 15;//ok |
加锁说明
- next-key lock 增加范围锁（5,10]，（10，15]
- 因为c是非唯一索引，故（5,10]不会退化为10
- 因为查询并不是等值查询，故[10,15]不会退化成[10,15)
- 所以最终加锁为：(5,15]
执行说明：
- update id=16，执行OK。
- update id=15、insert id=8 和 insert id=10 和 insert id=13，执行阻塞。
查看加锁详细信息：
show engine innodb status\G;
Spin rounds per wait: 276.00 RW-shared, 11.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3422
Purge done for trx's n:o < 3416 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 3421, ACTIVE 59 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 139825693812480, query id 273 localhost root updating
update t set d = d + 1 where c = 15
------- TRX HAS BEEN WAITING 43 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 80 index c of table `eshop`.`t` trx id 3421 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 8000000f; asc ;;
解释一下：为什么同样的更新 id = 15 这条记录，但是 where 条件是 c = 15 就阻塞了，但是 id = 15 就成功了呢？
- 首先我们 来看一下 2 个 sql 的执行计划：
mysql> explain update t set d = d + 1 where c = 15;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| 1 | UPDATE | t | NULL | range | c | c | 5 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
mysql> explain update t set d = d + 1 where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | UPDATE | t | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
- 从执行计划可以看出来，当 c = 15 的时候走的是 c 的索引，前面大家已经知道 c 已经加锁了，所以此时肯定会阻塞呀，而且从查询锁的信息输出也能知道，此时是被 c 的索引上的 next-key lock 阻塞了。(index c of table
eshop.ttrx id 3421 lock_mode X waiting Record lock)- 但是当 where 条件是 id = 15 的时候走的是主键索引，主键索引又没有被锁定，所以肯定是可以执行的了。
5.7 普通索引-等值问题
上面的数据增加一行（30,10,30），这样在数据库中存在的c=10的就有两条记录
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | update t set d = d+ 1 where c = 15; //ok |
加锁说明：
- next-key lock 增加范围锁（5,10]，（10，15]
- 因为是等值查询故退化为（5,10]，（10，15)
- 所以最终的加锁范围就是：(5,15)
执行说明：
- update c=15 成功，insert id = 12阻塞。
加锁的范围如下图(其实应该也很好理解，因为普通索引，并不是唯一索引，所以他分控制这中间所有可能产生的值):
5.8 普通索引-等值Limit问题
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | update t set d = d+ 1 where c = 15; //ok |
- 根据 5.7 改造，将 delete 增加 limit 2 的操作
- 因为知道了数据加锁值加 2 条，故在加锁（5，10] 之后发现已经有两条数据，故后面不在向后匹配加锁。所以事务B执行成功，加锁范围如下
- 所以说 delete 语句的话，有条件的时候如果可以加 limit 最好加一个 limit 对加锁有好处，减少锁冲突的频率。
6、看几个有疑惑的例子
6.1 例子 1
6.1.1. 建表语句、数据
-- create 表语句
CREATE TABLE t (
id INT PRIMARY KEY AUTO_INCREMENT,
b INT,
KEY b(b)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO t (id, b)
VALUES (1, 2),
(3, 4),
(5, 6),
(7, 8),
(9, 10);
6.1.2 现象
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | insert into t value(0, 4);//blocked |
加锁分析：
先加 (4,6] (6,8]，因为是等值查询，则退化为间隙锁，所以为 (6,8)
最终就是 (4,8)
但是为什么这里会阻塞呢？
6.1.3 分析验证
- insert 中 id 为 0,这个就相当于 id 自增长了，所以这里其实 id 就是 10 了
分析一下索引的信息：
查看锁的信息：
---TRANSACTION 3442, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 139825693812480, query id 308 localhost root update
insert into t1 value(0,4)
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 80 index b of table `eshop`.`t1` trx id 3442 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000005; asc ;;
从上面的锁信息可以看到插入意向锁，被 6 之前的间隙锁阻塞了，所以我们的分析是正确的，他是插入到了 3,4 这条记录的后面，所以不能成功。
6.2 例子 2
6.2.1 建表语句、数据
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t
values
(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25)
6.2.2 现象
| 步骤 | 事务 A | 事务 B |
|---|---|---|
| 1 | begin; | - |
| 2 | - | update t set d =d + 1 where id = 5;//blocked |
6.2.3 分析验证
上面 3 个语句的查看锁状态的输出：
- update t set d =d + 1 where id = 5
> MySQL thread id 3, OS thread handle 139819775452928, query id 25 localhost root updating
update t set d =d + 1 where id = 5
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 3 n bits 80 index PRIMARY of table `eshop`.`t` trx id 2577 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000000a07; asc ;;
2: len 7; hex a70000011b011c; asc ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
- insert into t values (1,1,1);
MySQL thread id 3, OS thread handle 139819775452928, query id 29 localhost root update
insert into t values (1,1,1)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 3 n bits 80 index PRIMARY of table `eshop`.`t` trx id 2577 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000000a07; asc ;;
2: len 7; hex a70000011b011c; asc ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
- insert into t values (26,100,24)
---TRANSACTION 2624, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 139819775452928, query id 152 localhost root update
insert into t values (26,100,24)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 3 n bits 80 index PRIMARY of table `eshop`.`t` trx id 2624 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
加锁分析：
先加 (5,10] (10,15] (15,20] (20,25]，因为是等值查询，则退化为间隙锁，所以为 (20,25)
最终就是 (5,25)
但是你去实验会发现他全部锁住了，0-supremum 都不可以操作
这个时候我们就要去看看这个语句的 执行计划 了
mysql> explain select * from t where c >=10 and c < 21 for update;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | c | NULL | NULL | NULL | 6 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
从执行计划中我们 可以看到，key 为NUll，就是并没有走索引，那就不怪了，因为没有走索引，所以他把 c 的所有都加索引了，所以你会发现所有的区间都不可执行。
好了，今天的总结就到这里，这个只是基于看的 mysql 专栏自己的总结，如果有问题，请大家指出，一起探讨。
参考资料：
MySQL 实战 45 讲
扫一扫
233
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
