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