innodb中的事务的一则应用

最近做一个关于用户积分的功能，功能十分简单，增加和减少积分，减积分时需保证积分足够。

环境是虐心的php fastcgi，数据库是mysql innodb 5.1.68。

增加积分时使用sql ： update table set credit = credit + xxx where uid = xxjacob； 就可以了。

减少积分时需要先判断积分是否足够，则需要启动事务，进行如下操作：

1、得先select 出积分oldCredit

2、如果积分够，算出newCredit。update table set credit = credit - xxx where uid = xxjacob

 问题是，两个php进程在同一时刻，通过第1步取得的oldCredit是足够减的，但是只够减一次。可是他们都进行了第2步操作。这样可能产生负积分或者下溢等情况。

由于php的多进程模式，不能再应用层面控制并发。只能在数据库层面考虑了。

 

环境默认的事务隔离级别是Repeatable-Read。考虑将该部分事务的级别提高成Serializable，以期待这些操作串行执行。

可是进一步了解一下mysql的事务实现，是使用MVCC进行事务控制的实现，Serializable隔离级别并不是真正的序列化执行。

mysql文档 http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_serializable 中描述 Seriallizable:

“This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled”


参考 http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html.

"SELECT ... LOCK IN SHARE MODE 是在选择的row上加共享锁，不会block读，会block写。不仅不会避免上面的问题。而且会产生死锁。

比如这个业务场景：

tx0 read，加sharelock

tx1 read，获得sharelock

tx0 要update，被tx1阻塞，等待tx1释放sharelock。

tx1 也要update，被tx0阻塞，等待tx0释放sharelock。 产生死锁。

而 SELECT ... FOR UPDATE 会在读的row及相关索引上加独占锁（和执行update语句的效果一样）。会block 1、update这些rows。2、SELECT ... LOCK IN SHARE MODE。3、某个隔离级别事务中的读操作（其实就是Serializiable，因为默认是LOCK IN SHARE MODE）。(普通的读操作不会被阻塞)

 

经过测试，以上业务在第一步使用 select 。。。 for update 可以解决该问题。

 

也参考了 https://blogs.oracle.com/mysqlinnodb/ Repeatable Read Isolation Level in InnoDB