Identities - Part 2 A Bug

Introduction

Recently I was testing something and I noticed the following bug.

The Problem

I inserted a duplicate row into my table. The trigger caught this and issued a Rollback which prevented the row from being inserted. I had expected this, so I wasn't surprised. As I always do, I then proceeded to run a few more tests to see if the trigger worked completely as expected. It was during this that I noticed something strange had happened.

Even though the transaction rolled back, it appears the identity property assigns a new value outside of the transaction. Not quite what I expected and a behavior that might cause some issues.

Case 1

To test my theory that the identity property works like this:

• Get next identity value
• Start Transaction
• Insert Row
• Commit Transaction

and not like this

• Start Transaction
• Get next identity value
• Insert Row
• Commit Transaction

which is what I would expect, I decided to create a table and make some inserts. Here is my first test:

create table MyTable
(MyID int identity(1, 1)
, MyDesc varchar(20)
)
go
select * from MyTable
go
insert MyTable select 'Expect 1'
select * from MyTable
go
At this point, there is one row as expected along with the expected identity value. To test my theory, I next created a trigger:
create trigger MyTable_tri on MyTable for insert
as
if (select MyDesc from inserted) = 'Expect 2'
rollback transaction
return
go
Now I run a new insert.
insert MyTable select 'Expect 2'
go
select * from MyTable
go
As expected, there is still only one row. Now the question of where the identity seed sits is where this gets interesting. I would expect that the next identity value would be 2 since the transaction was rolled back. So let's insert a row and see.
insert MyTable select 'Expect 2, too'
go
select * from MyTable
go
drop table MyTable
I dropped the table to clean up, but notice the results. The next identity value, 3, is used for the new row, even though the previous transaction was rolled back.
Case 1
Next I wondered if am explicit transaction exhibited the same properties. So I decided to setup a new test.
create table MyTable
(MyID int identity(1, 1)
, MyDesc varchar(20)
)
go
insert MyTable select 'Expect 1'
go
select * from MyTable
go
As in the first case, we would not expect the identity value to be set at 2. Let's now insert a row using an explicit transaction:
begin transaction
insert MyTable select 'Expect 2'
rollback transaction
go
select * from MyTable
go
As we expected, only one row is in the table as this transaction was rolled back. Now is the interesting part. Let's insert a new row, which should be an identity value of 2.
insert MyTable select 'Expect 2, too'
go
select * from MyTable
go
drop table MyTable
go
Shazam!!!
Apparently even an explicit transaction does not contain any identity work. Despite the rolled back transaction, it appears the identity value is calculated outside of the transaction wrapper.
If you want to download my test code, it is available here:identity2.sql
Conclusions
Microsoft always cautions that one should not depend on the identity value to be sequential and unique. I guess this warning is given with good reason. Apparently the identity property functions outside the scope of any type of transaction. While I'm not sure how much of a bug this is, I would definitely classify it as a bug and something of which you should be aware.
As always I welcome feedback on this article using the "Your Opinion" button below. Please also
rate this article.
Steve Jones
©dkRanch.net May 2002
Return to Steve Jones Home