mysql 最大值7F_关于mysql的一些额外的了解

1.本文介绍：处理用户重复点击引发事务幻读的问题

2.场景： service逻辑(获取远端接口数据保存于本地临时表，首先需要删除表中数据，然后批量添加数据(跟数据量无关)，最后分页查询返回给界面)

3.实验结果：

关闭自动提交，数据库默认引擎innodb，事务默认隔离级别 可重复读(不会出现脏读，不可重复读(两次结果不一样)，会出现幻读)

操作语句：

1 select count(*) fromsys_person_consumer_order_temp;2 delete fromsys_person_consumer_order_temp;3 INSERT INTO `sys_person_consumer_order_temp`(`order_no`,`name`,`work_code`,`card_no`,`trade_amount`,`status`,`trade_date`,`merch_no`,`merch_name`,`remark`,`update_time`) VALUES ('9000024520190520180557208038', '嘉联测试卡B', '900087', '570579026', 100, 'S', '20190520180557', '84961015943A000', '诚意金测试---勿动', '记账成功', '20190520180557');4 INSERT INTO `sys_person_consumer_order_temp`(`order_no`,`name`,`work_code`,`card_no`,`trade_amount`,`status`,`trade_date`,`merch_no`,`merch_name`,`remark`,`update_time`) VALUES ('9000024520190520180430873158', '嘉联测试卡B', '900087', '570579026', 100, 'S', '20190520180430', '84961015943A000', '诚意金测试---勿动', '记账成功', '20190520180431');5 select count(*) from sys_person_consumer_order_temp ;

1 DROP TABLE IF EXISTS`sys_person_consumer_order_temp`;2 CREATE TABLE`sys_person_consumer_order_temp` (3 `id` int(11) UNSIGNED ZEROFILL NOT NULLAUTO_INCREMENT,4 `order_no` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',5 `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,6 `work_code` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工工号',7 `card_no` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '会员卡号',8 `trade_amount` int(15) UNSIGNED DEFAULT NULL COMMENT '交易金额',9 `status` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,10 `trade_date` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '交易日期',11 `merch_no` char(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',12 `merch_name` char(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,13 `remark` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',14 `update_time` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,15 PRIMARY KEY(`id`) USING BTREE,16 INDEX`orderno`(`order_no`) USING BTREE17 ) ENGINE = InnoDB AUTO_INCREMENT = 604788 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

多次查询，结果不一致

解决思路：

1.引发死锁(开始清空数据库，因为是临时表开始无所谓数据) service查询后再去清空数据

1 select count(*) fromsys_person_consumer_order_temp;2 INSERT INTO `sys_person_consumer_order_temp`(`order_no`,`name`,`work_code`,`card_no`,`trade_amount`,`status`,`trade_date`,`merch_no`,`merch_name`,`remark`,`update_time`) VALUES ('9000024520190520180557208038', '嘉联测试卡B', '900087', '570579026', 100, 'S', '20190520180557', '84961015943A000', '诚意金测试---勿动', '记账成功', '20190520180557');3 INSERT INTO `sys_person_consumer_order_temp`(`order_no`,`name`,`work_code`,`card_no`,`trade_amount`,`status`,`trade_date`,`merch_no`,`merch_name`,`remark`,`update_time`) VALUES ('9000024520190520180430873158', '嘉联测试卡B', '900087', '570579026', 100, 'S', '20190520180430', '84961015943A000', '诚意金测试---勿动', '记账成功', '20190520180431');4 select count(*) fromsys_person_consumer_order_temp ;5 delete from sys_person_consumer_order_temp;(第一个事务不提交，第二个事务会阻塞)

第二种思路：

不修改service逻辑

关于幻读跟不可重复度的区别

https://www.cnblogs.com/itcomputer/articles/5133254.html

https://www.cnblogs.com/JohnABC/p/3521061.html

https://blog.csdn.net/qq_33591903/article/details/81672260

A事务未提交，B事务读取咯未提交的数据        脏读 (注意啊，如果B事务也去更新A事务更新的那条数据，A事务没有提交那条数据，处于阻塞状态)

会有行锁，操作不同的数据没问题

针对 select 然后 update 但是 对于 insert 没有作用(幻读)

可重复读利用历史快照去解决数据读取到咯不一样的问题