一、说明
最近碰到一个比较有意思的事情,想要和大家分享一下。
-
问题:
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
五、提问点
- 以上就是本文主要内容,有不正确或者有异议的地方,请在下方评论区说明。
重点来了:你们觉得那种方式更好,更优。开动脑筋思考下这两种解决方式都有哪些利弊?