MySQL并发事务中已删除行仍被查询到的原因及与PG差异解析

测试环境与初始化数据 #

CREATE TABLE memberships (
    id SERIAL PRIMARY KEY,
    user_id INT,
    group_id INT
);

INSERT INTO memberships(user_id, group_id) 
    VALUES (1, 1), (2, 1), (1, 2), (2, 2);

并发事务时间线 #

T7查询结果 #

select * from memberships;
+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
|  1 |       1 |        1 |
|  2 |       2 |        1 |
|  3 |       1 |        2 |
|  4 |       2 |        2 |
+----+---------+----------+
4 rows in set (0.00 sec)

1. MySQL出现该问题的原因及与MVCC的交互 #

• 快照生成规则：MySQL可重复读隔离级别下，事务的快照在第一次执行查询语句时生成且固定不变。事务2在T3执行select时生成快照，此时事务1的delete尚未提交，快照中仍包含group_id=2的行，MySQL会判定事务2的启动时间早于事务1。
• 删除操作的MVCC处理：MySQL的delete是给行打删除标记并记录事务ID，而非物理删除。事务2在T6执行delete时，基于自身快照匹配行，此时事务1已提交，但事务2的快照仍能看到group_id=2的行。不过这些行已被事务1标记删除，事务2的delete实际未修改任何行，且MySQL不会触发冲突检测。
• 查询的快照一致性：事务2的所有查询都沿用T3生成的快照，即便执行了delete也不会更新快照。因此T7的查询会返回快照中存在的所有行，包括事务1已删除但事务2快照中仍可见的group_id=2行。

2. PostgreSQL无此问题的原因及与MVCC的交互 #

• 快照与冲突检测机制：PostgreSQL的可重复读隔离级别遵循严格快照隔离，会主动检测并发写冲突。事务2在T3生成快照后，事务1提交了对group_id=2行的删除。当事务2在T6执行delete时，PostgreSQL会检查目标行的最新版本。
• 删除操作的冲突处理：PostgreSQL执行delete时会尝试获取行锁，并对比行的最新事务ID与自身快照的事务ID范围。发现目标行已被快照外的提交事务修改后，会判定序列化冲突，直接抛出could not serialize access due to concurrent delete错误，终止事务2操作，避免不一致结果。
• MVCC版本管理：PostgreSQL每行版本都标记了事务ID范围，当事务尝试修改行时，若发现行已被快照外的提交事务修改，就会触发冲突报错，保证可重复读级别下的一致性。

内容的提问来源于stack exchange，提问作者Stack Underflow 11111