mysql死锁：插入的同时修改其外键关联数据

Question

mysql在插入时会锁定外键关联的数据，这时候如果我想更新这条关联数据的部分字段该如何操作呢？
如同不停接收多个用户发来的消息并保存，此时可能会有修改用户信息的操作。
我有如下两个事务同时执行时出现死锁，不知道该如何解决这个问题。
必须使用添加版本号去实现乐观锁么？还是要去掉这个外键呀？

-- 事务1 添加A表数据，外键关联B表主键为a的数据
INSERT INTO `AAA` (`id`, `b_id`, `count`, `content`) VALUES ('1', 'a', 1, NULL);
-- 事务2 修改B表主键为a数据的部分字段
UPDATE `BBB` SET `name` = 'name_1', `score` = 57 WHERE `BBB`.`b_id` = 'a';

Answer 1

以下内容由CHATGPT及阿里嘎多学长共同生成、有用望采纳：

---

在MySQL中，死锁通常发生在两个或更多事务之间，每个事务都在等待其他事务释放资源。对于你描述的情况，问题在于事务1在插入记录到AAA表时，隐式地获取了对BBB表中b_id为'a'的记录的共享锁（因为外键约束），而事务2试图更新同一记录，需要排他锁。当两个事务试图以不同的顺序锁定相同的资源时，就会发生死锁。

解决这个问题通常有以下几种策略：

1. 事务顺序：确保所有事务按照相同的顺序处理资源。例如，你可以确保所有事务先更新BBB表，然后再插入AAA表。这样可以避免死锁，因为每个事务都会按照相同的顺序获取锁。

2. 锁表顺序：如果你无法控制事务的执行顺序，可以考虑显式地锁定表，先锁定BBB，再锁定AAA。但这可能会影响并发性能。

3. 乐观锁：不是必须的，但可以考虑使用乐观锁来减少锁竞争。你可以添加一个版本字段，每次更新时检查版本号是否与预期一致。如果在插入时版本号已改变，说明有其他事务进行了更新，事务可以重试。这需要你的应用层来处理冲突。

4. 死锁检测与回滚：MySQL本身就有一个内置的死锁检测机制，当检测到死锁时，会回滚其中一个事务以打破死锁。然后，你的应用程序需要捕获这个错误并决定如何重试或通知用户。

5. 减少锁定时间：尽可能快地完成事务，减少锁定资源的时间，可以降低死锁的概率。

6. 调整隔离级别：将事务隔离级别从REPEATABLE READ降低到READ COMMITTED可以减少死锁的可能性，但这也可能导致其他并发问题，如脏读。

在你的情况下，如果没有其他业务上的限制，调整事务的顺序可能是最简单的解决方案，即先更新BBB，再插入AAA。如果这样做仍然有死锁的风险，你可能需要进一步分析你的事务处理逻辑和并发模式，看看是否可以通过优化事务设计来避免死锁。