超卖超卖超卖.md

create table stock(
  id int not null auto_increment primary key comment "主键id",
  sku_id int not null unique key comment "sku id",
  count int not null comment "库存"
)engine = innodb comment "库存表";

create table order_item(
  id int not null auto_increment primary key comment "主键id",
  sku_id int not null comment "sku_id",
  user_name varchar(32) not null comment "下单用户名"
)engine=innodb comment "订单表";

insert into stock(sku_id,count) value(1234,1);  //在只有一个库存的情况下

一般的减库存逻辑

1.开启事务
2.查库存
3.判断库存
4.将库存减一
5.写订单
6.写订单失败，回滚
6.写订单成功，提交事务

在repeatable read、read committed、read uncommitted级别下一般的减库存逻辑是会发生超卖的

	session A	session B
1	begin	begin
2	select count from stock where sku_id = 1234;//得到库存为1
3	stock>0	select count from stock where sku_id = 1234;//得到库存为1，select默认不加锁
4	update stock set count=count-1 where sku_id = 1234;	stock>0
5		update stock set count=count-1 where sku_id = 1234; //(排它锁与排它锁阻塞)
6	insert into order_item(sku_id,user_name) value(1234,"session A"); //写订单	继续阻塞
7	insert成功	继续阻塞
8	commit
9		update stock set count=count-1 where sku_id = 1234;
10		insert into order_item(sku_id,user_name) value(1234,"session B"); //写订单
11		commit //发生超卖

seriablizable隔离级别防止超卖

seriablizable级别就是读写串行化，肯定不会造成超卖，但是性能非常低，不应该使用这个隔离级别

悲观锁防止超卖

	session A	session B
1	begin	begin
2	select count from stock where sku_id = 1234 for update;//得到库存为1
3	stock>0	select count from stock where sku_id = 1234 for update;//(共享锁与排它锁阻塞)
4	update stock set count=count-1 where sku_id = 1234;	继续阻塞
6	insert into order_item(sku_id,user_name) value(1234,"session A"); //写订单	继续阻塞
7	insert成功	继续阻塞
8	commit
9		stock<=0 return false

select加独占锁会造成死锁

	session A	session B
1	begin	begin
2	select count from stock where sku_id = 1234 lock in share mode;//得到库存为1
3	stock>0	select count from stock where sku_id = 1234 lock in share mode;//不阻塞，独占锁之间不冲突
4		stock>0
5	update stock set count=count-1 where sku_id = 1234;//阻塞，独占锁和排他锁冲突
6		update stock set count=count-1 where sku_id = 1234;//死锁

select双读不加锁在 repeatable read、read committed级别下不会发生超卖

	session A	session B
1	begin	begin
2	select count from stock where sku_id = 1234;//得到库存为1
3	stock>0
4		select count from stock where sku_id = 1234;//得到库存为1
5	update stock set count=count-1 where sku_id = 1234;
6		update stock set count=count-1 where sku_id = 1234; //排他锁阻塞
7	select count from stock where sku_id = 1234;//0	继续阻塞
8	stock>=0	继续阻塞
9	insert into order_item(sku_id,user_name) value(1234,"session A"); //写订单	继续阻塞
10	commit //释放锁	继续阻塞
11		select count from stock where sku_id = 1234; // -1，这里其实是幻读
12		stock>=0 //false
13		回滚

双读在update stock set count=$count-1 where sku_id = 1234会发生超卖($count是第一次select得到的count)，正确的语句应该是update stock set count=count-1 where sku_id = 1234

额外篇：测试超卖代码加锁错误造成的死锁

	session A	session B
1	begin	begin
2	select count from stock where sku_id = 1234 for update;//得到库存为1
3	stock>0	select count from stock where sku_id = 1234 lock in share mode;//独占锁和共享锁冲突，阻塞
4	update stock set count=count-1 where sku_id = 1234;	死锁

额外篇：乐观锁如何解决超卖？？？？乐观锁在这个库存涉及下无法解决超卖