MySQL中SELECT ... FOR UPDATE锁范围与索引的关联疑问

场景复现 #

表结构与数据初始化 #

CREATE TABLE example_table (
    id INT AUTO_INCREMENT,
    col1 VARCHAR(255),
    col2 INT,
    PRIMARY KEY (id)
);
INSERT INTO example_table (col1, col2) VALUES ('a',1); 
INSERT INTO example_table (col1, col2) VALUES ('b',2); 
INSERT INTO example_table (col1, col2) VALUES ('c',3); 
INSERT INTO example_table (col1, col2) VALUES ('d',4); 

事务执行现象 #

• 无索引状态：两个独立事务分别执行SELECT * FROM example_table where col1="a" FOR UPDATE;和SELECT * FROM example_table where col1="b" FOR UPDATE;，第二个查询会被阻塞，必须等待第一个事务释放排他锁才能执行。
• 为col1添加索引后：两个查询可以同时执行，无需互相等待。

原因拆解 #

非索引列导致全表锁的逻辑 #

当查询条件使用非索引列时，数据库没办法通过索引快速定位目标数据，只能启动全表扫描来匹配符合条件的行。而SELECT ... FOR UPDATE的核心是要确保当前事务拿到的数据在后续操作中不会被其他事务修改——如果只锁已经扫描到的行，扫描过程中其他事务修改了还没扫描到的行，就会破坏事务的一致性。因此数据库会直接对全表所有行加上排他锁，这就导致后续同类型的查询必须等待锁释放。

索引列实现行级锁的逻辑 #

当查询条件使用索引列时，数据库可以通过索引直接定位到目标行的存储位置，不需要遍历全表。此时SELECT ... FOR UPDATE只会对索引指向的具体数据行（以及对应的索引节点）加上排他锁，其他未被索引定位到的行不受锁的影响。所以不同事务锁定不同目标行时，彼此不会产生阻塞。

内容的提问来源于stack exchange，提问作者yuria