讨论事务的隔离性下，商品库存超买问题

转自http://blog.csdn.net/unix21/article/details/52852342

事务的隔离机制是指：

Read Uncommitted（读取未提交内容）
Read Committed（读取提交内容）
Repeatable Read（可重读）
Serializable（可串行化）

具体的解释最经典的MySQL书《高性能MySQL(第3版)》已经有了就不在其他地方再引用了：

隔离机制的比较

其实也有人喜欢用锁来控制并发，书中还提到了“隐式”和“显示锁定”，是这么建议的：

虽然这样，但是其实如果不经过实际的演练还是很难理解上面说的事务隔离机制到底怎么样可以防止并发。

1.查看MySQL版本

我们的版本是5.1.7

2.查看存储引擎

>show engines;

存储引擎是：InnoDB

3.实验表

假设有个商品表g，关键字段num表示库存，name表示商品名称

主要就是看不同事务隔离机制下并发修改库存是否会出现超卖。

假设我们的程序需要先查询库存，如果库存>0都可以卖，update扣库存，否则rollback。

为了制造并发肯定需要2个事务，假设是A和B。

4.确认事务隔离机制

修改会话的事务隔离级别

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

>select @@global.tx_isolation,@@tx_isolation;

5.Serializable

场景一：

显然一开始AB查询的数据是一样的num=1

A开始update

这时候在等待，无法update。

过一会就超时了。

如果这个时候B也update那么一样会等待超时

所以这样，AB就会都超时。

这时即使commit也是返回0，数据库不会变化。

场景二：

A在update等待的时候，B马上commit，但是B没有update

查看结果

这次A成功的扣库存。

所以从上面可以得出一个结论：serializable是可以很好的控制并发。

然后需要把库存改为1，便于测试。

6.read committed

>set session transaction isolation level read committed;

>select @@global.tx_isolation,@@tx_isolation;

场景三：

初始化AB查出来的库存都是1，然后A可以update一条数据，无等待。

这时候AB再比较下库存，A已经是0，B是1，因为A没有commit。

然后A执行commit操作，这时候B再查已经是库存0；

这时候B执行update返回是0行，因为update不能满足where条件，所以B只有Commit，然后重新提交。

场景四：

一开始AB都是一样的库存1，然后A开始update，然后A的库存是0，B是1，因为A还没有提交。

这时候B再update

按照前面的经验，B等待其实是再等A提交，A如果一直不提交，B就会超时。

这时A提交commit，B查询就得到A更新后的结果，这时B查到库存是0自然不会去更新，也就只能结束事务。

场景五：

AB先后update，然后A在B超时之前commit，这时由于B已经读到A更新后的结果0，所以B就不能成功update。

7.repeatable read

>set session transaction isolation level repeatable read;

>select @@global.tx_isolation,@@tx_isolation;

场景六：

然后A开始update，然后A和B分别读到库存是1和0

然后A提交commit，这时候再查看A和B的库存还是保持不变。

这时候B再次尝试update

依然是返回0条，说明更新不成功。

场景八：

AB同时update

如果A不及时commit那么B肯定会超时

场景九：

就是场景八A及时commit

如果A及时commit

所以可以看出无论是read committed还是repeatable read只要update的条件where  num>0足够充分都是可以控制并发防止超卖的。

如果没有带where  num>0这个控制条件，那么肯定会可以update成功的。

8.read uncommitted

这个是需要杜绝的，就不讨论了。

9.如果没有带where  num>0，那么会怎么样呢。其实只要理解了上述流程就可以想明白会怎么样。

对于read committed

A已经update，B读到库存是0自然不会去更新；

A没有update，B读到库存是1，这要看A会不会及时提交；

如果A及时提交，B自然会去更新因为满足where条件，且成功，这样就超卖-1；

这时候由于B没有提交，所以AB分别查出0和-1

然后B提交commit，AB查出的都是-1，就不演示了。

修改会话为repeatable read

AB先后update，B在等待

然后A立即提交commit，B马上update得到返回。

结果就是-1产生了超卖：

总结：

1.使用serializable是可以防止超卖，但是性能怎么样需要数据说明；

2.read committed和repeatable read带上where条件库存num>0都是可以防止超卖的，不过需要处理超时。

3.其他各种组合情况还会更复杂，具体具体问题具体分析。

心得：

1.update 语句更新的都是当前读，select 根据rc和rr不同，前者读的是当前读，后者是快照读，而ruc可以读到未提交的数据；

2.rc、rr、ruc进行写（更新、新增【除外】、删除）都是有排它锁的，不允许进行其他写的事务；

3.serializable 进行写的时候gap（将相邻的几个行都锁起来，可以对新增进行排它锁）+排它锁（猜测，该隔离级别没用过，这里就不求证了），不允许其他写事务。