MySQL insert on update锁冲突案例

一、说明

最近碰到一个比较有意思的事情，想要和大家分享一下。

问题：
- insert into XXXX on duplicate key update XXX
  - 这种语句是否建议使用，可能会导致什么问题？
  - 如果因为这种语句产生了问题，应该如何解决？
环境信息：
- insert into temp(u_month, uid, u_revenue) values('2020-11-01',11,100),('2020-11-01',12,100) on duplicate key update u_revenue = u_revenue + 1;

二、先决条件

create table temp(
 id int(11) not null auto_increment comment '自增主键',
 u_month date not null comment '每月的第一天',
 uid int(11) not null comment '用户id',
 u_revenue bigint(11) not null default '0' comment '收入金额',
 primary key (id),
 unique key uid_month_idx (u_month, uid) using btree
) engine=innodb default charset=utf8;

insert into temp(u_month, uid, u_revenue ) values('2020-11-01',11,100),('2020-11-01',12,100),('2020-11-01',13,100),('2020-11-01',14,100);

mysql> select * from temp;
+----+------------+-----+-----------+
| id | u_month    | uid | u_revenue |
+----+------------+-----+-----------+
|  1 | 2020-11-01 |  11 |       100 |
|  2 | 2020-11-01 |  12 |       100 |
|  3 | 2020-11-01 |  13 |       100 |
|  4 | 2020-11-01 |  14 |       100 |
+----+------------+-----+-----------+
4 rows in set (0.00 sec)

三、问题复现

事务一
- 不要提交事务

begin;
insert into  temp(u_month, uid, u_revenue)
values('2020-11-01',12,1) on duplicate key update u_revenue = u_revenue + 1;

事务二

begin;
insert into  temp(u_month, uid, u_revenue)
values('2020-12-01',15,1) on duplicate key update u_revenue = u_revenue + 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

注意：事务二的 insert 是真的会在最后一行插入数据，因为 '2020-12-01',15 并不存在。

但比较尴尬的是明明 事务一 在 insert 的时候会因为有唯一约束加 记录锁 S，但并不能够影响 事务二 在最后一行 insert 数据啊

在阻塞的时候查看 innodb 引擎状态，捞出来 事务二 锁阻塞信息

---TRANSACTION 5759, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1116, OS thread handle 139737704658688, query id 5381 localhost root update
insert into  temp(u_month, uid, u_revenue)
values('2020-12-01',15,1) on duplicate key update u_revenue = u_revenue + 1
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 146 page no 3 n bits 72 index PRIMARY of table `testdb`.`temp` trx id 5759 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

注意：

事务二是想要加 插入意向锁 被阻塞了，好像是被间隙锁 supremum 高位数据无穷大范围。

事务一因为有数据冲突，将 insert 转为 update 执行，不过在 RR 隔离级别下为了防止幻读，会对 insert 失败的位置加 间隙锁

四、解决方式

4.1 语句拆分

判断要插入的数据是否已经存在（使用 unique key 唯一键作为 where 条件）

mysql> select count(1) from temp where u_month = '2020-11-01' and uid = 12;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

如果返回 1，那就代表这条数据已经存在了，要进行 update 操作

update temp set u_revenue = u_revenue + 1 where u_month = '2020-11-01' and uid = 12;

如果返回 0，代表这条数据不存在，要进行 insert 操作

insert into  temp(u_month, uid, u_revenue) values('2020-11-01',12,1);

应用程序拆分语句时的逻辑代码可以参考下方 SP存储引擎

drop procedure test;

delimiter //
create procedure test(in v_date datetime, in v_id int, in v_revenue int, out v_status int) comment "测试测试"

begin
    -- declare v_status int default 0;
    select count(1) into v_status from temp where u_month  = v_date  and uid = v_id;
    if v_status = 1 then
        update temp set u_revenue  = u_revenue + v_revenue where u_month  = v_date and uid = v_id;
    else
        insert into temp(u_month, uid, u_revenue) values(v_date,v_id,v_revenue);
    end if;
commit;

end //

delimiter ;

call test('2020-11-01', 15, 100, @a);

select @a;

4.2 唯一键提升为主键

删除原有自增 ID 主键
删除原有唯一键
提升原有唯一键的两列为主键

mysql> alter table temp drop column id, drop primary key, add primary key (u_month,uid), drop index uid_month_idx;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

五、提问点

以上就是本文主要内容，有不正确或者有异议的地方，请在下方评论区说明。

重点来了：你们觉得那种方式更好，更优。开动脑筋思考下这两种解决方式都有哪些利弊？