insert into***** on duplicate key update的使用-蒲公英云

insert into***** on duplicate key update的使用

悠悠 2022-04-10 03:26 271阅读 0赞

问题是这样的：

业务上有这样的需求，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 (
idint(11) NOT NULL AUTO_INCREMENT,<br>friend_1_idint(11) NOT NULL,<br>firned_2_idint(11) NOT NULL,<br> UNIQUE KEYuk_friend(friend_1_id,firned_2_id)<br> PRIMARY KEY (id`)
) ENGINE=InnoDB;

顺便说明一下，“like”是关键字，我一般不建议使用关键字作为库名、表名、字段名或索引名。

问题简述下，在并发场景下，同时有两个人，设置为关注对方，就可能导致无法成功加为朋友关系。

现在，我用时刻顺序表的形式，把这两个事务的执行语句列出来：

20181224154302455

图 1 并发“喜欢”逻辑操作顺序

由于一开始 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 的这条记录。