CREATE
DATABASE IF NOT EXISTS testdb;
use
testdb;
-- 注意:必须为条件列建立非唯一索引,否则锁全表,下文会验证
CREATE TABLE students_nk_lock
(
id INT PRIMARY KEY,
name VARCHAR(50),
score INT,
key idx_score(score)
);
INSERT INTO students_nk_lock (id, name, score)
VALUES (1, 'Alice', 85),
(4, 'Bob', 90),
(7, 'Carol', 95),
(10, 'Lucy', 100);
版本 5.7 ,默认 RR 级别。
BEGIN;
SELECT * FROM students_nk_lock WHERE score >= 90 FOR UPDATE;
BEGIN;
INSERT INTO students_nk_lock VALUES(0, 'Dave', 83); -- 阻塞
INSERT INTO students_nk_lock VALUES(2, 'Dave', 84); -- 阻塞
UPDATE students_nk_lock SET score=85 WHERE score=85; -- 阻塞
INSERT INTO students_nk_lock VALUES(5, 'Dave', 85); -- 阻塞
INSERT INTO students_nk_lock VALUES(5, 'Dave', 91); -- 阻塞
INSERT INTO students_nk_lock VALUES(11, 'Dave', 101); -- 阻塞
对于普通索引,按个人理解应该是锁住 ({1, 85} -> +inf)
这段索引记录范围,然而实测貌似锁了全表,甚是不解!来请教相关大佬
![]() |
1
wenxueywx 99 天前
SELECT * FROM students_nk_lock WHERE score >= 90 FOR UPDATE;
|
![]() |
2
wenxueywx 99 天前 ![]() 查询走的全表扫描吧
你 explain 看看 |