Problems with Identity and Generating Sequential Numbers Manually

We can see three problems with the Identity property. SQL 6.5 had all three, SQL 7.0 two of them; SQL 2000 still has the last one.

1. The value of the next number was often lost and set to null. This led to problems with PK, which is often on the Identity column. We had to use DBCC CHECKIDENT. The problem was solved with SQL 7.0.

2. If the table has some triggers that insert into other tables with columns with Identity property, what value holds the @@identity global variable? From the last trigger, but we don’t know the order of the triggers? This problem was solved in SQL 2000 with the SCOPE_IDENTITY() and IDENT_CURRENT() functions. Besides that we have limited influence on triggers order – we can select the first and the last to fire.

3. The third problem is still not resolved in SQL 2000. The transaction isolation level does not have any influence on the Identity. Even with the serializable level you get gaps if a transaction is not committed.

The script bellow shows the third problem and the solution with manual sequential numbers. Please do check Joe Celko’s books and posts in public newsgroups for some other solutions as well.

/* Problems with Identity property

Manually inserting sequence numbers without gaps */

/* 1. Infrastructure */

IF OBJECT_ID(‘dbo.midents’,‘U’) IS NOT NULL

   DROP TABLE dbo.midents

GO

IF OBJECT_ID(‘dbo.demo1’,‘U’) IS NOT NULL

   DROP TABLE dbo.demo1

GO

IF OBJECT_ID(‘dbo.demo2’,‘U’) IS NOT NULL

   DROP TABLE dbo.demo2

GO

CREATE TABLE dbo.midents

(tablename varchar(10) PRIMARY KEY,

 mident int)

GO

CREATE TABLE dbo.demo1

(id int IDENTITY(1,1) NOT NULL)

GO

CREATE TABLE dbo.demo2

(id int NOT NULL)

GO

INSERT INTO dbo.midents VALUES(‘dbo.demo2’,0)

SELECT *

  FROM dbo.midents

GO

/* 2. Identity – can’t control transaction isolation level */

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

BEGIN TRANSACTION

INSERT INTO dbo.demo1 DEFAULT VALUES

SELECT *

  FROM dbo.demo1

— stop here, open another session and

— execute statements from the point Session2

ROLLBACK TRANSACTION

— Session2

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

BEGIN TRANSACTION

INSERT INTO dbo.demo1 DEFAULT VALUES

SELECT *

  FROM dbo.demo1

— blocked because of serializable isolation level

— go to session 1 and execute the rollback

COMMIT

— after rollback this transaction executes with the value 2

— serializable level did not affect the identity

— reset the isolation level

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

— Session 1 again

— Check for forgotten transactions

SELECT @@trancount

— reset the isolation level

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

/* 3. Manual Identity – take control */

BEGIN TRANSACTION

DECLARE @mid int

SET @mid=0

UPDATE dbo.midents — WITH (HOLDLOCK)

   SET @mid=mident=mident+1

 WHERE tablename=‘dbo.demo2’

SELECT *

  FROM dbo.midents

SELECT @mid

INSERT INTO dbo.demo2 VALUES(@mid)

SELECT *

  FROM dbo.demo2

— stop here, open another session and execute statements from the point Session3

ROLLBACK TRANSACTION

— Session3

EXEC sp_lock

SELECT OBJECT_NAME(290100074),OBJECT_NAME(322100188) 

— find correct object id’s

— go to session 2 and execute statements from the point Session2

— Session2

BEGIN TRANSACTION 

— session 2 is waiting, show locks from session 3 again

DECLARE @mid int

SET @mid=0

UPDATE dbo.midents — WITH (HOLDLOCK)

   SET @mid=mident=mident+1

 WHERE tablename=‘dbo.demo2’

SELECT *

  FROM dbo.midents

SELECT @mid

INSERT INTO dbo.demo2 VALUES(@mid)

SELECT *

  FROM dbo.demo2

COMMIT

— go to session 1 and execute the rollback

— we have sequential numbers without gaps – QED

— Check for forgotten transactions

SELECT @@trancount