create table Stock
(
Id int identity(1,1) primary key,
Name nvarchar(50),
Quantity int
)
--insert
insert into Stock select 'orange',10在秒杀等高并发情况下，使用下面的存储过程会导致库存为负产生超卖问题：
create procedure [dbo].Sale
@name nvarchar(50),
@number int,
@result bit output
as
begin tran
declare @Quantity int
select @Quantity=Quantity from Stock where Name=@name
if @Quantity>=@number begin
waitfor delay '00:00:10' --todo
update Stock set Quantity=Quantity-@number where Name=@name
end
if @@rowcount>0 begin
select @result=1
commit
end
else begin
select @result=0
rollback
end
go这种情况下，我们可以对行使用排他锁(X锁)来解决：
create procedure [dbo].Sale
@name nvarchar(50),
@number int,
@result bit output
as
begin tran
declare @Quantity int
select @Quantity=Quantity from Stock [color=Brown]with(ROWLOCK,XLOCK)[/color] where Name=@name
if @Quantity>=@number begin
waitfor delay '00:00:10' --todo
update Stock set Quantity=Quantity-@number where Name=@name
end
if @@rowcount>0 begin
select @result=1
commit
end
else begin
select @result=0
rollback
end
go补充说明
[1].不用行锁时，减库存用Quantity=@Quantity-@number问题更严重，不仅仅是为负的问题了；
[2].XLOCK的作用有两点：一是说明锁模式为排他锁，二是延长行锁时间至事务结束。若省略XLOCK仍会产生超卖问题；
[3].锁模式有共享锁(HOLDLOCK)、更新锁(UPDLOCK)和排他锁(XLOCK)，考虑将上边XLOCK改为HOLDLOCK结果会怎样？
--------------------------------------------------------------------------------------------------------
有网友提供了另外一种方法，由于update语句本身具有排他锁，因而用下边方法也是可以的：
create procedure [dbo].Sale
@name nvarchar(50),
@number int,
@result bit output
as
begin tran
declare @Quantity int
select @Quantity=Quantity from Stock where Name=@name
if @Quantity>=@number begin
waitfor delay '00:00:10' --todo
update Stock set Quantity=Quantity-@number where Name=@name [color=Brown]and Quantity>=@number[/color]
end
if @@rowcount>0 begin
select @result=1
commit
end
else begin
select @result=0
rollback
end
go
评论（0）
暂无评论。