一、抛砖引玉
业务上有这样的需求,A、B 两个用户,如果互相关注,则成为好友。设计上是有两张表,一个是 like 表,一个是 friend 表,like 表有 user_id、liker_id 两个字段,我设置为复合唯一索引即 uk_user_id_liker_id。语句执行逻辑是这样的:
分析:
以 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 锁层面有没有办法处理?
二、解题
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,
`friend_2_id` int(11) NOT NULL,
UNIQUE KEY `uk_friend` (`friend_1_id`,`friend_2_id`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
PS:“like”是关键字,我一般不建议使用关键字作为库名、表名、字段名或索引名。
此时假如有高并发场景
说明:并发情况下friend 表里面是不会有记录的插入。第 1 步即使使用了排他锁也不行,因为记录不存在,行锁无法生效
解决方案:
首先,要给“like”表增加一个字段,比如叫作 relation_ship,并设为整型,取值 1、2、3。
值是 1 的时候,表示 user_id 关注 liker_id;值是 2 的时候,表示 liker_id 关注 user_id;值是 3 的时候,表示互相关注。
应用代码里面,比较 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”表的时候,如果反向的关系已经存在,就会出现行锁冲突。
下面的是重点!!!!!!
🏁:1.业务层面经常遇到的场景
首先创建一条记录,然后插入到数据库,如果数据库已经存在同一主键的记录,执行update操作;否则,执行insert操作。
这个操作可以在业务层做,也可以在数据库层面做。
- 业务层一般做法是先查询,如果不存在就插入,如果存在就更新;但是查询和插入不是原子性操作,在并发量比较高的时候,可能两个线程都查询不到某个记录,所以会执行两次插入,其中一条必然会因为唯一性约束冲突而失败。
- 数据库层mysql中INSERT … ON DUPLICATE KEY UPDATE就可以做这个事情,并且是原子性操作
参考文档:https://www.cnblogs.com/east7/p/11706729.html