for update 死锁场景

解决幻读的问题

在业务中有这么一个场景,查询id 为 30 的记录,如果存在更新,不存在就插入

正常逻辑代码

事物F1


result =  select * from rt_shop where id = 30;
if(null == result){
    inset into rt_shop set column_name1 = value1, column_name2 = value2，…;
 }else{
    update rt_shop set column_name1 = value1, column_name2 = value2，…;
 }

事物F2

delete from rt_shop where id  = 30;

存在的问题

F1 中因为查询(DQL) 和操作(DML) 是两个分开的操作,因为DQL 默认是不加任何锁的, DQL 成功后 ,其他事物F2 依然可以对这个记录进行 DML, 所以当F2 删除了这个记录,那么F1 进行更新的时候就会报错!

这个是时候就产生了幻读,之前明明是有的,那么现在更新的时候就没了,所以就操作失误了;那么如何解决这个幻读问题呢?一般来讲解决幻读都是采用 next-key(范围锁+行锁)

使用next-key解决幻读的问题

本次需要处理的场景是查询id 为 30 的记录,如果有的话就更新,没有的话就新增

没有id 为 30 的场景

代码案例 ,这是一个表

CREATE TABLE `rt_shop` (
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '店铺名称', 
 `recruiter_id` int(11) NOT NULL COMMENT '顾问ID',
 `image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '店铺照片', `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 0 否 1是', 
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), 
 KEY `idx.recruiter_id` (`recruiter_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COMMENT='店铺表';

数据

id name   recruiter_id   image  is_deleted create_time    update_time
29 宫本无错   1  xx 0  2021-05-31 17:50:29    2021-06-03 15:54:58
31 李的店铺   4  https://st1-d172YjKq1eTNWhLj.png   0  2021-05-31 17:50:29    2021-06-16 17:58:06

业务代码代码解释

事务T1


start transaction;

select * from rt_shop where id  = 30 for update;

INSERT INTO `rt_shop` (`id`, `name`, `recruiter_id`, `image`, `is_deleted`, `create_time`, `update_time`)
VALUES
 (30, '慢跑的店铺', 3, 'https://thTLZ11nhOvy52MMy6uJOia45oIdt6qbriafmI10wLElNbg5DGVXTuCNQzezuaQvjMYVib74TSLtu0oC2Q/132', 0, '2021-05-31 17:50:29', '2021-05-31 17:50:29');
commit

事务T2

start transaction;
select * from rt_shop where id  = 30 for update;

INSERT INTO `rt_shop` (`id`, `name`, `recruiter_id`, `image`, `is_deleted`, `create_time`, `update_time`)
VALUES
 (30, '慢跑的店铺', 3, 'https://5oIdt6qbriafmI10wLElNbg5DGVXTuCNQzezuaQvjMYVib74TSLtu0oC2Q/132', 0, '2021-05-31 17:50:29', '2021-05-31 17:50:29');
commit

如果并发场景下,先执行了事物A,如果有id是30 的数据,那么就会加行锁,属于独占锁,T2的for update 就无法加锁,会阻塞串行,不会发生死锁

如果没有 id为 30的数据,那么就会加一个范围锁 ,范围锁的范围就是 {29,31},因为范围锁不是独占锁,所以T2也可以进行加锁;

这个时候当事物 T1 想添加一个id 为 30的数据,因为 T2 已经加了范围锁,所以插入的时候检测到有一个T2的锁,所以就等待T2释放锁,所以会一直阻塞

T2 往下执行的时候也有这个问题,所以都在等待对方,就发生了死锁的问题!,会返回如下代码

Deadlock found when trying to get lock; try restarting transaction

所以范围锁也不能完全解决问题,需要有一个新的思路

解决方案

解决命令

insert into test (a,b) values (1,2) on duplicate key update b = b + 1;

mysql “ON DUPLICATE KEY UPDATE” 语法

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。

INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
相当于下面
UPDATE TABLE SET c=c+1 WHERE a=1;

如果行作为新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2。