Handling max(ID) in a concurrent environment

Question

I am new to web application programming and handling concurrency using an RDBMS like SQL Server. I am using SQL Server 2005 Express Edition.

I am generating employee code in which the last four digits come from this query:

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

I am not following how to handle issues that might arise due to concurrent connections. Many users can pick same max(ID) and while one user clicks "Save Record", the ID might have already been occupied by another user.

How to handle this issue?

Answer 1

It is a bad idea to use MAX, because with a proper locking mechanism, you will not be able to insert rows in multiple threads for the same district. If it is OK for you that you can only create one user at a time, and if your tests show that the MAX scales up even with a lot of users per district, it may be ok to use it. Long story short, dealing with identies, as much as possible, you should rely on IDENTITY. Really.

But if it is not possible, one solution is to handle IDs 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 incrementing IDs is a transaction separated to the user creation transaction if you want to create many users in parallel threads. You can limit to have only the ID generation in sequence.

The code can 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;

The Repeatable Read and XLOCK keywords are the minimum that you need to avoid two threads to get the same ID. If the table does not have all districts, you will need to change the Repeatable Read into a Serializable, and fork the Update with a Insert.

Answer 2

Here are two ways of doing what you want. The fact that you might end up with unique constraint violation on EmpCode I will leave you to worry about :).

1. Use scope_identity() to get the last inserted ID and use that 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 row to Employees. Should be done in a transaction to be sure that you will not be left with the default random 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 computed 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 row to Employees:

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

Answer 3

This can be done through Transaction Isolation Levels. For instance, if you specify SERIALIZABLE as the level then other transactions will be blocked so that you aren't running into this problem.

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