mysql 间隙锁

[rqxiao]

mysql 版本 5.7.2

隔离级别 rr

mysql 间隙锁 ！不是锁定行，也不是锁定某个列，是锁定对应的索引。测试表 tx_test ，age 字段加了索引了。

---

-- Table structure for tx_test

---

DROP TABLE IF EXISTS `tx_test`;
CREATE TABLE `tx_test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

---

-- Records of tx_test

---

BEGIN;
INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (1, '123', 3);
INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (2, '44', 4);
INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (3, '55', 5);
INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (4, '41', 50);
COMMIT;

#session1

begin;

SELECT * from tx_test;

update tx_test set name='aaaaa' where age >1 and age <20;

SELECT * from tx_test;

COMMIT;

#session2

begin;

SELECT * from tx_test;

INSERT INTO tx_test (id, name, age) VALUES (88, '41', 77);

SELECT * from tx_test;

COMMIT;

为什么 session2 的 INSERT INTO tx_test (id, name, age) VALUES (88, '41', 77); 会阻塞呢 。session1 的查询不是应该 锁定了 -无穷到 50 吗

[movq]

第一个事务后面有 SELECT * from tx_test;，所以要全锁住，不然不是 repeatable read

[rqxiao]

@movq session1 改成 update tx_test set name='aaaaa' where age >1 and age <5;。session2 就可以插入

[Inf1nity]

我这边在 MySQL 8.0 下面测试的 session1 的查询执行（未 commit ）后，给 age = 3, age = 4, age = 5 的辅助索引加了 next key lock ，同时给对应的主键 id = 1, id = 2, id = 3 加了 record lock ，之后执行 session2 并没有被阻塞。
楼主可以试试用下面这个查询查看加锁情况：

SELECT
ENGINE,
EVENT_ID,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS,
LOCK_DATA
FROM performance_schema.data_locks;

[wps353]

@rqxiao
楼主，试试 update tx_test force index(age) set name='aaaaa' where age >1 and age <20;
看看 session2 能不能插入成功？

[rqxiao]

@wps353 可以插入成功，这个 update 操作还会区分数据分部情况来判断锁不锁索引吗。。

[Chaox]

我猜测是你这个数据量太少了，mysql 优化器就没走索引，直接锁表了。所以 4 楼加上 force index(age)走了索引就可以了
网上的资料：
即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看 MySQL 优化器的判断。当然你也可以在 sql 语句中写明强制走某个索引。

[lookStupiToForce]

顺道提一嘴
mysql 从 5.6 开始就支持 explain update 了
https://dev.mysql.com/doc/refman/5.6/en/explain.html

[wangxin3]

8.0.29 亲测无这个问题

[ivanMeng]

经过我不断实验和分析 。。。。
explain 可以看到 age<4 和 age <20 走了不通的命中 key
二级索引太少了 还没有 All 快 索引走了 Primarykey 所以锁全表了。。。。
让我也想了多半天 都怀疑间隙锁了。。。。
而且 8 的版本 没毛病。。。

[koloonps]

@ivanMeng @wangxin3 为什么我的 8.0.29 有这个问题........