Skip to content

Latest commit

 

History

History
94 lines (83 loc) · 4.06 KB

超卖超卖超卖.md

File metadata and controls

94 lines (83 loc) · 4.06 KB
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; 死锁

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