15-02 | 一个关于唯一索引的业务场景

一、抛砖引玉
业务上有这样的需求,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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值