SQL statement document number generation anti-concurrency

User feedback said to find duplicate document number, check found that the following document number was assigned to a different two employees

The system uses the statement exec getnewordernumber ' pwgnumber ', ' PWG ', 1, @pwg_number output

To generate a unique document number

The statement that gets the maximum value in this stored procedure is as follows:

if exists (SELECT * from S_systemset (nolock) where [email protected] and [email protected])        begin          Begin tran--Statement Segment 1 Select @ordernumber =typename+substring (Replicate (' 0 ', convert (int,8)) +itemvalue,       len (Replicate (' 0 ', conv     ERT (int,8)) +itemvalue)-(CONVERT (int,8)-1), convert (int,8)) from         S_systemset (nolock)        where [email protected ] and            [email protected]      --statement segment 2              update S_systemset           set itemvalue=itemvalue+1 from        S_systemset            where [email protected] and            [email protected]          Commit Tran      

1	同时开两个SQL查询窗口执行以下语句

Window one: waitfor DELAY ' 00:00:05 '
print ' 1 '
DECLARE @ordernumber varchar, @i int=1, @sysid int=1
While @i<=31000
Begin
exec testnewordernumber ' testnumber ', ' TST ', 1, @ordernumber output
Insert into Test_log (docnumber,sysid,indate)
VALUES (@ordernumber, @sysid, GETDATE ())
Set @[email protected]+1
End

Window Two:

WAITFOR DELAY ' 00:00:06 '
print ' 2 '
DECLARE @ordernumber varchar, @i int=1, @sysid int=2
While @i<=32000
Begin
exec testnewordernumber ' testnumber ', ' TST ', 1, @ordernumber output
Insert into Test_log (docnumber,sysid,indate)
VALUES (@ordernumber, @sysid, GETDATE ())
Set @[email protected]+1
End

Check if there are duplicates:

Select Docnumber,count (Docnumber)
From Test_log
GROUP BY Docnumber
Having COUNT (Docnumber) >=2

The test results are as follows: 8 records are duplicated.

　　

After two different users call this statement in the same second, a duplicate pwg_number is generated, why?

The transaction has been used in SQL to get the maximum value, why do different users return the same pwg_number when they call almost simultaneously?

Try to swap the statement segment 1 and statement segment 2 as follows:

--Swap Statement Segment 1 and statement segment 2 to begin TRAN        update S_systemset   --statement segment 2        set itemvalue=itemvalue+1 from        s_systemset            where [email protected] and            [email protected]     --statement segment 1      Select @ordernumber =typename+substring (Replicate (' 0 ', convert (int,8)) +itemvalue,len (Replicate (' 0 ', convert (int,8)) +itemvalue)-(CONVERT (int,8)-1), convert (int,8)) From         S_systemset (rowlock)--Change nolock to Rowlock       where [email protected] and            [email protected]          Commit Tran

Repeat in two windows parallel test, the first time is 90,000 strokes, the second is 900,000 strokes, 3rd time 100,000 pens. Statistics have no duplicate records.

Is the problem of repeating the document number to be solved?

Everyone SQL Daniel, why the sentence segment one and the sentence segment two after the order of the swap, there will be no duplication.

The question comes, is the above statement really guaranteed to produce a unique maximum value?

SQL statement document number generation anti-concurrency