最近做一个关于用户积分的功能,功能十分简单,增加和减少积分,减积分时需保证积分足够。
环境是虐心的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