怎么解决幻读问题

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