Avoiding Concurrency Problems with Max+1 Integer in SQL Server 2008... Making Own Identity Value

Select MAX(field)+1 FROM ... Concurrency issues

If you code it right, you should be able to get the same concurrency out of this as with an IDENTITY solution. It is not intuitive; you would think that the locking would reduce concurrency. But I ran several tests with five distinct connections hammering the table, and proved to myself that the MAX+1 trick performed just about exactly the same as IDENTITY. You can see the discussion in the first section of the following blog post:

https://sqlblog.org/2009/10/12/bad-habits-to-kick-making-assumptions-about-identity

Anyway here is the syntax I recommend (I don't really trust the INSERT ... VALUES (SUBQUERY) model, sorry):

   DECLARE @NextID INT;

   BEGIN TRANSACTION;

   SELECT @NextID = COALESCE(MAX(NumberColumn), 0) + 1
       FROM dbo.TableA WITH (UPDLOCK);

   INSERT dbo.TableA SELECT @NextID;

   COMMIT TRANSACTION;

You'll have to imagine the error handling portions (omitted for brevity) but I think you'll get the drift.

Of course don't take any of this for gospel. You will need to run tests on your hardware using your data and access patterns to determine whether concurrency will be harmed by this method. This could very well be one of those "it depends" answers.

Handling max(ID) in a concurrent environment

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')

Adding max(value)+1 in new row, can this be a problem?

Why would you use a custom-rolled Identity field when there is such a great one already in SQL Server?

Just use INT Identity (1,1) for your ID field and it will automatically increment each time a row is inserted. It also handles concurrency much better than pretty much anything you could implement manually.

EDIT:

Sample of a manual ID value:

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable (IdField, Col1, Col2, Col3,...)
VALUES
(1234, 'Col1', 'Col2', 'Col3',...)

SET IDENTITY_INSERT MyTable OFF

You need to include an explicit field list for the INSERT.

inserting into table and incrementing a column based on current highest number

You can just pull the Max(Pos) and add 1 to it:

Insert  Table
        (C1, C2, Pos)
Select  'C1', 'C2', Max(Pos) + 1
From    Table

sql server: Is this nesting in a transcation sufficient for getting a unique number from the database?

As it turned out, i didn't want to lock the table, i just wanted to execute the stored procedure one at a time.
In C# code i would place a lock on another object, and that's what was discussed here
http://www.sqlservercentral.com/Forums/Topic357663-8-1.aspx

So that's what i used

declare @Result int
EXEC @Result =
sp_getapplock @Resource = 'holdit1', @LockMode = 'Exclusive', @LockTimeout = 10000 --Time to wait for the lock
IF @Result < 0
BEGIN
ROLLBACK TRAN
RAISERROR('Procedure Already Running for holdit1 - Concurrent execution is not supported.',16,9)
RETURN(-1)
END

where 'holdit1' is just a name for the lock.
@result returns 0 or 1 if it succeeds in getting the lock (one of them is when it immediately succeeds, and the other is when you get the lock while waiting)

Is this sql update guaranteed to be atomic?

Using SQL SERVER (v 11.0.6020) that this is indeed an atomic operation as best as I can determine.

I wrote some test stored procedures to try to test this logic:

-- Attempt to update a Customer row with a new Count, returns 
-- The current count (used as customer order number) and a bit
-- which determines success or failure.  If @Success is 0, re-run
-- the query and try again.
CREATE PROCEDURE [dbo].[sp_TestUpdate]
(
  @Count INT OUTPUT,
  @Success BIT OUTPUT
) 
AS
BEGIN
   DECLARE @NextCount INT 

   SELECT @Count=Count FROM Customer WHERE ID=1
   SET @NextCount = @Count + 1
   UPDATE Customer SET Count=@NextCount WHERE ID=1 AND Count=@Count
   SET @Success=@@ROWCOUNT
END  

And:

-- Loop (many times) trying to get a number and insert in into another
-- table.  Execute this loop concurrently in several different windows
-- using SMSS.
CREATE PROCEDURE [dbo].[sp_TestLoop]
AS
BEGIN
   DECLARE @Iterations INT
   DECLARE @Counter INT
   DECLARE @Count INT
   DECLARE @Success BIT
   SET @Iterations = 40000
   SET @Counter = 0
   WHILE (@Counter < @Iterations)
   BEGIN
      SET @Counter = @Counter + 1
      EXEC sp_TestUpdate @Count = @Count OUTPUT , @Success = @Success OUTPUT
      IF (@Success=1)
      BEGIN
          INSERT INTO TestImage (ImageNumber) VALUES (@Count)
      END
   END
END

This code ran, creating unique sequential ImageNumber values in the TestImage table. This proves that the above SQL update call is indeed atomic. Neither function guaranteed the updates were done, but they did guarantee that no duplicates were created, and no numbers were skipped.

SQL Server Custom Identity Column

Try this:

BEGIN TRAN

INSERT INTO TBLKEY
VALUES((SELECT MAX(ID) + 1 AS NVARCHAR) FROM TBLKEY WITH (UPDLOCK)),'EHSAN')

COMMIT

When selecting the max ID you acquire a U lock on the row. The U lock is incompatible with the U lock which will try to acquire another session with the same query running at the same time. Only one query will be executed at a given time. The ids will be in order and continuous without any gaps between them.

A better solution would be to create an extra table dedicated only for storing the current or next id and use it instead of the maximum.

You can understand the difference by doing the following:

Prepare a table

CREATE TABLE T(id int not null PRIMARY KEY CLUSTERED)
INSERT INTO T VALUES(1)

And then run the following query in two different sessions one after another with less than 10 seconds apart

BEGIN TRAN
DECLARE @idv int
SELECT @idv = max (id) FROM T
WAITFOR DELAY '0:0:10'
INSERT INTO T VALUES(@idv+1)
COMMIT

Wait for a while until both queries complete. Observe that one of them succeeded and the other failed.

Now do the same with the following query

BEGIN TRAN
DECLARE @idv int
SELECT @idv = max (id) FROM T WITH (UPDLOCK)
WAITFOR DELAY '0:0:5'
INSERT INTO T VALUES(@idv+1)
COMMIT

View the contents of T

Cleanup the T Table with DROP TABLE T

How does a database decide which process it should give the lock to, when there's two or more processes requesting for it at the exact same time?

From the point of view of the database software, there is no such thing as the "same time." But let's say you have a multi-processor system with two network interfaces and two requests end up getting handled at exactly the same CPU clock cycle-- very unlikely (there are billions of cycles per second) but not completely impossible.

There will be a thread for each request. Each thread will need to acquire a database lock, which presumably is held somewhere in memory. The lock will be protected by an operating system lock which will prevent both threads from accessing the database lock at the same time.

The operating system lock, ultimately, also depends on memory access. In order to access the memory, the CPU places a lock on it and transfers a copy of it into onboard cache. The other CPU won't be able to access it until the first CPU is done. So only one CPU will be able to write to the memory to get the O/S lock to get the database lock.

If by some coincidence, both CPUs request the same segment of memory at exactly the same time and both try to acquire the memory lock, the two requests are resolved by "bus arbitration," which could work several ways. It may end up depending on which CPU is physically closer to the bus line, or it might depend on which CPU has the higher device ID.

An application that uses a database will never have to worry about minuscule details like this. It's taken care of by the database platform, by the O/S, the CPU, and ultimately the physical hardware.

---

Related Topics