A workaround to avoid inserting duplicate data in case of database concurrency

At present, the company's project encountered a situation: the need to insert a record into a data table table1, the structure of the table is similar to the following definition:

Column Name	Type	Whether to allow empty
Id	Int	No
Area	String	No
Areaindex	Int	No
Name	String	No

Where the value of name is stitched together by area and Areaindex, in the form of "Area+areaindex". For the same area,areaindex counting starting from 1, so for the case of area "AA", "BB", "CC" respectively, the value of name is similar to the following:

AA001 AA002 BB001 AA003 BB002 CC001 this form.

When inserting a new value, it is necessary to determine the maximum areaindex of the area in the data table, adding 1 as the areaindex of the new row, and stitching the name into the database. In the case of a database concurrency, a large number of identical records can occur.

One of the better ways to think about this is:

1, create a new table Table2 to store the maximum areaindex of area, when inserting a new record, get the maximum areaindex from the new table, add one as the Areaindex of the newly recorded, and update the maximum areaindex of the new table.

2, create a new stored procedure to add the records to the table. In this stored procedure, query Table2 and lock the table first. After you have inserted the maximum areaindex for the area of the record and updated the records in Table2, insert the record into Table1. The code for the stored procedure resembles the following:

BEGIN
BEGIN TRAN
DECLARE @MaxAreaIndex int

--Query and lock Table2
SELECT @MaxAreaIndex = Areaindex
From Table2 with (Tablockx)
WHERE area= @Area

--After getting to the maximum Areaindex, update Table2
IF @MaxAreaIndex is NULL
BEGIN

--if there is no record in Table2, a new record is added
SET @MaxAreaIndex = 1
INSERT into [Table2]
([Maxareaindex], [area])
VALUES
(@MaxAreaIndex, @Area)
END
ELSE

BEGIN

SET @MaxAreaIndex = @MaxAreaIndex + 1
UPDATE Table2
SET Maxareaindex = @MaxAreaIndex
WHERE area = @Area

END

INSERT into Table1
([Name], [area], [Areaindex])
VALUES
(@Area + RTRIM (LTRIM (STR (@MaxAreaIndex))), @Area, @MaxAreaIndex)

COMMIT TRAN

The key code is:

SELECT @MaxAreaIndex = Areaindex
From Table2 with (Tablockx)
WHERE area= @Area

When a table Table2 is locked in a stored procedure, other calls to the stored procedure must wait for the previous execution to complete and release the lock on the table before continuing execution.

In the case of area not many, efficiency has no big impact.