为何SELECT ... FOR SHARE未被其他事务的UPDATE阻塞？场景解析

这是InnoDB基于索引加锁的特性导致的，核心原因在于覆盖索引查询无需回表，不会触及被排他锁占用的聚簇索引行，具体拆解如下：

1. Session1的Update操作加锁情况 #

当Session1执行update table0 set coAttr0_0=2;时，InnoDB会完成以下加锁动作：

• 找到聚簇索引（主键pkAttr0对应的索引）中的目标行，给该行加排他锁（X锁）；
• 由于修改的coAttr0_0字段存在于二级索引table0index_commAttr0中，会同步给该二级索引的对应记录加X锁；
• 二级索引table0index_pk仅包含pkAttr0字段，本次修改不涉及该索引的内容，因此这个索引上的记录不会被加X锁。

2. Session2两次FOR SHARE查询的差异 #

第一次查询：select pkAttr0 from table0 for share; #

这个查询属于覆盖索引查询：MySQL可以直接通过二级索引table0index_pk获取到pkAttr0的值，完全不需要回表访问聚簇索引的行。
由于table0index_pk上的记录没有被Session1加X锁，Session2可以顺利给这个索引的记录加共享锁（S锁），因此不会被阻塞。

第二次查询：select * from table0 for share; #

这个查询需要获取所有字段，必须回表访问聚簇索引的行。而Session1已经给聚簇索引的目标行加了X锁，S锁与X锁互斥，因此Session2的查询会被阻塞，直到Session1提交或回滚释放锁。

补充：如果没有手动创建table0index_pk这个二级索引，优化器会直接使用主键索引执行查询，此时当前读会触及被加X锁的聚簇索引行，查询就会被阻塞。

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