Mysql 保持逻辑外键完整性
Mysql 保持逻辑外键完整性
1. 环境与前提
MySQL 默认隔离级别是 REPEATABLE READ（对 InnoDB 而言）。
user表中有主键userid，并且有一个ver字段（用于乐观锁）。msg表中有外键userid（或者逻辑上依赖 user 存在）。你希望保证不会插入一条
msg，其对应的userid在user表中不存在。💡注意：user表的userid， ver; msg 表的 userid 都要建立索引，防止锁定范围过大。
💡注意：死锁分析：
结论：会产生死锁，但不会长时间阻塞（毫秒级解决「mysql自动判断并牺牲一个事务」）。高频死锁导致的性能问题和部分请求失败「正常处理即可」。
2. 操作 A（删除人员）的步骤
操作 A 的伪代码（我按照你的逻辑转成 SQL 事务）：
SELECT @ver := ver FROM user WHERE userid = 1;
SELECT @count := COUNT(*) FROM msg WHERE userid = 1;
IF (@count = 0) THEN
-- 此处也可以是 update set ver = ver + 1 from user where userid = 1;
DELETE FROM user WHERE ver = @ver AND userid = 1;
END IF;
（注意：实际上 MySQL 中 IF 只能用在存储过程或程序代码中，这里假设是程序逻辑分步执行，但整体在一个事务里。）
其实你给的原始 A 操作可能是在程序里判断，如果 @count = 0 就执行 delete，否则不删。
假设 A 的执行时序是：
1. 查询 user 中 userid=1 的 ver。
2. 查询 msg 中 userid=1 的消息数量。
3. 如果消息数为 0，则用 ver 做条件删除该用户（乐观锁，防止中间被修改）。
3. 操作 B（插入消息）的步骤
操作 B 伪代码（事务内执行）：
START TRANSACTION;
SELECT @c := COUNT(*), @ver := ver FROM user WHERE userid = 1;
IF (@c = 0) THEN
ROLLBACK;
ELSE
INSERT INTO msg(userid, message) VALUES (1, 'a');
-- 这个update语句有两个目的：
-- 1. 使用update 防止重复读「读取的是当前数据」， 使用 更新行数来检查 user 数据是否还存在
-- 2. 更新ver的版本号，防止在 msg使用 user 数据后，user 数据被删除或更新
UPDATE user SET ver = ver + 1 WHERE userid = 1 AND ver = @ver; -- 乐观锁检查
IF (ROW_COUNT() = 0) THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END IF;逻辑是：
1. 检查用户是否存在，不存在就回滚。
2. 存在的话插入一条消息。
3. 更新 user 的 ver（这里 ver = ver + 1 改变ver的值，只是用 WHERE ver = @ver 检查是否该行还是之前读到的版本），如果更新行数为 0，说明用户被其他人修改或删除了，就回滚整个事务。
4. 并发问题分析
我们要防的是：B 检查用户存在，然后 A 删除用户，B 再插入消息成功，导致消息对应的用户不存在。
场景模拟（时序）：
B 开始事务（RR 隔离级别），执行
SELECT ... FROM user WHERE userid = 1，读到用户存在@c=1，记下@ver。A 开始事务（假设在 B 之后开始，但 A 的事务可能在 B 提交之前提交），执行
SELECT ver FROM user WHERE userid=1（会读到最新的提交版本，因为 RR 下每次单独查询看到的是当前读？这里注意，如果 A 只是普通 SELECT，RR 下第一次读会取快照，但 DELETE 是当前读，等一下分析）。
更精确地：
A 的
SELECT @ver := ver FROM user WHERE userid=1在 RR 下会取**快照读**（事务开始时的数据），如果 A 开始晚于 B 开始，但 B 还没提交，那么 A 看到的是 B 开始前的数据，用户存在。接着 A 的
SELECT COUNT(*) FROM msg WHERE userid=1也是快照读，看到 B 插入前的消息数（假设为 0）。然后 A 执行
DELETE FROM user WHERE ver=@ver AND userid=1，这是一个当前读（会看到最新的已提交数据，并加锁）。
如果 B 还没提交，那 B 对用户行有锁（因为 B 的 UPDATE 在后面才做，但此时 B 还没执行到 UPDATE，所以用户行还没有被 B 加锁？等等，B 只做了 SELECT，在 RR 下 SELECT 快照读不加锁，所以用户行此时没有锁，A 可以删除）。
但这里 A 的 DELETE 条件 ver=@ver 中的 @ver 是快照读到的版本，如果 B 更新了 ver 提交了，那么 A 就删不掉。
不过我们要考虑的 race 是：**B 的 SELECT 读到用户存在，之后 A 删除了用户并提交，然后 B 插入消息、更新用户检查失败则回滚**。
这样不会出现消息对应不存在的用户，因为 B 的更新用户行失败会导致回滚，消息插入也会被撤销。
问题点
如果 B 的 UPDATE user SET ver=ver + 1 WHERE userid=1 AND ver=@ver 是在 A 删除**并提交**之后执行，那么 WHERE ver=@ver 会失败（因为该行已被删除，不存在了）ROW_COUNT=0，B 会回滚，所以 B 插入的消息不会最终提交。
所以乐观锁保证了：只要 A 在 B 检查用户存在之后删除了用户，B 最终会回滚。
5. 可能的漏洞？
如果 B 的事务中SELECT 和 INSERT 之间，用户被 A 删除并提交，此时 B 的 INSERT 依然能执行（因为没有外键约束或未即时检查），然后 B 执行 UPDATE user 发现行不存在，回滚。
这已经安全。
但是，有一种情况：如果 B 执行 UPDATE user 时WHERE ver=@ver 还能匹配到行吗？
不行，因为行已被删除，所以回滚。
6. 特殊情况：B 的 SELECT 是快照读，A 在 B 开始之后删除并提交，B 的 SELECT 仍然看到用户存在吗？
是的，RR 下，B 事务内第一次读 user 是快照读，即使 A 删除用户并提交，B 仍然看到那行存在（因为 MVCC）。
所以 B 会进入 else 分支插入消息，然后 UPDATE 时发现当前读不到该行（或者读到的是新插入的不同 ver？不，行已删），所以 ROW_COUNT=0，回滚。
结论：**不会出现脏数据**。
7. 如果 B 的 UPDATE 条件改为 userid=1（去掉 ver 检查）？
如果不检查 ver，只检查用户 id 是否存在：
UPDATE user SET ver=ver WHERE userid=1，如果该行已被删除，则 ROW_COUNT=0，仍会回滚。
所以乐观锁的 ver 检查在这里不是必须的，只要更新用户行的任意字段并用用户 id 作为条件，就能检测该行是否还存在。
实际上这里的乐观锁主要是防止用户行被**修改**（例如 ver 变了），而不是被删除。但删除也会导致更新行数 0，所以也能防止。
8. 边缘情况：B 在插入消息后，UPDATE 前，A 删除用户，但 A 的删除条件 ver=@ver 不成立（因为 B 更新了 ver），所以 A 没删成，B 更新成功提交。
这种情况用户仍然存在，消息插入没问题，不是我们要防止的异常。我们要防止的是“消息对应不存在的用户”，这种情况用户存在，所以没问题。
9. 总结
在 MySQL 默认的 REPEATABLE READ 隔离级别下，你给出的逻辑（包含乐观锁检查）可以保证不会插入一条其用户不存在的消息。
因为：
1. B 事务会先检查用户存在（快照读）。
2. 插入消息后，会用 UPDATE + WHERE 当前读检查用户行是否还存在且未被修改。
3. 如果用户行在 B 检查之后被其他事务删除，B 的 UPDATE 会返回 0 行更新，导致 B 回滚，消息不会真正插入数据库。
4. RR 隔离级别保证了 B 事务内的快照读一致性，而 UPDATE 的当前读能检测到并发修改，从而回滚。
所以并发控制是安全的。