MYSQL的默认事务隔离级别REPEATABLE-READ也会出现数据更新丢失问题

    本文为多年前自己进行的测试试验： publish:April 13, 2017 -Thursday 现转移到CSDN同时带上原个人博客网站中的评论内容。

一、MYSQL的默认事务隔离级别REPEATABLE-READ也会出现数据更新丢失问题

    今天的这个试验，主要是在考虑MYSQL的默认事务隔离级别REPEATABLE-READ，我认为会导致数据更新丢失的问题，网上没有看到对这个问题的细致讨论，我觉得这个隔离级别允许其它的事务读取数据，那其它的事务在修改后很有要有可能被其它的事务覆盖。

    测试的mysql表结构SQL语句如下：

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(10) unsigned NOT NULL DEFAULT '20',
  `nowtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

1. 如现在的表数据内容：

2 join 5 2017-04-13 16:24:51
3 hello 200 2017-04-13 16:29:01
4 world 200 2017-04-13 16:29:03

    假设有两个事务A,B，事务A先进入并读取数据，事务B在A进入后再进入，事务A执行时间长于B，于是B先完成。执行语句如下：

2. 事务A执行语句：

set tx_isolation='Repeatable-read';
SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;
set autocommit=0;
START TRANSACTION;
	select sleep(4),user.* from user where id=4;
	update user set age=age-10 where id=4;
#ROLLBACK;
COMMIT;
show WARNINGS;

3. 事务B执行语句：

set tx_isolation='Repeatable-read';
SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;
set autocommit=0;
START TRANSACTION;
  select * from user  where id=4;
	update user set age=age-5 where id=4;
COMMIT;

    上面的执行语句A先开始，其读取到了ID为4的记录，值为200，并保存200这个值，这时事务B也读取这行记录，B将值减去5，事务B会先执行完成，数据表里这时的值应该是195，于是接下来再执行事务A，按理既然执行了两次减少，最后的值应该是185，但是执行结果是190。因为在事务A的整个过程中是可重复读的，即它读取到的值都是200，减掉10后，最后执行事务A,表里的数据就成了190.于是就丢掉了事务B的更新。打开MYSQL的全局请求日志：

set @@global.general_log='ON';
show variables like '%general_log%';

记录如下：

2017-04-13T00:00:00.016433Z        29 Query     SET PROFILING=1
2017-04-13T00:00:00.017099Z        29 Query     SHOW STATUS
2017-04-13T00:00:00.023720Z        29 Query     SHOW STATUS
2017-04-13T00:00:00.038987Z        29 Query     set autocommit=0
2017-04-13T00:00:00.050045Z        29 Query     START TRANSACTION
2017-04-13T00:00:00.064703Z        29 Query     select sleep(4),@age:=user.age from user where id=4
2017-04-13T00:00:00.246120Z        30 Query     SET PROFILING=1
2017-04-13T00:00:00.247028Z        30 Query     SHOW STATUS
2017-04-13T00:00:00.253363Z        30 Query     SHOW STATUS
2017-04-13T00:00:00.267244Z        30 Query     set autocommit=0
2017-04-13T00:00:00.276894Z        30 Query     START TRANSACTION
2017-04-13T00:00:00.289865Z        30 Query     update user set age=age-5 where id=4
2017-04-13T00:00:00.311861Z        30 Query     COMMIT
2017-04-13T00:00:00.349040Z        30 Query     SHOW STATUS
2017-04-13T00:00:00.355351Z        30 Query     SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
2017-04-13T00:00:00.357147Z        30 Query     SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.027241*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=792 GROUP BY STATE ORDER BY SEQ
2017-04-13T00:00:00.069960Z        29 Query     update user set age=@age-10 where id=4
2017-04-13T00:00:00.075049Z        29 Query     COMMIT
2017-04-13T00:00:00.082197Z        29 Query     SHOW STATUS
2017-04-13T00:00:00.088791Z        29 Query     SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
2017-04-13T00:00:00.090807Z        29 Query     SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.003476*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=821 GROUP BY STATE ORDER BY SEQ
2017-04-13T00:00:00.536710Z        28 Query     SELECT * FROM `user` LIMIT 0, 500
2017-04-13T00:00:00.537632Z        28 Query     SHOW COLUMNS FROM `test`.`user`

总结：所以事务隔离级别REPEATABLE-READ并不能解决冲突问题，解决的方法：

1，事务隔离级别设置为最高:Serializable
2, 非Serializable隔离级别中事务中使用排它锁for update.
3，使用乐观锁，但注意，不能在REPEATABLE-READ隔离级别的事务中使用乐观锁。因为它是可重复读的。

    最后，REPEATABLE-READ级别还会出现幻读的问题，这里不再细说。Serializable可解决幻读问题。

二、原文章评论内容转存如下 

Level : 1.    User：us20180112085813-321    Time：2018-01-12 09:15:22

经测试您描述的问题不能复现，Mysql5.5按您的描述测试，每次结果都是185，没毛病啊

Level : 2.    User：us20170221102720-985    Time：2018-01-12 17:36:59

[04007]你有漏掉逻辑没?好久以前作的测试了，这过了好久没事再去看这玩意头大。

Level : 3.    User：us20200114132654-347    Time：2020-01-14 14:25:31

请问，为啥不能在REPEATABLE-READ隔离级别的事务中使用乐观锁。因为它是可重复读的。对于这句话不太理解呢？

Level : 4.    User：us20200114132654-347    Time：2020-01-14 14:31:33

不能在REPEATABLE-READ隔离级别的事务中使用乐观锁。可以这样理解么？ 以实现version的乐观锁举例，因为在Repeatable-read级别下，所以每次在同一事务中，读取到的数据都是一样的，所以比较version是无意义的

Level : 5.    User：us20200114132654-347    Time：2020-01-14 14:43:00

但是在REPEATABLE-READ隔离级别下，事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放；这期间不是不允许其他事务修改数据么，那是不是就不需要加乐观锁了

Level : 6.    User：us20191109133510-568    Time：2020-01-16 16:39:39

[04007]你说的对

Level : 7.    User：us20200506161129-149    Time：2020-05-06 17:41:32

你这个确实在Mysql5.7版本下，不能复现。

Level : 8.    User：us20200508163739-905    Time：2020-05-08 16:48:36

在5.6.27也没有这个问题，个人认为是在做update操作的时候会获取当前最新的值（已commit）再来操作，因为update本身在数据库就是并行的。

Level : 9.    User：us20200710102113-684    Time：2020-07-10 13:55:12

mysql5.6.25 REPEATABLE-READ 测试无法复现。

Level : 10.    User：us20200725125742-575    Time：2020-07-25 13:27:49

没见过这个无法select sleep(4),user.* from user where id=4;

Level : 11.    User：us20200725125742-575    Time：2020-07-25 13:32:19

如果是sleep是设置睡眠时间，是select执行前睡眠还是执行后睡眠