关于快照隔离和幻影读的一些深入思考

       mysql的隔离级别有四种：read uncommitted，read committed，repeatable read ，serializable

       前面三种的区别 比较容易理解 。关键是第三种个第四种的区别 。

       前段时间微信公众号上看到的一个例子，大致是这么说的：

• • table credit上面记录了每个用户的消费记录，record1（小明，date1，消费5000元），record2（小明，data2，消费2000元）
  • 有一条隐式约束，即小明的消费记录加载一期不能超过小明的信用额度1万元：5000+2000<10000
  • 事务1，小明想消费了2000元，先去select sum（消费） from credit where user=小明；若sum（消费）< 10000，则允许小明此次消费2000元，向credit表中插入一条新的记录，否则交易失败；
  • 事务2跟事务1相同，也是消费2000元；
• 如果事务1跟事务2完全串行，那么事务2不会交易成功
• 如果事务1未提交之前，事务2开始，那么
  • 在read repeatable的隔离级别下，事务2也是可以执行的，因为事务2开始时，计算小明消费总额时，读取到的是credit表的快照，此时仍然是可以消费的；
  • 如果级别是serializable的隔离级别，事务1在计算消费总额度时，会针对查询的范围都加上范围读锁，事务2执行的时候，发现事务1已经自己要读取的记录，加了范围锁，那么事务2会阻塞等待事务1提交或回滚。

        对于范围读不加锁导致的不一致性现象，SQL标准中叫幻影读（   phantom read）。mysql的前三种隔离级别，采用的是基于MVCC的快照隔离技术。而对于顺序读，则按照严格的2PC协议实现。对于顺序读，是否就无法采用快照隔离的技术呢。答案是否定的，可以增加一些额外的技巧做workaround。例如读取一个范围数据时，将这些范围数据映射为一个写操作，这个在第二个事务也做同样的范围读时，就就可以检测到写冲突。

        这个问题另外一个规避手段，应用层规避，用一条单独的记录来存储用户的剩余信用额度，每次只读取一条记录做判断。在做数据库设计时，为了性能，采用Repeatable Read的隔离级别（数据库的全局配置参数）；为了一致性，尽量规避范围查询后做的写操作，而是把范围查询的聚合运算结果提前存到一个记录中。补充一点， mysql的RR隔离级别的MVCC实现是有些问题的，会导致write skew。简单的例子就是下面的语句update set count = count+1；如果是RR级别，并发高了，会出现lost update。oracle和pg不存在这个问题，pg的mvcc的rr实现，第二个事务提交前会检查写冲突。当然mysql可以通过自己的一个sql关键字规避这个问题，但是对于不熟悉的人，是个很大的坑，笔者就遇到过。

后记，实际上PG的serializable级别可能会存在write skew，PG的serializable实现了一种很特殊的serializable snapshot isolation，也会有些write skew。比如这么做：

1. R两条记录A.count和B.count，if A.count+ B.count >1 然后W A=A-1。
2. R两条记录A.count和B.count，if A.count+ B.count >1 然后W B=B-1。

出事条件是A.count=1且B.count=1;如果这两个事物同时执行，PG的serializable会允许两者同时提交，PG的SSI机制会认为事物1修改A和事务2修改B没有冲突。显然这违背了业务的约束，产生了write skew。看来，只有严格的2PL协议，才能保证不会出现write skew和read skew的现象。

参考

https://en.wikipedia.org/wiki/Snapshot_isolation

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

http://www.evanjones.ca/db-isolation-semantics.html

补充，一些容易混淆的技术概念：

• 对于快照隔离，一般认为是一种隔离的级别，笔者更倾向于理解为隔离的技术，在此基础上可以实现不同的隔离级别，例如PG中对于serializable的实现，就是对SI隔离的升级为serializable snapshot isolation；
• SQL92事务隔离级别的定义是基于悲观锁的思想，例如教科书上的三级封锁协议的定义；商用数据库的实现过程中，多采用基于乐观锁的MVCC技术；商用DBMS事务隔离级别的定义，跟SQL92的标准是有所差别的。
• 不同的DBMS对于事务隔离级别的定义和实现相差非常大；例如DB2中最高隔离级别repeatable read，相当于mysql的serializable；DB2的cursor stability（锁住select查询游标指向的当前记录）在mysql并没有定义；

https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0004121.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

http://it.dataguru.cn/article-8406-1.html?hmsr=toutiao.io&utm_medium=toutiao.io&utm_source=toutiao.io