Mysql锁机制--并发事务带来的更新丢失问题

Mysql 系列文章主页

===============

刚开始学习 Mysql 锁的时候，觉得 Mysql 使用的是行锁，再加上其默认的可重复读的隔离级别，那就应该能够自动解决并发事务更新的问题。可事实上，并不能解决！

可是，为什么不能解决呢？

带着问题，自己写了个简单Demo，来测试并分析其中道理：

Demo 代码路径：https://github.com/cyhbyw/cyh_Spring_IsolationConcurrencyTransaction

Demo 工程名称：usingMybatis

0 准备工作

工程中所有文件如下图所示；

代码运行前，注意设置数据库的用户名和密码，同时，需要运行 test.sql 文件中的脚本用以初始化数据；

1 测试

1.1 业务代码

1     @Transactional(rollbackFor = RuntimeException.class)
2     public void increaseMoney(Integer id) {
3         Employee employee = employeeMapper.findById(id);
4         final Integer oldMoney = employee.getMoney();
5         LOGGER.info("oldMoney: {}", oldMoney);
6         employee.setMoney(oldMoney + 1);
7         employeeMapper.updateEmployee(employee);
8     }

代码说明：

第1行，添加事务
第3行，取出数据
第4，5行，取出旧的Money值并记录日志
第6行，为 Employee 对象设置新的值（就是+1操作）
第7行，执行Sql更新

1.2 测试代码

1     private void increaseMoney() {
2         int threadCount = 100;
3         while (threadCount-- > 0) {
4             new Thread(() -> employeeService.increaseMoney(1)).start();
5         }
6     }

代码说明：

启了 100 根线程去调用业务方法
每个线程将 Money+1，理论上，最后的Money=100（初始值为0）

1.3 测试结果

预期结果：100，实际结果：22

提示：22这个值不固定（因为是多线程访问嘛，而线程的执行是由CPU决定的，具有一定波动性）

1.4 程序日志

  1 2018-04-25 11:32:05,416  INFO [Thread-83] (EmployeeService.java:27) - oldMoney: 0
  2 2018-04-25 11:32:05,416  INFO [Thread-18] (EmployeeService.java:27) - oldMoney: 0
  3 2018-04-25 11:32:05,416  INFO [Thread-19] (EmployeeService.java:27) - oldMoney: 0
  4 2018-04-25 11:32:05,416  INFO [Thread-54] (EmployeeService.java:27) - oldMoney: 0
  5 2018-04-25 11:32:05,416  INFO [Thread-15] (EmployeeService.java:27) - oldMoney: 0
  6 2018-04-25 11:32:05,416  INFO [Thread-52] (EmployeeService.java:27) - oldMoney: 0
  7 2018-04-25 11:32:05,416  INFO [Thread-17] (EmployeeService.java:27) - oldMoney: 0
  8 2018-04-25 11:32:05,416  INFO [Thread-55] (EmployeeService.java:27) - oldMoney: 0
  9 2018-04-25 11:32:05,416  INFO [Thread-53] (EmployeeService.java:27) - oldMoney: 0
 10 2018-04-25 11:32:05,416  INFO [Thread-20] (EmployeeService.java:27) - oldMoney: 0
 11 2018-04-25 11:32:05,416  INFO [Thread-43] (EmployeeService.java:27) - oldMoney: 0
 12 2018-04-25 11:32:05,416  INFO [Thread-2] (EmployeeService.java:27) - oldMoney: 0
 13 2018-04-25 11:32:05,416  INFO [Thread-56] (EmployeeService.java:27) - oldMoney: 0
 14 2018-04-25 11:32:05,416  INFO [Thread-8] (EmployeeService.java:27) - oldMoney: 0
 15 2018-04-25 11:32:05,416  INFO [Thread-16] (EmployeeService.java:27) - oldMoney: 0
 16 2018-04-25 11:32:05,462  INFO [Thread-14] (EmployeeService.java:27) - oldMoney: 1
 17 2018-04-25 11:32:05,527  INFO [Thread-27] (EmployeeService.java:27) - oldMoney: 2
 18 2018-04-25 11:32:05,534  INFO [Thread-26] (EmployeeService.java:27) - oldMoney: 2
 19 2018-04-25 11:32:05,534  INFO [Thread-22] (EmployeeService.java:27) - oldMoney: 2
 20 2018-04-25 11:32:05,535  INFO [Thread-23] (EmployeeService.java:27) - oldMoney: 2
 21 2018-04-25 11:32:05,534  INFO [Thread-24] (EmployeeService.java:27) - oldMoney: 2
 22 2018-04-25 11:32:05,538  INFO [Thread-29] (EmployeeService.java:27) - oldMoney: 3
 23 2018-04-25 11:32:05,538  INFO [Thread-25] (EmployeeService.java:27) - oldMoney: 3
 24 2018-04-25 11:32:05,540  INFO [Thread-32] (EmployeeService.java:27) - oldMoney: 3
 25 2018-04-25 11:32:05,541  INFO [Thread-30] (EmployeeService.java:27) - oldMoney: 3
 26 2018-04-25 11:32:05,540  INFO [Thread-28] (EmployeeService.java:27) - oldMoney: 3
 27 2018-04-25 11:32:05,542  INFO [Thread-31] (EmployeeService.java:27) - oldMoney: 3
 28 2018-04-25 11:32:05,542  INFO [Thread-33] (EmployeeService.java:27) - oldMoney: 3
 29 2018-04-25 11:32:05,548  INFO [Thread-37] (EmployeeService.java:27) - oldMoney: 4
 30 2018-04-25 11:32:05,550  INFO [Thread-21] (EmployeeService.java:27) - oldMoney: 4
 31 2018-04-25 11:32:05,550  INFO [Thread-34] (EmployeeService.java:27) - oldMoney: 4
 32 2018-04-25 11:32:05,575  INFO [Thread-39] (EmployeeService.java:27) - oldMoney: 5
 33 2018-04-25 11:32:05,575  INFO [Thread-38] (EmployeeService.java:27) - oldMoney: 5
 34 2018-04-25 11:32:05,576  INFO [Thread-41] (EmployeeService.java:27) - oldMoney: 5
 35 2018-04-25 11:32:05,575  INFO [Thread-40] (EmployeeService.java:27) - oldMoney: 5
 36 2018-04-25 11:32:05,577  INFO [Thread-42] (EmployeeService.java:27) - oldMoney: 5
 37 2018-04-25 11:32:05,577  INFO [Thread-44] (EmployeeService.java:27) - oldMoney: 6
 38 2018-04-25 11:32:05,578  INFO [Thread-45] (EmployeeService.java:27) - oldMoney: 6
 39 2018-04-25 11:32:05,602  INFO [Thread-35] (EmployeeService.java:27) - oldMoney: 7
 40 2018-04-25 11:32:05,603  INFO [Thread-13] (EmployeeService.java:27) - oldMoney: 7
 41 2018-04-25 11:32:05,604  INFO [Thread-36] (EmployeeService.java:27) - oldMoney: 7
 42 2018-04-25 11:32:05,604  INFO [Thread-12] (EmployeeService.java:27) - oldMoney: 8
 43 2018-04-25 11:32:05,605  INFO [Thread-10] (EmployeeService.java:27) - oldMoney: 8
 44 2018-04-25 11:32:05,606  INFO [Thread-9] (EmployeeService.java:27) - oldMoney: 9
 45 2018-04-25 11:32:05,606  INFO [Thread-6] (EmployeeService.java:27) - oldMoney: 9
 46 2018-04-25 11:32:05,606  INFO [Thread-1] (EmployeeService.java:27) - oldMoney: 9
 47 2018-04-25 11:32:05,644  INFO [Thread-47] (EmployeeService.java:27) - oldMoney: 10
 48 2018-04-25 11:32:05,644  INFO [Thread-48] (EmployeeService.java:27) - oldMoney: 10
 49 2018-04-25 11:32:05,644  INFO [Thread-46] (EmployeeService.java:27) - oldMoney: 10
 50 2018-04-25 11:32:05,649  INFO [Thread-49] (EmployeeService.java:27) - oldMoney: 10
 51 2018-04-25 11:32:05,650  INFO [Thread-75] (EmployeeService.java:27) - oldMoney: 10
 52 2018-04-25 11:32:05,651  INFO [Thread-76] (EmployeeService.java:27) - oldMoney: 10
 53 2018-04-25 11:32:05,651  INFO [Thread-50] (EmployeeService.java:27) - oldMoney: 10
 54 2018-04-25 11:32:05,686  INFO [Thread-79] (EmployeeService.java:27) - oldMoney: 11
 55 2018-04-25 11:32:05,686  INFO [Thread-78] (EmployeeService.java:27) - oldMoney: 11
 56 2018-04-25 11:32:05,686  INFO [Thread-77] (EmployeeService.java:27) - oldMoney: 11
 57 2018-04-25 11:32:05,693  INFO [Thread-80] (EmployeeService.java:27) - oldMoney: 11
 58 2018-04-25 11:32:05,693  INFO [Thread-100] (EmployeeService.java:27) - oldMoney: 11
 59 2018-04-25 11:32:05,693  INFO [Thread-81] (EmployeeService.java:27) - oldMoney: 11
 60 2018-04-25 11:32:05,694  INFO [Thread-51] (EmployeeService.java:27) - oldMoney: 11
 61 2018-04-25 11:32:05,694  INFO [Thread-82] (EmployeeService.java:27) - oldMoney: 11
 62 2018-04-25 11:32:05,737  INFO [Thread-59] (EmployeeService.java:27) - oldMoney: 12
 63 2018-04-25 11:32:05,738  INFO [Thread-58] (EmployeeService.java:27) - oldMoney: 12
 64 2018-04-25 11:32:05,739  INFO [Thread-57] (EmployeeService.java:27) - oldMoney: 12
 65 2018-04-25 11:32:05,746  INFO [Thread-60] (EmployeeService.java:27) - oldMoney: 12
 66 2018-04-25 11:32:05,746  INFO [Thread-99] (EmployeeService.java:27) - oldMoney: 12
 67 2018-04-25 11:32:05,746  INFO [Thread-62] (EmployeeService.java:27) - oldMoney: 12
 68 2018-04-25 11:32:05,748  INFO [Thread-61] (EmployeeService.java:27) - oldMoney: 12
 69 2018-04-25 11:32:05,786  INFO [Thread-63] (EmployeeService.java:27) - oldMoney: 13
 70 2018-04-25 11:32:05,786  INFO [Thread-66] (EmployeeService.java:27) - oldMoney: 13
 71 2018-04-25 11:32:05,786  INFO [Thread-64] (EmployeeService.java:27) - oldMoney: 13
 72 2018-04-25 11:32:05,792  INFO [Thread-65] (EmployeeService.java:27) - oldMoney: 13
 73 2018-04-25 11:32:05,792  INFO [Thread-97] (EmployeeService.java:27) - oldMoney: 13
 74 2018-04-25 11:32:05,792  INFO [Thread-98] (EmployeeService.java:27) - oldMoney: 13
 75 2018-04-25 11:32:05,792  INFO [Thread-68] (EmployeeService.java:27) - oldMoney: 13
 76 2018-04-25 11:32:05,792  INFO [Thread-96] (EmployeeService.java:27) - oldMoney: 13
 77 2018-04-25 11:32:05,839  INFO [Thread-95] (EmployeeService.java:27) - oldMoney: 14
 78 2018-04-25 11:32:05,840  INFO [Thread-92] (EmployeeService.java:27) - oldMoney: 14
 79 2018-04-25 11:32:05,839  INFO [Thread-93] (EmployeeService.java:27) - oldMoney: 14
 80 2018-04-25 11:32:05,860  INFO [Thread-94] (EmployeeService.java:27) - oldMoney: 14
 81 2018-04-25 11:32:05,862  INFO [Thread-67] (EmployeeService.java:27) - oldMoney: 14
 82 2018-04-25 11:32:05,861  INFO [Thread-89] (EmployeeService.java:27) - oldMoney: 14
 83 2018-04-25 11:32:05,867  INFO [Thread-91] (EmployeeService.java:27) - oldMoney: 14
 84 2018-04-25 11:32:05,887  INFO [Thread-90] (EmployeeService.java:27) - oldMoney: 15
 85 2018-04-25 11:32:05,890  INFO [Thread-88] (EmployeeService.java:27) - oldMoney: 16
 86 2018-04-25 11:32:05,893  INFO [Thread-85] (EmployeeService.java:27) - oldMoney: 17
 87 2018-04-25 11:32:05,893  INFO [Thread-86] (EmployeeService.java:27) - oldMoney: 17
 88 2018-04-25 11:32:05,894  INFO [Thread-84] (EmployeeService.java:27) - oldMoney: 17
 89 2018-04-25 11:32:05,894  INFO [Thread-87] (EmployeeService.java:27) - oldMoney: 18
 90 2018-04-25 11:32:05,895  INFO [Thread-5] (EmployeeService.java:27) - oldMoney: 18
 91 2018-04-25 11:32:05,896  INFO [Thread-4] (EmployeeService.java:27) - oldMoney: 19
 92 2018-04-25 11:32:05,935  INFO [Thread-71] (EmployeeService.java:27) - oldMoney: 20
 93 2018-04-25 11:32:05,935  INFO [Thread-70] (EmployeeService.java:27) - oldMoney: 20
 94 2018-04-25 11:32:05,936  INFO [Thread-73] (EmployeeService.java:27) - oldMoney: 20
 95 2018-04-25 11:32:05,941  INFO [Thread-69] (EmployeeService.java:27) - oldMoney: 20
 96 2018-04-25 11:32:05,941  INFO [Thread-74] (EmployeeService.java:27) - oldMoney: 20
 97 2018-04-25 11:32:05,941  INFO [Thread-72] (EmployeeService.java:27) - oldMoney: 20
 98 2018-04-25 11:32:05,941  INFO [Thread-7] (EmployeeService.java:27) - oldMoney: 20
 99 2018-04-25 11:32:05,978  INFO [Thread-11] (EmployeeService.java:27) - oldMoney: 21
100 2018-04-25 11:32:05,978  INFO [Thread-3] (EmployeeService.java:27) - oldMoney: 21

日志分析：

日志中第1到15行，它们的 oldMoney 全部等于0
说明对应的15根线程在同一时间读到了相同的值0
这15根线程给Money设置的新值都是 0+1=1
这15根线程再分别执行Sql更新操作，期间可能会竞争Mysql的行锁

2 不能解决的原因分析

现在来分析一下为什么Mysql的行锁加上可重复读不能解决并发事务的更新问题

可重复读：表示的是在同一个事务内，所有的读取操作返回相同的数据，也就是不感知外部事务对数据的变更。假设事务A是当前事务，事务B是外部事务，事务A现在读取到 Money=10，接下来，事务B也读到值为10同时将其更新为11，但是对于事务A来说，它不能感知到外部事务对数据的变更，所以，事务A还是错误地认为当前 Money=10 并将其更新为11，于是，事务A覆盖了事务B的提交，从而造成更新丢失。

至于行锁，这只是用来保证并发事务更新时的先后顺序。比如，事务B在将Money=10更新为11时，如果正在这个更新期间（假设更新时间比较长）事务A也想做更新操作，则事务A将被阻塞，直到事务B更新完成后事务A才能更新，它与数据本身（事务A的、事务B的）没有任何关系。

由此可见，虽然 Mysql 是行锁且默认是可重复读，但是对于并发事务的更新操作，仍然会出现更新丢失问题，解决办法是——锁。

相关阅读:
《摄像头 —— MIPI CSI2简介》
《notepad++批量在任意行以及任意位置添加相同字符》
《摄像头 —— sensor移植以及调试》
条款18：让接口容易被正确使用，不易被误用
 条款20：宁以 passbyreferencetoconst 替换 passbyvalue
第13章运行时类型信息
 两个MATLAB函数dir2par和cplxcomp
业务连续性计划
 语音识别之——音频特征fbank与mfcc，代码实现与分析
 在logo上面实现类似闪电的动画效果
原文地址：https://www.cnblogs.com/cyhbyw/p/8869857.html