Manual generation of serial numbers for transactions and lock applications

Manual generation of serial numbers for transactions and lock applications

Author: no_mIss

For numbers, we usually use automatic numbers, but sometimes it also generates numbers such as BH0001?
The method is generally to query the maximum value in the table, and then add this value to 1 to get the new number.

Here we do not discuss an example of a table. For better scalability,
Create a table separately to store maxid. The principle is the same.

First, create a table to generate the maximum idnumber of different projects.
Create table T_table (id int, projectid char (1) primary key)
Insert into T_table SELECT 1, 'A'
Insert into T_table SELECT 1, 'B'
Insert into T_table SELECT 1, 'C'

Note: A, B, and C represent different projects. Here we only use
This assumption is to generate the order number OrderId.


Next, create a stored procedure and return the order number OrderId.

Create proc P_CreateOrderId
@ ProjectId CHAR (1), -- project name
@ ReOrderId VARCHAR (50) OUTPUT -- returned Order Number
AS
BEGIN TRAN
DECLARE @ maxid int
-- Obtain the maximum id of projectid as.
SELECT @ maxid = (id + 1) FROM T_table WHERE projectid = @ ProjectId
-- The settings shown here have a latency of 10 s.
Waitfor delay '00: 00: 10'
UPDATE T_table SET id = @ maxid FROM T_table WHERE projectid = @ ProjectId
SELECT @ reOrderId = @ maxid
COMMIT TRAN

Call method:
Declare @ reOrderId int
Exec P_CreateOrderId 'A', @ reOrderId out
Select @ reOrderId

After obtaining this Orderid, You Can format it again, such as BH00001.

At first glance, you may think there is no problem, but if you have done a multi-user concurrent Data Access Project, you will find a problem:
If both users execute
SELECT @ maxid = (id + 1) FROM T_table WHERE projectid = @ ProjectId
Then, the same value @ maxid will be obtained. This is obviously unacceptable.

To better illustrate the problem, I added this sentence:
Waitfor delay '00: 00: 10'


When we execute
Declare @ reOrderId int
Exec P_CreateOrderId 'A', @ reOrderId out
Select @ reOrderId
And then execute
Select * from T_table where projectid = 'A'
It is found that data with projectid A in the current table can be obtained. It clearly indicates that when multiple users execute this PROC at the same time,
The same order ID (maxid + 1) is returned.


Haha ..... Something else ....

We know that all MSSQL operations on data are performed first by placing a lock.
When the SELECT statement is used, the shared lock is placed, and the shared lock is released after execution,
Other users can still place shared locks, so we only need to let the SELECT statement not release
The transaction where it is located is completely released and then the shared lock can be released.

Now, the goal is clear .?

Let's improve the isolation level of transactions:
SELECT @ maxid = (id + 1) FROM T_table WHERE projectid = @ ProjectId
Changed to: SELECT @ maxid = (id + 1) FROM T_table (XLOCK, PAGLOCK) WHERE projectid = @ ProjectId

Note:
XLOCK uses the exclusive lock and remains until the end of the transaction on all data processed by the statement. Use PAGLOCK or TABLOCK to specify the lock.


After this setting, when a user executes the stored procedure, other users need to wait.
By the way: the shorter the transaction, the better.

I hope this will be helpful to everyone. If you are interested, you have time to write another example to reduce the transaction isolation level .?

I am at a limited level. If you have any problems, please correct me.