mysql执行SQL如何保证事务一致性_mysql执行机制解析

事务提交前的数据写入 redo log buffer 和 Buffer Pool，标记为脏页；未提交修改对其他事务不可见靠 undo log+read view 实现 MVCC，而非未写磁盘。

事务提交前的数据到底写到哪了

MySQL 执行 INSERT、UPDATE、DELETE 时，即使还没 COMMIT，数据也并非“只在内存里晃荡”。InnoDB 会立即将变更写入 redo log buffer（内存中的重做日志缓冲区），同时更新 Buffer Pool 中对应页的副本。这些页此时被标记为“脏页”，但尚未刷盘。

关键点在于：未提交事务的修改对其他事务不可见，靠的是 undo log + read view 实现 MVCC，而不是靠“没写磁盘”来保证隔离性。

• redo log 保证崩溃可恢复（持久性），它记录的是“物理逻辑日志”，不是 SQL 语句
• undo log 存在 ibdata1 或独立表空间中，用于回滚和构造一致性读视图
• 如果事务中途崩溃，redo log 中未刷盘的部分丢失，但已刷盘的 redo log 会在重启时重放；而未 COMMIT 的事务，其 undo log 记录会被用于回滚——这正是 ACID 中原子性与持久性的协同机制

autocommit=OFF 时哪些操作会隐式开启新事务

当 autocommit=OFF，你显式执行 BEGIN 或 START TRANSACTION 后，事务边界看似清晰。但 MySQL 在某些语句执行时会自动提交当前事务并开启新事务，导致你以为的“大事务”其实被悄悄拆开。

典型触发语句包括：ALTER TABLE、DROP TABLE、CREATE INDEX、RENAME TABLE、ANALYZE TABLE，以及任何 DDL 操作（除 CREATE TEMPORARY TABLE 外）。

• 执行 ALTER TABLE t1 ADD COLUMN x INT 前若有未提交事务，MySQL 会先隐式 COMMIT 当前事务，再执行 DDL，DDL 完成后再开启新事务（如果 autocommit 仍为 OFF）
• SELECT 不会触发隐式提交，但 SELECT …… FOR UPDATE 或 SELECT …… LOCK IN SHARE MODE 属于事务内加锁操作，必须在事务中执行，否则会自动开启一个只读事务（且立即释放锁）
• 可通过 SHOW ENGINE INNODB STATUSG 查看 TRANSACTIONS 部分确认当前活跃事务数，避免误判

binlog 和 redo log 如何协同保证主从一致性

MySQL 主从复制依赖 binlog，而崩溃恢复依赖 redo log。两者格式不同、写入时机不同、存储位置不同，但必须保持逻辑一致，否则可能导致主从数据不一致或恢复后 binlog 缺失。

InnoDB 使用“两阶段提交（2PC）”协调二者：

• 第一阶段：事务 prepare 时，将 redo log 写入磁盘并标记为 PREPARE 状态
• 第二阶段：写入 binlog 到磁盘后，再将 redo log 标记为 COMMIT
• 若 crash 发生在 prepare 后、binlog 写入前：重启后该事务被回滚（因无对应 binlog，不可复制）
• 若 crash 发生在 binlog 写入后、redo commit 前：重启后该事务被提交（因有 binlog，必须保证主库和从库行为一致）

这个机制要求 sync_binlog=1 和 innodb_flush_log_at_trx_commit=1 同时启用，否则可能破坏 2PC 的原子性保障。

长事务 为什么 会导致 undo log 膨胀和复制延迟

长事务本身不直接写更多日志，但它会阻碍 purge thread 清理 undo log。只要有一个活跃事务的 read view 还需要访问某条旧版本记录，对应的 undo log 就不能被回收。

后果是：

• ibdata1 或 undo tablespace 持续增长，甚至占满磁盘
• 主库 binlog 无法被 purged（expire_logs_days 不生效），因为从库可能还在拉取旧事务的事件
• 从库应用 relay log 时，若遇到大事务（如全表 UPDATE），会单线程卡住，造成复制延迟飙升

排查方式：

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query  FROM information_schema.INNODB_TRX  ORDER BY trx_started LIMIT 5;

重点关注 trx_state = 'RUNNING' 且 trx_started 时间过久的记录。

真正危险的不是“事务运行时间长”，而是“事务持 有锁 + 持有 read view 时间长”。哪怕只是 SELECT 不加锁，只要没提交，也会拖住 purge。