MySQL好友关系表加锁交换列值方案是否理论无死锁？

你的方案确实存在明确的死锁、数据一致性和性能问题，完全不建议上线用。所谓省一半存储空间的收益微乎其微，和引入的风险比完全得不偿失。

核心问题1：并发场景下必然出现数据不一致 #

你现在follow函数里的前两个查询都是普通快照读，没加for update锁，两个用户同时互相关注的时候100%会出脏数据：
比如A和B同时点击关注对方，两个事务同时开启：

• 事务T1（A关注B）：先查(A,B)不存在，再查(B,A)——因为T2还没提交，快照读看不到未提交数据，于是直接插入(A,B,0)
• 事务T2（B关注A）：先查(B,A)不存在，再查(A,B)——同样读不到T1的未提交插入，也直接插入(B,A,0)

最后两个事务都提交成功，表里会同时存在两条单向记录，is_both_way全是0，逻辑上已经是互关状态但系统识别不出来，直接导致私信权限判断错误，后续取关操作也只会处理其中一条，剩下的脏数据会永久留在表里。
哪怕你给所有查询都加上for update，也解决不了后面的问题。

核心问题2：交换字段的更新逻辑必然触发死锁 #

InnoDB的行锁是加在索引项上的，你的唯一键是(user_id1, user_id2)，当你执行update交换两个user_id的值时，需要同时操作两个索引项：删除旧的联合索引键，插入新的联合索引键，过程中需要先后获取两个索引项的锁。
死锁触发场景非常好复现：当A和B已经是互关状态（假设记录为(A,B,1)），此时并发执行两个操作：

• T1：A取关B，先拿到(A,B)的行锁，然后执行update要把记录改成(B,A,0)，申请(B,A)位置的插入意向锁
• T2：B同时取关A，先查(B,A)不存在，再查(A,B)申请锁，等待T1释放

只要并发量上来，很容易出现两个事务互相持有对方需要的锁、等待对方释放的情况，直接触发死锁，死锁报错会频繁出现。

核心问题3：查询性能完全不可用 #

你这个设计下，要查一个用户的关注列表，必须写：

select * from FriendRelation where user_id1 = #{userId} or (user_id2 = #{userId} and is_both_way = 1)

这个语句根本没法利用(user_id1, user_id2)联合索引的最左前缀，百万级数据量下就会走全表扫描，稍微有点流量直接把数据库打挂。查粉丝列表也有同样的问题。

建议直接用双写方案 #

你担心的两倍存储空间完全是过度焦虑：

• 百万级数据算下来，每行就算存3个bigint加1个tinyint，单条记录不到30字节，两百万条也就60MB左右，还不如一张普通高清照片大，存储成本可以忽略不计。

双写方案是工业界实现关注关系的标准方案，没有多余逻辑：

• 不需要is_both_way字段，A关注B就插(A,B)，B关注A就插(B,A)，取关就删对应方向的记录
• 查关注列表直接where user_id1 = #{userId}，查粉丝列表直接where user_id2 = #{userId}，查互关直接判断两个方向的记录是否都存在，全是等值查询，100%走索引，性能拉满
• 所有操作都只操作(self, target)这一条固定的记录，锁顺序完全一致，根本不会出现死锁，逻辑简单到几乎不可能写bug。

不要为了省可以忽略不计的成本，搞复杂的单表设计，后续线上出问题排查的成本，比那点存储成本高一万倍。

内容的提问来源于stack exchange，提问作者cr001