业务需求
业务上有这样的需求,A、B两个用户,如果互相关注,则成为好友。设计上是有两张表,一个是like表,一个是friend表,like表有user_id、liker_id两个字段,我设置为复合唯一索引即uk_user_id_liker_id。
CREATE TABLE `like` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`liker_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;
CREATE TABLE `friend` (
id` int(11) NOT NULL AUTO_INCREMENT,
`friend_1_id` int(11) NOT NULL,
`firned_2_id` int(11) NOT NULL,
UNIQUE KEY `uk_friend` (`friend_1_id`,`firned_2_id`)
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
语句执行逻辑是这样的:
以A关注B为例:
第一步,先查询对方有没有关注自己(B有没有关注A)
select * from like where user_id = B and liker_id = A;
如果有,则成为好友
insert into friend;
没有,则只是单向关注关系
insert into like;
并发下产生的问题
但是如果A、B同时关注对方,会出现不会成为好友的情况。因为上面第1步,双方都没关注对方。第1步即使使用了排他锁也不行,因为记录不存在,行锁无法生效。请问这种情况,在MySQL锁层面有没有办法处理?
由于一开始A和B之间没有关注关系,所以两个事务里面的select语句查出来的结果都是空。
因此,session 1的逻辑就是“既然B没有关注A,那就只插入一个单向关注关系”。session 2也同样是这个逻辑。
这个结果对业务来说就是bug了。因为在业务设定里面,这两个逻辑都执行完成以后,是应该在friend表里面插入一行记录的。
如提问里面说的,“第1步即使使用了排他锁也不行,因为记录不存在,行锁无法生效”
解决方法
首先,要给“like”表增加一个字段,比如叫作 relation_ship,并设为整型,取值1、2、3。
值是1的时候,表示user_id 关注 liker_id;
值是2的时候,表示liker_id 关注 user_id;
值是3的时候,表示互相关注。
然后,当 A关注B的时候,逻辑改成如下所示的样子:
应用代码里面,比较A和B的大小,如果A<B,就执行下面的逻辑
mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;
/*代码中判断返回的 relation_ship,
如果是1,事务结束,执行 commit
如果是3,则执行下面这两个语句:
*/
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;
如果A>B,则执行下面的逻辑
mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;
/*代码中判断返回的 relation_ship,
如果是2,事务结束,执行 commit
如果是3,则执行下面这两个语句:
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;
这个设计里,让“like”表里的数据保证user_id < liker_id,这样不论是A关注B,还是B关注A,在操作“like”表的时候,如果反向的关系已经存在,就会出现行锁冲突。
然后,insert … on duplicate语句,确保了在事务内部,执行了这个SQL语句后,就强行占住了这个行锁,之后的select 判断relation_ship这个逻辑时就确保了是在行锁保护下的读操作。
操作符 “|” 是按位或,连同最后一句insert语句里的ignore,是为了保证重复调用时的幂等性。
这样,即使在双方“同时”执行关注操作,最终数据库里的结果,也是like表里面有一条关于A和B的记录,而且relation_ship的值是3, 并且friend表里面也有了A和B的这条记录。