Click here to Skip to main content
15,868,141 members
Articles / Database Development / SQL Server

Some Interesting T-SQL Scripts Regarding Two Transactions Related to Identity Fields and Table Variables

,
Rate me:
Please Sign up or sign in to vote.
4.33/5 (3 votes)
26 Apr 2010CPOL5 min read 21.6K   6   2
This article introduces some interesting T-SQL scripts regarding two transactions related to the identity fields and table variables

Introduction

This article introduces some interesting T-SQL scripts regarding two transactions related to the identity fields and table variables.

Background

When designing databases, I normally do not use identity fields. I always try to give the database tables some practically meaningful primary keys. Most of the IT systems that I have experienced heavily use replications to synchronize the data among database servers and a lot of the replications are merge replications. In this case, we will have two not very pleasant maintenance jobs if identity fields are used:

  • We need to make sure that the identity seeds on different servers are properly set, so no duplicate identities are generated at different servers.
  • When the replication fails, fixing the data will be a nightmare by constantly setting the "IDENTITY INSERT" on and off.

Recently due to some historical reasons, the IT systems that I am working on heavily use identity fields, so I started to look into it. I wrote some small scripts and the result is kind of interesting. I would like to share them with my friends.

I am not sure if these small scripts deserve to be in an article, but let me post them here anyway. I ran these scripts in SQL server 2008.

Script No.1 - Identity Fields in Table Variables

This script is more related to table variables than identity fields:

SQL
DECLARE @T AS TABLE
 ([ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Name] [varchar](50) NOT NULL)
 
INSERT INTO @T([Name]) VALUES ('Student No. 1')
INSERT INTO @T([Name]) VALUES ('Student No. 2')
INSERT INTO @T([Name]) VALUES ('Student No. 3')
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    INSERT INTO @T([Name]) VALUES ('Student No. 4')
ROLLBACK TRAN
 
INSERT INTO @T([Name]) VALUES ('Student No. 4')
 
SELECT [ID], [Name] FROM @T

It first defines a table variable that has an identity field, and then inserts some data into the table variable. The insertion of "Student No. 4" is in a transaction which is rolled back immediately. After the transaction is rolled back, the "Student No. 4" is inserted again.

Run the script, we get the following result:

ATableVariable.jpg

We can find that the "Student No. 4" shows up twice. What this means is that table variables do not participate in transactions. If we want to rely on transactions to determine the data contents in table variables, we shall be very careful.

Script No.2 - Identity Fields in Temporary Tables

The script No. 2 is almost identical to the script No. 1, except that the table variable is replaced by a temporary table.

SQL
CREATE TABLE #T
 ([ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Name] [varchar](50) NOT NULL)
 
INSERT INTO #T([Name]) VALUES ('Student No. 1')
INSERT INTO #T([Name]) VALUES ('Student No. 2')
INSERT INTO #T([Name]) VALUES ('Student No. 3')
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    INSERT INTO #T([Name]) VALUES ('Student No. 4')
ROLLBACK TRAN
 
INSERT INTO #T([Name]) VALUES ('Student No. 4')
 
SELECT [ID], [Name] FROM #T
 
DROP TABLE #T

Run the script, we get the following result:

ATempTable.jpg

Temporary tables do participate in transactions. The "Student No. 4" shows up only once, but the ID for "Student No. 4" is 5. The identify seed is not rolled back when we roll back the first attempt to insert "Student No. 4". This means that we shall never assume the identity fields will give us consecutive numbers.

The above script uses a temporary table to do the experiment, but the result is true for persistent database tables too. We have noticed many times that the identity seeds were not rolled back when transactions rolled back in our IT systems.

Some Further Study on Transactions

A database transaction should be "ACID". The definition of Atomicity is "Atomicity (or atomicness) is one of the ACID transaction properties. In an atomic transaction, a series of database operations either all occur, or nothing occurs". According to this definition, when SQL server rolls back an insertion related to identity fields, it does not strictly follow the "ACID" definition to make "nothing occurs" happen.

Is there any advantage not to roll back the identity seed? The advantage is to allow better parallelism.

To prove my assumption, I did a further experiment. I first ran the following script:

SQL
IF  EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[TStudentTable]')
  AND type in (N'U'))
DROP TABLE [dbo].[TStudentTable]
 
CREATE TABLE [dbo].[TStudentTable](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NOT NULL)
  
INSERT INTO TStudentTable([Name])
 VALUES ('Student No. 1')
  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    INSERT INTO TStudentTable([Name])
  	VALUES ('Student No. 2')

This script first creates a persistent database table [TStudentTable] with an identity field. After inserting one row, it starts a transaction to insert another row inside the transaction. This transaction is neither committed nor rolled back.

If the rolling back of this transaction will also roll back the identity seed, this pending transaction will need to block any attempt to insert rows into this table from other transactions to avoid cascading rollbacks in case this transaction is indeed rolled back. But does this on-going transaction block other transactions trying to insert into [TStudentTable] table?

I started another instance of SQL server management studio to open a new connection session, and issued the following script:

SQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
 INSERT INTO TStudentTable([Name])
  VALUES ('Student No. 3')
COMMIT TRAN

The script generated the following result:

Committed.jpg

It shows that the insertion of "Student No. 3" is not blocked, although the transaction to insert "Student No. 2" in an earlier transaction is still pending. Because rolling back a transaction does not roll back the identity seed, the insertion of "Student No. 3" remains valid regardless if the insertion of "Student No. 2" in the pending transaction is finally committed or rolled back.

Let us roll back the pending transaction inserting "Student No. 2" and take a look at the data in the [TStudentTable]:

SQL
ROLLBACK TRAN
 
SELECT * FROM TStudentTable
DROP TABLE TStudentTable
FinalResult.jpg

We can find that the insertion of "Student No. 3" is indeed committed.

Conclusion

By running the above small scripts, we can come to the following conclusions:

  • Table variables do not participate in transactions, We should not rely on transactions to determine the data contents in the table variables.
  • When rolling back an insertion related to identity fields, the identity seeds are not rolled back. By allowing not to roll back the identity seeds, we can achieve better parallelism. At the same time, we shall not assume that the identity fields will always have consecutive numbers. Even for rows inserted inside a single transaction, there is no guarantee that the numbers will be consecutive.

If you want to repeat the experiments, you may need to have access to a SQL server and may need some permissions to run the scripts.

History

This is the first revision of this article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 4 Pin
span23728-Jun-10 21:11
span23728-Jun-10 21:11 
Generalthanks for sharing. Pin
A Rahim Khan4-May-10 8:54
A Rahim Khan4-May-10 8:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.