怎么解决幻读问题

在业务中有这么一个场景,查询 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 的场景

代码案例,这是一个表

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。 



 3
 0
 分享
评论图片
评论