MySQL中SET @var=(SELECT..)与SELECT INTO的异常行为问询

已观测到的行为差异 #

• 锁机制：SET @my_var = (SELECT ..)会对匹配行加锁（是否锁定单行取决于查询是否命中唯一索引）；而等价的SELECT id INTO @my_var不会加锁，仅使用快照读。
• 快照读取：在REPEATABLE READ（RR）隔离级别下，SET语句内的SELECT会获取新的数据快照，读取其他会话已提交的更新值；SELECT INTO则始终使用事务初始阶段的快照，读取事务启动时的数据。

行为差异的根源 #

1. 语句执行逻辑的分类不同 #

MySQL对两种语句的执行逻辑归属有明确区分：

• SELECT ... INTO @var 是纯查询语句的扩展形式，完全遵循事务隔离级别的快照读规则。在RR级别下，它会复用事务启动时创建的一致性快照，仅读取快照内的数据，不会触发任何行锁（除非显式添加LOCK IN SHARE MODE/FOR UPDATE）。
• SET @var = (SELECT ...) 中的子查询被引擎归类为DML操作关联的查询。虽然SET本身是变量赋值，但内部子查询的执行逻辑被标记为“需要获取最新数据”，因此会触发当前读：直接读取数据的最新版本，同时根据查询条件对匹配行加锁（若命中唯一索引则锁定单行，否则锁定范围）。

2. 事务快照的获取时机不同 #

• 对于SELECT INTO，由于属于快照读范畴，事务启动时创建的一致性快照会被全程复用，因此在RR级别下不会读取到其他事务提交的新数据。
• 对于SET内的子查询，因为触发的是当前读，每次执行都会重新获取最新的数据快照，所以能读取到其他会话已提交的更新结果，同时也会因为当前读的特性对行进行加锁。

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