关于mysql 事务性的一点探究
事件起因
夺宝吧优惠券体系中要加行优惠券的预算控制。 预算控制的表结构如下
| 字段名 | 类型 | 解释 |
|---|---|---|
id |
int AI,PK | 自增id |
deliver_budget |
int | 分发预算 |
delivered_num |
int | 实际分发的值 |
因为优惠券的分发，涉及到几个表的修改。又需要控制预算， 一个比较直观的方法是在事务开始的时候select ... for update 锁住这一行, 之后在进行各种操作。 这肯定没有问题。但是代码太多了（主要是beego 的orm没有select for update的支持。 于是想到用update voucher set delivered_num = delivered_num + 1 where id = ? and deliver_budget > delivered_num 并检查update的数量来操作。但是对于mysql了解比较少， 不知道这样行不行。于是验证一下。
验证过程
开了两个session, 并start transaction 然后进行操作 ，过程如下
| Session1 | Session2 | 说明 |
|---|---|---|
1.start transaction |
1.start transaction |
|
2.检查原始值 select * from voucher where id=1; |
2.检查原始值 select * from voucher where id=1; |
初始值 deliver_budget=1 delivered_num=0 |
3.进行update update voucher set delivered_num = delivered_num + 1 where id = 1 and deliver_budget > delivered_num; |
Rows matched: 1 Changed: 1 Warnings: 0 update 成功 | |
4.重新select select * from voucher where id=1; |
update 确实生效 | |
5.检查select select * from voucher where id=1; |
这里的返回结果与初始值相同，可见mysql的默认事物级别Repeatable reads是有效的， 在事物中所有的读保持一致性。 |
|
6.update update voucher set delivered_num = delivered_num + 1 where id = 1 and deliver_budget > delivered_num; |
update操作wait for lock。这里就解决了我之前的移问 两个事物update同一行的时候会有锁机制。 而且这个锁并不是在update之后就即刻释放。 | |
| 7.commit | session2中的操作6完成。 进一步证明了在isolation level 为Repeatable reads下所有的锁是在事物结束时才进行。同时第6步返回的结果是 Rows matched: 0 Changed: 0 Warnings: 0。 这是我在这次实验中发现的最有趣的地方 从逻辑上来讲， select 的结果 delivered_num = 0 deliver_budget = 1 是会进行updat的但是mysql确没有。 这一块很令我费解于是有了第8步验证 |
|
8.select * from voucher where id=1 and deliver_budget > delivered_num; |
结果这时居然返回了一行。 看来 mysql的select和update是两个完全不同的逻辑 |
|
9.我想验证一下update直接+=1会是什么鬼 update voucher set delivered_num = delivered_num + 1 where id=1又出现了正确的但是逻辑很诡异的结果 |
出来的结果delivered_num 为2， 不是在这个事物中update之前delivered_num是0嘛，+1 之后成了2（mysql你他妈在逗我！） |
到此验证结束。 想一想mysql还蛮智能的，在事务中select 满足repeatable reads
而update不满足。这样就保持了数据的一致性。然而从程序的逻辑角度来讲还是蛮诡异的。
探究的过程
对于验证得到的结果，蛮超出我的想像的。 然后就google一通搜索。首先重新去了解了一下isolation 这个概念。isolation有几个级别： Serializable, Repeatable reads, Read committed, Read uncommitted 这几个概念之前也听说过，每次看完之后都觉得理解了，过会就忘。
mysql默认的隔离级别是repeatable reads, 也就是说在一个事物中，所有的读都是读的最开始select的snapshot。验证操作中的第5步也印证了这个问题。但是从程序的角度来讲，update操作不应该是先读到原来的值然后在更新一下么。按照这个逻辑 读的是snapshot update应该是基于这个snapshot进行的update操作才对。（但是从业务逻辑的角度来讲确实需要这样来实现一致性，不由得为mysql的设计点个赞。）
于是继续翻文档： http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html 在一个隐藏的角落看到这样一些话 The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following。
But 这是如何实现的呢? 简单的猜想一下觉得可能是通过MVCC来实现的
innodb为每一个我们看到的数据行存储了多个版本并标记每一个版本的创建版本号和删除版本号。 repeatable reads 的实现就是去在第一次读的时候找低于事务开始时的版本号的数据版本。 而按照这个推论，UPDATE就是永远基于最新版本的数据进行的操作了 这是一个猜测，没有在文档中找到。小心求证的过程就不做了。
总结
- 以后在处理并发问题的时候就可以放心的在事物中用update了。而不用去显示的
select for update锁住一行。 - 在事务操作中 select得到的数据不可信，不要基于select的结果去进行下一步操作。update尽量用一个原子操作去完成。