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:
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:
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.
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:
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:
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:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
INSERT INTO TStudentTable([Name])
VALUES ('Student No. 3')
COMMIT TRAN
The script generated the following result:
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]:
ROLLBACK TRAN
SELECT * FROM TStudentTable
DROP TABLE TStudentTable
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.
