Processing max (ID) in a parallel environment

Question

I am new to programming and processing concurrency web applications using RDBMS like SQL Server. I am using SQL Server 2005 Express Edition.

I generate an employee code in which the last four digits come from this query:

SELECT max(ID) FROM employees WHERE district = "XYZ"; 

I do not understand how to handle problems that may arise due to concurrent connections. Many users can select the same max (ID), and while one user clicks "Save Entry", the identifier may already be taken by another user.

How to deal with this problem?

Answer 1

Here are two ways to do what you want. The fact that you may encounter a unique violation of the EmpCode , I will leave you to worry about :).

1. Use scope_identity() to get the last inserted identifier and use it to calculate EmpCode .

Table definition:

 create table Employees ( ID int identity primary key, Created datetime not null default getdate(), DistrictCode char(2) not null, EmpCode char(10) not null default left(newid(), 10) unique ) 

Add one line to employees. You must complete the transaction to ensure that you are not left with a random default value from left(newid(), 10) in EmpCode :

 declare @ID int insert into Employees (DistrictCode) values ('AB') set @ID = scope_identity() update Employees set EmpCode = cast(year(Created) as char(4))+DistrictCode+right( 10000+@ID , 4) where ID = @ID 

2. Make EmpCode a calculated column .

Table definition:

 create table Employees ( ID int identity primary key, Created datetime not null default getdate(), DistrictCode char(2) not null, EmpCode as cast(year(Created) as char(4))+DistrictCode+right(10000+ID, 4) unique ) 

Add one line for employees:

 insert into Employees (DistrictCode) values ('AB') 

Answer 2

It’s a bad idea to use MAX because with the right locking mechanism, you won’t be able to insert rows into multiple threads for the same region. If everything is all right for you, you can only create one user at a time, and if your tests show that MAX scales even with a large number of users in each district, it may be convenient to use. In short, when dealing with identity as much as possible, you should rely on IDENTITY. Actually.

But if this is not possible, one solution is to process the identifiers in a separate table.

 Create Table DistrictID ( DistrictCode char(2), LastID Int, Constraint PK_DistrictCode Primary Key Clustered (DistrictCode) ); 

Then you increment the LastID counter. It is important that the identifier increment is a transaction divided by a user creation transaction if you want to create many users in parallel threads. You can restrict the ability to generate only an identifier.

The code might look like this:

 Create Procedure usp_GetNewId(@DistrictCode char(2), @NewId Int Output) As Set NoCount On; Set Transaction Isolation Level Repeatable Read; Begin Tran; Select @NewId = LastID From DistrictID With (XLock) Where DistrictCode = @DistrictCode; Update DistrictID Set LastID = LastID + 1 Where DistrictCode = @DistrictCode; Commit Tran; 

Keywords Repeatable reading and XLOCK are the minimum you need to avoid two threads to get the same identifier. If the table does not have all counties, you need to change Repeatable reading to Serializable and develop Update using Insert .

Answer 3

This can be done using Transaction Isolation Levels . For example, if you specify SERIALIZABLE as a level, then other transactions will be blocked so that you do not encounter this problem.

If I do not understand your question correctly, let me know.