Mysql Repeatable-Read级别第二类丢失更新

操作步骤

新建数据库

  • 连接数据库: mysql -hlocalhost -P8889 -uroot -p123456
  • 新建数据库:create database tbschedule;

新建表

1
2
3
4
5
6
CREATE TABLE `trans` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(256) DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

测试第二类丢失更新

  • 分别启动两个客户端:
    Client1: 简写C1,mysql -hlocalhost -P8889 -uroot -p123456use tbschedule
    Client2: 简写C2, mysql -hlocalhost -P8889 -uroot -p123456use tbschedule

  • 查看事务隔离性级别:select @@TX_ISOLATION, 显示为可重复读。
    Alt text

  • C1开启事务: start transaction;

  • C1进行查询:select * from trans where id = 1;, 这是data字段为‘data1’。
    Alt text
  • C2开启事务: start transaction;
  • C2进行查询: select * from trans where id = 1;, 结果与C1查询的结果一致。
  • C2进行更新: update trans set data = 'data2' where id = 1;, 并提交事务commit
    Alt text

  • C2再次进行查询: select * from trans where id = 1;, data已经修改为’data2’.
    Alt text

  • C1再次进行查询: select * from trans where id = 1;, data还是’data1’, 说明C2已提交的修改确实没有影响到C1,C1是可以重复读的。
    Alt text
  • 新启动C3,并进行查询,和C2查询一致,说明针对data的修改对其他非C1客户端都生效了。
    Alt text
  • C1进行更新,update trans set data = 'data3' where id = 1;,并提交事务commit
  • C1,C2,C3进行查询:select * from trans where id = 1;,发现data已经都被改为’data3’了,C2的更新结果’data2’已经丢失。
    Alt text

解决方案

Mysql的Repeatable-Read级别并不能避免第二类丢失更新,需要在程序中判断处理。一般可以有如下两种方法:消息队列和加锁

消息队列

这种方法思想比较简单,就是使用消息队列把并发转成顺序操作,这样自然也不会有丢失更新的问题。但缺点是增加了消息队列,增加了程序的复杂度和运维的困难。

加锁

通常,根据对其他人修改数据的预期不同,可以分为乐观锁和悲观锁。

  • 悲观锁(Pessimistic Lock), 就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上排他锁,这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。

  • 乐观锁(Optimistic Lock), 就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下,即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。但如果经常产生冲突,上层应用会不断的进行retry,这样反倒是降低了性能,所以这种情况下用悲观锁就比较合适。

乐观锁解决问题方案

  1. 首先给第二类丢失更新的记录增加版本号
  2. 在对一条记录更新时添加版本号条件 id和version+1
  3. 如果更新记录条数为1,则更新成功;否则,更新失败,可以做业务回滚