MySQL是Coder必备知识,最近我也是趁着自己有时间,看过一遍网络知识后,再回过头来看下MySQL,看MySQL的时候用到了Margin Note3,确实挺好用的,推荐学习时使用,电脑端使用book xnote。我会把思维导图丢到网页上来,也是方便我使用电脑查看。

MySQL学习推荐课程:MySQL 实战 45 讲


问题手记

1、事务隔离级别引申出来的可见性问题,若记录都未存在,无行锁,并发插入时无法按照预期进行更新;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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两个用户,如果互相关注,则成为好友。设计上是有两张表,一个是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锁层面有没有办法处理?

如图:

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

A、B之间没有关注关系(没有记录),事务开始时,查询为空。

对业务来说,双方已经互点关注了,已经不是喜欢了,而是需要在friend表中插入记录。

如图示中,在进行查询时,选择的数据加上行锁也没啥用,毕竟在查询的时候数据都还不存在。

首先,要给“like”表增加一个字段,比如叫作 relation_ship,并设为整型,取值1、2、3。

值是1的时候,表示user_id 关注 liker_id; 0b01
值是2的时候,表示liker_id 关注 user_id; 0b10
值是3的时候,表示互相关注。 0b11

然后,当 A关注B的时候,逻辑改成如下所示的样子:

应用代码里面,比较A和B的大小,如果A<B,就执行下面的逻辑

1
2
3
4
5
6
7
8
9
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,则执行下面的逻辑

1
2
3
4
5
6
7
8
9
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”表的时候,如果反向的关系已经存在,就会出现行锁冲突。

把正反向的数据都转化到一行记录里面,而不是分开(通过A、B UserID的大小)

然后,insert … on duplicate语句,确保了在事务内部,执行了这个SQL语句后,就强行占住了这个行锁,之后的select 判断relation_ship这个逻辑时就确保了是在行锁保护下的读操作。

事务在执行第一条语句时开启,开启后,碰到insert … on duplicate语句(先判断记录是否存在,存在即update,不存在即insert),修改语句,占用该行锁直到事务结束。(占不住则意味着要等待别人把该记录的行锁释放,也就是另外的事务结束)

操作符 “|” 是按位或,连同最后一句insert语句里的ignore,是为了保证重复调用时的幂等性。

insert ignore into,重复则忽略,不重复则插入。(重复时只是报警,不会报错)

这样,即使在双方“同时”执行关注操作,最终数据库里的结果,也是like表里面有一条关于A和B的记录,而且relation_ship的值是3, 并且friend表里面也有了A和B的这条记录。


这里的问题就是事务隔离引申出来的问题,“可见性”。当数据都还未出现呢,此时不管是当前读,还是快照读都得眼瞎。

解决此问题,依然还是用到了行锁,精妙的地方是,利用了insert … on duplicate语句,创建与更新同时存在,后面的查询语句去更新验证结果,由于两阶段锁协议,所以查询语句也是带锁的。