解决并发情况下库存减为负数问题--update2016.04.24

场景：

一个商品有库存，下单时先检查库存，如果>0，把库存-1然后下单，如果<=0，则不能下单，事务包含两条sql语句：

select quantity from products WHERE id=3;
update products set quantity = ($quantity-1) WHERE id=3;

在并发情况下，可能会把库存减为负数(两个进程同时select出来的都>0,然后都会执行update)，怎么办呢？

方法1：

InnoDB支持通过特定的语句进行显示加锁：

select...lock in share mode

select...for udpate

select quantity from products WHERE id=3 for update;
update products set quantity = ($quantity-1) WHERE id=3;

但是执行for update会产生一些其他的影响

1.select语句变慢

2.一些优化无法正常使用,例如索引覆盖扫描

3.很容易造成服务器的锁争用问题

方法二：

把udpate语句写在前边，先把数量-1，之后select出库存如果>-1就commit,否则rollback。

update products set quantity = quantity-1 WHERE id=3;
select quantity from products WHERE id=3 for update;

上边的事务中先执行了update，所以id=3的行被加了行锁,只有commit/rollback是才会被释放（事务中锁是逐步获得的，但是都是commit时所释放的）。很好的解决了并发问题。

方法三：

update语句在更新的同时加上一个条件

$quantity = select quantity from products WHERE id=3;
update products set quantity = ($quantity-1) WHERE id=3 and queantity = $quantity;

这样虽然select语句没有加锁，但是因为mysql的事务隔离级别是可重复读，所以其他事务的修改不会影响到select的结果，当执行到update时，如果有其他事务锁住了这条记录，update会等待，等到其他事务释放锁，update会执行，但此时如果quantity的数量已经被修改，update的执行会返回影响行数为0。

原因：

场景比如先select出来的$quantity=3，然后执行update的条件为id = 3 and quantity=3，执行更新返回影响函数为0，但再次执行select时发现id=3的记录quantity确实是3啊，怎么有这条记录却更新不了呢？

这”归功于“mysql的事物隔离级别和MVCC，当第一次select时$quantity=3，然后其他事务先于这个update执行了，导致update的条件并没有找到合适的记录，因为在可重复读级别中，update的读是“当前读”，读取的是最新的数据。而再次select时查到的$quantity还是等于3，因为对于select的读是“快照读”，读取的是历史数据，这也是可重复读级别的特性。

注意方法二中：当一个事务提交后如果库存正好是0，此时另一个事务读取到的还是1，这就是可重复读，但是由于先执行的update，update执行完之后的库存就是-1，此时再执行select的时候的结果就是-1，所以可重复读是指当前事务没有写语句的时候，如果写语句发生则是建立在其他事务提交后的结果基础上的。

详细的Innodb中的事务隔离级别可以看美团点评技术团队的一篇文章，有对于MVCC和间隙锁的解释：http://tech.meituan.com/innodb-lock.html