Use the seed table in SQL Server to generate the sequence of sequential numbers

A person asked me a few days ago about the duplicate serial number. I thought about it. Although it is simple but extensive, I wrote this blog to introduce it and hope it will help you.

When developing database applications, you will often encounter the situation of generating a sequential number. For example, if you create an order module, the order number must be unique and the rule is: this rule is applicable to the year, month, day, and 6-digit serial numbers when an order is placed.

For such a system to generate a sequential number, we generally create a new seed table in the database, each time a new order is generated:

1. Read the maximum number of seeds on the current day.

2. Generate a unique order number based on the maximum seed value and the current year, month, and day.

3. Update the maximum value of the seed so that the maximum value is greater than 1.

4. Insert the order data to the order table based on the generated order number.

The preceding steps are completed in a transaction to ensure the continuity of the serial number. This idea is correct, and it seems that there is no problem in use. However, when the business volume is large, an error is often reported: "The Order Number violates the primary key constraint, duplicate order numbers cannot be inserted into the Order table." What's going on? Let's make a simple Demo to reproduce it:

1. Create a seed table and order table. This is just a simple Demo. Therefore, many fields are saved, and the order number is assumed to be a sequential number, so you no longer need to use the year, month, and day + 6 sequential numbers.

Create table Seek -- seed TABLE
(
SeekValue INT
)
GO
Insert into Seek VALUES (0) -- The initial seed value is 0.
GO
Create table Orders
(
OrderID int primary key, -- order number, PRIMARY KEY
Remark VARCHAR (5) NOT NULL
)

2. Create a stored procedure that passes in the Remark parameter and inserts it into the Order table based on the generated serial number:

Create proc AddOrder -- Author: Deep Blue
@ Remark VARCHAR (5) -- input parameter
AS
DECLARE @ seek int
Begin tran -- start a transaction
SELECT @ seek = SeekValue -- read the maximum value in the seed table as the serial number
FROM Seek

-- Generate the order number. This step is omitted because the order number assumed here is the sequential number.

UPDATE Seek SET SeekValue = @ seek + 1 -- UPDATE the seektable so that the maximum value is + 1

Insert into t1 VALUES (@ seek, @ remark) -- INSERT an order data

COMMIT -- submit a transaction

3. Create a query window, use the following statement to call the created stored procedure, and insert new orders continuously:

WHILE 1 = 1
EXEC AddOrder 'test1' -- inserts orders continuously

 

4. Create a New query window and insert new orders continuously through the method to simulate high concurrency:

WHILE 1 = 1
EXEC AddOrder 'test2'

 

5. After running for a period of time, we stop the two endless loops. We can see that there are a lot of exceptions in the message window:

Message 2627, Level 14, status 1, process AddOrder, 11th rows
The primary key constraint 'pk _ Orders _ C3905BAF08EA5793 'is violated '. Duplicate keys cannot be inserted in the 'dbo. Orders 'object.
The statement has been terminated.

Why? This should be explained from the transaction isolation level and lock:

Generally, when writing a program, we use the default transaction isolation level-committed read. When querying the Seek table in the first step, the system places a shared lock for the table, in terms of lock compatibility, shared locks and shared locks are compatible. Therefore, when a transaction reads the maximum value of the Seek table, other transactions can read the same maximum value, the two transactions read the same maximum value, so the same sequential number is generated, so the same order number is generated, so there will be an error that violates the primary key constraints.

Now that you know the principles, you can solve this problem. You only need to first read the number + 1 in the seed table, and then modify the stored procedure as follows:

Alter proc AddOrder -- Author: Deep Blue
@ Remark VARCHAR (5)
AS
DECLARE @ seek int
BEGIN TRAN

UPDATE Seek SET SeekValue = SeekValue + 1 -- modify data first

SELECT @ seek = SeekValue-1 -- 1 has been added, so here-1 down
FROM Seek

Insert into Orders VALUES (@ seek, @ remark)

COMMIT

 

Why can I write it like this? The first step is to execute the update operation. The system will request the update lock and then upgrade it to the exclusive lock because the update lock and exclusive lock are incompatible, therefore, after a transaction updates the Seek table, other transactions cannot update the table. They can continue only after the transaction is committed.

The lock compatibility table is attached here:

	Existing authorization Mode
Request Mode	IS	S	U	IX	SIX	X
Intention sharing (IS)	Yes	Yes	Yes	Yes	Yes	No
Share (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Intention exclusive (IX)	Yes	No	No	Yes	No	No
Intention exclusive sharing (SIX)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No