Problem with auto-increment of Identity column value after rollback
Hi,

I'm currently debugging a database app, with the client running in VB on the 2008 framework, and the database running in MSSQL Server 2005
At the moment, I'm writing the Save routine, for the final stage of a single user interaction, and I've noticed something odd.
The first table I'm adding a record to has an Identity column, which is being used to generate the company's ID number for the record, and which needs to be done first so that I can associate all the other components of the record with that ID number.

The Insert on that table goes fine, but a later stage is currently failing, meaning that the Insert is rolled back.
What I'm then seeing, though, is that the Identity is still being incremented, and so if I have a failed Save for Identity 15, which then needs rolling back, the next record will go in at Identity 16, leaving a gap in the ID numbers, which I can't really have.

I've verified that this happens even with an explicit call to transaction.Rollback() for my transaction chain; even if I call Rollback() directly after a successful insert to this table, without any errors, the record gets deleted, but the Identity column's value is still incremented.


What's the solution, here? I can't really have the application roll the Identity back by itself on a bad save, that seems too risky to me, but no alternative springs to mind. Presumably I'm missing something obvious, but it seems very strange to me that the Identity column isn't being rolled back along with the rest of the transaction.

Can anyone advise me as to how best to get the behaviour I want, i.e. that no matter the circumstances of exit or error, I have an unbroken, sequential series of ID numbers to use as references?

Cheers,

KFrost
September 12th, 2008 8:57am

* Moved to Database Engine forum from SQL Tools.

This is the behaviour of IDENTITY columns and i don't think it can be changed. However, you could get the behaviour you desire by using the DBCC CHECKIDENT command which allows you to reseed an identity column based on a value of your choosing. So you could capture the value inserted and reseed the column should the transaction be rolled back.


In saying that, i'd be very careful with this as you could easily end up with duplicate values as a result of concurrency which may lead to failed inserts (if you haveunique constraint) or (worse still?) just duplicate entries.

I don't think IDENTITY is designed to guarentee sequential numbers, just that a unique value is set each time. Relying on the sequential order of values should be avoided if possible,

HTH!

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2008 10:02am

Rich is right. When a row is inserted into a table with an identity column defined, rolling back the transaction does not reset the identity column value.

Think about the case where 10 rows are created in a table from 10 different connections all within one second. If the first one is rolled back, SQL Server cannot go back and reassign the other nine rows' identity value, especially considering those values may have been used into other foreign key relationships.

If you need to guarantee no gaps in your numbering system, you'll need to roll your own solution.

HTH...

Joe

September 12th, 2008 2:19pm

Many thanks to you all.

My power at home has been out all over the weekend so I couldn't answer until I got back to work.

I eventually guessed that this was why the feature was set up in this way, but it's nice to hear it from people who know the answer.

I'm just going to reseed the column at 1 when I roll the software out. It's only an in-house database for a smallish firm, so collisions are unlikely. I'll just tell them that it's a possibility they'll have to cope with. I expect they'll be okay with it if I can pass on to them the reason you've given me.

Again, many thanks.

KF
Free Windows Admin Tool Kit Click here and download it now
September 15th, 2008 6:07am

Dear All ,

Kindly see this post

http://blog.sqlauthority.com/2014/10/08/sql-server-reset-the-identity-seed-after-rollback-or-error/

CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
-- Select Identity
SELECT IDENT_CURRENT('SampleTable')
GO
-- Reset Identity Code
BEGIN TRY
DECLARE @IdentityValue BIGINT
SELECT @IdentityValue = IDENT_CURRENT('SampleTable')
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
UNION ALL
SELECT 'FifthRow---------'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
DBCC CHECKIDENT ('SampleTable', RESEED, @IdentityValue);
END
CATCH
GO
-- Select Identity
SELECT IDENT_CURRENT('SampleTable')
GO
-- Clean up
DROP TABLE SampleTable
GO

This information will be helpful to all.

April 11th, 2015 3:20am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics