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

五、提问点

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

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

「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论