The culprit behind a TRANSACTION statement in SQL

Posted by in Sql Server category on for Advance level | Points: 250 | Views : 7272 red flag
Rating: 3.33 out of 5  
 3 vote(s)

Don't look at the title of this Article.. Really didn't get anything good to match up with the scenario so named something funny 'Culprit' :)

We are going to look at the effects of using Transaction control in a DML statement(INSERT). I am gonna explain the topic with a real-time scenario.

A small Beginning: 

One of my friend works as a Staging Analyst in an MNC. He was assigned to do some manual INSERTs into a particular table with a valid logic. Like us, he even did put his INSERT logic inside a single TRANSACTION so that if something goes wrong then Transaction should ROLLBACK. No doubt that’s a good practice for all developers. He executed his INSERT script and unfortunately the TRANSACTION rolled back due to some error. He was likely happy cause he had used the code inside a single TRANSACTION. After fixing the error, he had executed again the INSERT script successfully and then went to check the inserted data in TARGET table. He was surprised seeing the discontinuous value in the identity column. He immediately called me for help. Finally I got something to help him certainly after understanding his whole scenario.

My whole testing and resolution for the issue goes here. I have made this article so realistic to make a feel of it!


How we should handle a misfire of rollback transaction during any DML execution? A successful TRANSACTION does not harm anything but an unsuccessful one may harm to your IDENTITY column of a table. We will learn here how to overcome the situation, if a rolled back transaction affects an IDENTITY value? Delete only dissociated IDENTITY records from a table(if inserted accidentally) and then reseeding to its original value without affecting any earlier\contiguous records.

Steps to the solution: 

(Lets first create the environment)
1. Creation of an Employee table with few dummy data.

Note: EmpID is an Identity column and has increment of +1 from starting of seed value 1.
--Table creation
CREATE TABLE [dbo].[T_Emp](
	[empid] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [varchar](20) NULL,
	[FirstName] [varchar](20) NULL,
	[Location] [varchar](20) NULL,
	[ManagerID] [int] NULL,
	[obsoleted] [smallint] NULL
--Dummy data inserts
Insert Employee
	Values	('Sahoo', 'Prasad', 'Ahmedabad', 0, 0),
			('Choudhury', 'Pravas', 'Bangalore',1, 0),
			('Rout', 'Priti','Bangalore', 1, 1 ),
			('Dave', 'Ankit', 'Delhi', 1, 0),
			('Bhatt', 'Kandarp', 'Pune', 1, 0),
			('Upadhyay', 'Rushi', 'Chennai', 1, 0)

(Pic 1.1)

2. INSERT statement with a TRANSACTION control.

Suppose your HR asked you to add 3 new employee details in the Employee table. Of-course you will take those employee details except EmpId from HR department and then you will insert those manually.

Play a Game: Put the insert statement in a TRANSACTION and then ROLLBACK immediately without committing it.
--Insert 3 new Emp records as per HR's request
Begin transaction
Insert Employee
	Values	('Mehta', 'Hilor', 'Mumbai', 1, 0),
			('Tangella', 'Phani', 'Hydrabad',1, 0),
			('Puhan', 'Prabhas','Orissa', 1, 1 )
Rollback transaction;
--Forcefully rolled back the transaction
The above code looks like, no data are going to be inserted in the Employee table as it has a ROLLBACK statement at the end. Lets have a look on the execution(Pic 2.1). (I have used ROW CONSTRUCTOR method to insert the data)

(Pic 2.1)

Looks like, 3 rows are being affected by the TRANSACTION. Alright now it's time to confirm that nothing have been inserted on execution to our previous code. Lets do a 'select *' on Employee table

(Pic 2.2)

The above screen shot looks like, we are OK as of now.

Lets insert those 3 employee details given by HR dept in the Employee table and COMMIT the TRANSACTION. I have used OUTPUT clause to see the data just been inserted.
--Committed Transaction
Begin transaction
Insert Employee
output inserted.*
	Values	('Mehta', 'Hilor', 'Mumbai', 1, 0),
			('Tangella', 'Phani', 'Hydrabad',1, 0),
			('Puhan', 'Prabhas','Orissa', 1, 1 )
commit transaction;

(Pic 2.3)

(Pic 2.4)

Oh my God! what happened to the EmpId column? Not fair.. I have not inserted any data so far but why my identity column seeded?

What's the solution now? How to delete those crap records and reseed back the Identity value?

No worries my friend.. We can do that.. Just follow me and take a long breath..

If you really ask me, why this happened? Frankly I have no answer for it.. and no idea about the culprit seeding our IDENTITY after ROLLBACK.

3. SELECT and DELETE the dissociated identity records(Inserted accidentally):

Hang on before deleting any records and take a min to check your logic with SELECT.
--Logic to see the dissociated records before deleting those
select * from Employee
where empid not in 
(select distinct es.empid from Employee as ES
inner join (select *, ROW_NUMBER() over(order by empid) as NewIdentity from Employee) as ET
on es.empid = et.NewIdentity where es.empid = ET.empid

(Pic 3.1)

(EmpID 10, 11 and 12 are to be erased. Please refer to Pic 2.4) Ok, we are good to go now! Replace SELECT * with DELETE and if possible use OUTPUT before WHERE clause to see the deleted record/s from the table. This's just to confirm which and all data are being erased.

(Pic 3.2)

Thank God! Finally deleted the mismatched identity records. Now, how to reseed the IDENTITY value from the highest number availble in EmpID column?

4. RESEED of Identity value:

Oh! I got something from my own blog for this scenario. No problem I will explain that here also..

As per our scenario here, we will have to reseed the next identity value to 7 because we are well up-to EmpID 6. But our Employee table says, It has a max identity value of 12 and the next insert will be generating an IDENTITY value of 13. Lets see how can we achieve that to make it to 7 for our next INSERT?

--Manually a record have inserted with a force flag to identity
set identity_insert employee on
insert employee (empid, LastName, FirstName, Location, ManagerID, obsoleted)
values (7,'Mehta', 'Hilor', 'Mumbai', 1, 0)
set identity_insert employee off
--IDENTITY value will be reseeded here.
declare @maxid int
set @maxid = (select max(EmpID) from Employee)
dbcc checkident('Employee', reseed, @maxid);

(Pic 4.1)

OK.. we are almost done! Now lets do the work given by HR. We have already inserted an employee details manually into the Employee table. Now lets insert another two records with just simple INSERT statement. If those will have EmpID 8 and 9 respectively then we are good to go with!

(Pic 4.2)

WOW.. we are done!! 

Imp: Identity_Insert ON allows us to insert record/s manually to a table having an identity column but we will have to call the field names before values goes into it. No doubt we can use row constructor method also to insert the records but make sure to give the path\field names of the table..

Eg: Insert employee (empid, LastName, FirstName, Location, ManagerID, obsoleted) values (10, 'Desai', 'Sunil', 'Bihar', 1, 0)

Question for all:

I know, this's not just the way to achieve but there will be many ways around. If you would have in my place then how would you have done this? Still I am asking myself.. Why identity value didn't reseed back when the transaction had rolled back? If you know the answer then pls pls share it here.. Your answer will be highly appreciated ..

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Deviprasad sahoo
Member Level: Starter
Member Status: Member
Member Since: 6/8/2012 3:40:15 AM
Country: India
S Devi Prasad
currently working as ASE in an USA based health care company.

Login to vote for this post.

Comments or Responses

Posted by: Deviprasads on: 11/26/2012 | Points: 25
Why the IDENTITY property skips values after rolling back a transaction?

Guys, don't break your head anymore with Google search..

Hugo Kornelis (SQL Server MVP), Replied the answer of above question in my blog. Please refer to the comment section of below link.
Posted by: Sheonarayan on: 11/26/2012 | Points: 25
Very good article Devi, keep it up!


Login to post response

Comment using Facebook(Author doesn't get notification)