SQL Server2000 uses row locks to solve the negative overselling problem of concurrent inventory

Assume that the structure and data of the database and table are as follows:

The code is as follows:	Copy code
Create table Stock ( Id int identity (1, 1) primary key, Name nvarchar (50 ), Quantity int ) -- Insert Insert into Stock select 'Orange ', 10

In the case of high concurrency such as second kill, using the following storage process will cause the inventory to be negative and cause the oversold problem:

The code is as follows:

Copy code

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

In this case, we can use the exclusive lock (X lock) to solve the problem:

The code is as follows:

Copy code

Create procedure [dbo]. Sale @ Name nvarchar (50 ), @ Number int, @ Result bit output As Begin tran Declare @ Quantity int Select @ Quantity = Quantity from Stock with (ROWLOCK, XLOCK) 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

Additional instructions

[1]. When no locks are needed, the problem of using Quantity = @ Quantity-@ number to reduce inventory is more serious, not just a negative problem;
[2]. XLOCK has two functions: one is to indicate that the lock mode is exclusive lock, and the other is to extend the row lock time until the transaction ends. If XLOCK is omitted, the problem of overselling will still occur;
[3]. What is the result of changing the above XLOCK to HOLDLOCK?

Bytes --------------------------------------------------------------------------------------------------------
Some netizens provide another method. Because the update statement itself has an exclusive lock, the following method can also be used:

 

The code is as follows:

Copy code

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 and Quantity >=@ number End If @ rowcount> 0 begin Select @ result = 1 Commit End Else begin Select @ result = 0 Rollback End Go