mysql中数据库的ACID特性与事务完整性

mysql的ACID特性依赖InnoDB引擎、事务隔离级别、SQL类型及显式事务控制；COMMIT后数据丢失源于innodb_flush_log_at_trx_commit配置不当；select不加锁仍阻塞因MVCC与锁机制交互；存储过程内需显式异常处理才能保障原子性。

MySQL 中的 ACID 特性不是“开关式功能”，而是依赖存储引擎、事务隔离级别、SQL 语句类型和显式事务控制共同作用的结果；InnoDB 支持完整 ACID，MyISAM 完全不支持事务，因此谈 ACID 前必须确认 ENGINE=InnoDB。

为什么 COMMIT 后数据还是丢了？——ACID 的持久性（Durability）失效场景

ACID 中的 D（Durability）要求事务提交后，即使系统崩溃，数据也不应丢失。但在 MySQL 中它并非绝对可靠，关键取决于配置：

• innodb_flush_log_at_trx_commit = 0：日志每秒刷盘一次，崩溃可能丢失最多 1 秒事务
• innodb_flush_log_at_trx_commit = 1（默认）：每次 COMMIT 都强制刷盘，保证持久性，但性能略低
• innodb_flush_log_at_trx_commit = 2：日志写入 OS 缓存即返回成功，OS 崩溃仍可能丢数据
• 若使用 sync_binlog=0，主从复制场景下 binlog 未同步也可能破坏一致性

生产环境务必确认：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

值为 1，且磁盘本身开启写缓存（hdparm -I /dev/sdX 中查看 Write cache 是否 enabled）需谨慎评估。

SELECT 不加 for UPDATE 为什么也阻塞了？——隔离性（Isolation）与 MVCC 实现细节

InnoDB 默认使用 MVCC + Next-Key Lock 实现可重复读（REPEAtable READ），但“不加锁读”仅对快照读（普通 SELECT）成立；当前读（如 SELECT ... FOR UPDATE、UPDATE、delete）会加行锁或间隙锁。

• 执行 UPDATE t SET x=1 WHERE id=5; 时，即使 id 是主键，也会对匹配行加 X 锁，并可能锁住 (3,5) 和 (5,7) 这两个间隙（防止幻读）
• SELECT * FROM t WHERE name='alice'; 若 name 无索引，会触发全表扫描 + 所有聚簇索引记录的 S 锁（在 READ COMMITTED 下是记录锁，在 REPEATABLE READ 下可能是临键锁）
• 用 SELECT ... LOCK IN SHARE MODE 替代 FOR UPDATE 可降低冲突，但依然阻塞写操作

排查锁等待最直接方式：

SELECT * FROM information_schema.INNODB_TRX;

结合 INNODB_LOCK_WaiTS 和 INNODB_LOCKS（MySQL 8.0+ 已移除后者，改用 performance_schema.data_locks）。

事务中调用存储过程，回滚会生效吗？——原子性（Atomicity）的边界限制

ACID 的 A（Atomicity）指事务内所有操作要么全做，要么全不做。但 MySQL 对存储过程中的事务控制有明确限制：

• 存储过程内部不能使用 START TRANSACTION 或 COMMIT，否则报错 Error 1305 (42000): SAVEPOINT does not exist
• 若过程内发生 SQL 异常（如主键冲突），默认不会自动回滚已执行语句，除非显式声明 DECLARE EXIT HANDLER 并执行 ROLLBACK
• INSERT ... ON DUPLICATE KEY UPDATE 是原子语句，但其“插入失败→转更新”逻辑不触发事务级回滚，只影响单条语句行为
• DDL 操作（如 ALTER TABLE）在 MySQL 5.6+ 中隐式提交当前事务，无法被外层 ROLLBACK 撤销

示例：以下过程若第二条 INSERT 失败，第一条仍会留在表中：

DELIMITER $$ CREATE PROCEDURE insert_two() BEGIN   INSERT INTO t(a) VALUES (1);   INSERT INTO t(a) VALUES (1); -- 主键冲突 END$$ DELIMITER ;

必须加异常处理才能保障原子性。

ACID 不是数据库自动施加的魔法，而是你选择的引擎、写的 SQL、配的参数、启的事务共同决定的行为边界；最容易被忽略的是：隔离级别变更（如从 REPEATABLE READ 改成 READ COMMITTED）会静默改变锁行为和 MVCC 快照规则，而应用层往往毫无感知。