MySQL事务疑问：为何普通SELECT正常，CREATE TABLE...SELECT会阻塞？

这问题我之前排查过，核心差异在于InnoDB的一致性读（快照读）和当前读的锁机制，咱们拆解来看：

先看第一个线程的锁持有情况 #

第一个线程执行的代码：

START TRANSACTION;
SELECT 1 FROM t GROUP BY 1 LOCK IN SHARE MODE;
UPDATE t SET ...=...;

• LOCK IN SHARE MODE会给表t的所有行加上共享锁（S锁）（因为GROUP BY 1会触发全表扫描）；
• 后续的UPDATE会把需要修改的行的S锁升级为排他锁（X锁）；
• 由于事务未提交，这些S锁和X锁会一直被持有，不会释放。

为什么普通SELECT * from t LIMIT 10能正常执行？ #

InnoDB在默认的REPEATABLE READ隔离级别下，普通SELECT用的是一致性读（快照读）：它不会去请求任何锁，而是直接从undo日志里读取事务启动时的数据快照，完全不触及第一个线程持有的行锁，所以自然不会被阻塞。

为什么CREATE TEMPORARY TABLE tmp AS (SELECT * FROM t LIMIT 10)会被阻塞？ #

重点来了：虽然查询语句和普通SELECT一样，但MySQL处理CREATE TABLE ... AS SELECT（包括临时表）时，不会使用一致性读，而是强制用当前读（Current Read）。

当前读的特点是必须读取最新的已提交数据，这就需要对读取的行请求共享锁（S锁）。但第一个线程的事务还持有部分行的X锁（来自UPDATE）和其他行的S锁：

• X锁和S锁是互斥的，当第二个线程的当前读尝试访问被UPDATE修改过的行时，请求的S锁会被X锁直接阻塞；
• 哪怕是未被修改的行，当前读也需要确保读取到最新状态，而第一个线程未提交的事务会让当前读等待锁释放才能继续。

简单总结：普通SELECT走快照读绕开了锁，而创建临时表的SELECT走当前读必须申请锁，所以被阻塞了。

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