Let's also hold it-optimize SQL SERVER lock usage

Second kill is no stranger. Second kill is more of a reflection on concurrency for our programmers. It may be a genius to consider how to make a second kill to sweep the "second kill ". The last day was invited to optimize the SQL statement for the second kill.

Let's take a look at the second kill storage process. (Some code is as follows)

　　

Create PROCEDURE [dbo]. [kill]
@ Userid nvarchar (64 ),
@ KillId int
AS
BEGIN
BEGIN TRAN
Declare @ storage int
-- Judge inventory
Select @ storage = storage from killProduct with (tablockx) where killID = @ killId
IF (@ rowcount = 0)
BEGIN
COMMIT TRAN
Select 'out of stock'
Return
END
IF (@ storage <= 0)
BEGIN
COMMIT TRAN
Select 'out of stock'
Return
END

-- Continue to determine whether the second kill is over
....
-- Determine whether userId has participated in the second kill
....

 

At first glance, there are several problems:

1. if else is too large, which affects performance. Because SQL server is not good at computing, the performance loss of an if branch is much higher than that of the if branch in clr. Therefore, we recommend that you make these judgments in clr, at the same time, we recommend that you write SQL statements on the first page like paging. Do not use the if (pageIndex = 1) Branch.

2. It's too early for TRAN. Why are you so impatient? You don't need to come to the foreplay. It's too emotional. How can we say that the granularity of things must be well grasped. Do not lock too many resources in things. The granularity of things is too large and will inevitably affect performance.

3. with (tablockx) is certainly necessary, because the second kill is characterized by high concurrency. As a man knows, it must hold on for a few seconds. The problem is that you want to lock the entire table and have exclusive permissions. you have to defeat others, so it means that others are victims, other people have to queue up to query data from other rows ).

4. There's nothing to do with the above two. if you don't think the first one is unnecessary?

 

Well, I can't help it either. I will post a piece of code on how to hold it. (Modified code)

 

-- Directly update and use the subquery to check whether the user has participated in the second kill.
-- Use hold to ensure that no other user updates the row before the process ends. The row is readable and rowlock is a shared lock.
UPDATE killProduct WITH (ROWLOCK, HOLDLOCK) SET storage = storage-1, @ storage = storage
WHERE killID = @ killId AND storage> 0 AND (select count (0) FROM KILLlIST (NOLOCK) where userid = @ USERID) = 0

IF (@ storage> 0)
BEGIN
-- Seckilling successful
END
ELSE
BEGIN
-- Failed
END

 

Holdlock, hold this row of data (rowlock) until the end of the entire transaction.

Replace holdlock with UPDLOCK to achieve the same effect.

Are you holding the second kill? How do you hold this concurrency? Please share with us!