April 28, 2009 at 7:59 am
We are writing and an application for a client. The application stores data in a SQL 2005 database. One of the requirements of the application is that all database writes are recorded for auditing purposes. The audit log is stored in a database table, with an identity field as the primary key. All works well, except when there is an error and a transaction rolls back. This can be replicated using the following T-SQL:
********************************************************************
CREATE TABLE #T1 ([ID] [int] IDENTITY (1,1) not null, [Value] varchar (50) not null)
insert #T1([Value]) values ('Value 1'), ('value 2')
select * from #T1
begin transaction
insert #T1([Value]) values ('Value 3')
rollback transaction
select * from #T1
insert #T1([Value]) values ('Value 3')
select * from #T1
drop table #T1
********************************************************************
In the above example, the value 'Value 3' gets an ID of 4. Is there any way to ensure that the next available ID is ALWAYS used.
We can circumvent this issue by using an int field, and doing a "select max([ID]) + 1" (see below) in order to increment the IDs, but that defeats the purpose of using the Identity. the purpose of teh Identity, is to be able to visually see if any audit records have been deleted.
********************************************************************
CREATE TABLE #T1 ([ID] [int] not null, [Value] varchar (50) not null)
insert #T1([ID], [Value]) select isnull(max([ID]), 0)+1, 'Value 1' from #T1
insert #T1([ID], [Value]) select isnull(max([ID]), 0)+1, 'Value 2' from #T1
select * from #T1
begin transaction
insert #T1([ID], [Value]) select isnull(max([ID]), 0)+1, 'Value 3' from #T1
rollback transaction
select * from #T1
insert #T1([ID], [Value]) select isnull(max([ID]), 0)+1, 'Value 3' from #T1
select * from #T1
drop table #T1
********************************************************************
Regards
Des Norton
April 28, 2009 at 8:02 am
try putting this after your rollback:
DBCC CHECKIDENT (#T1,reseed,0)
DBCC CHECKIDENT (#T1,reseed)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 8:16 am
If you have to have consecutive values then IDENTITY is NOT the way to go as it is designed to behave the way you are seeing it behave. For whatever reasons the increment of IDENTITY happens outside the transaction so it is not rolled back. You could do as Christopher suggests and if the transaction fails and rolls back used DBCC CHECKIDENT to reseed the identity value, something like this:
IF OBJECT_ID('tempdb..#test') IS NOT NULL
BEGIN
DROP TABLE #test
END
CREATE TABLE #test (id INT IDENTITY(1,1), test_col SMALLDATETIME)
BEGIN TRANSACTION good_insert
INSERT INTO #test (
test_col
) VALUES (
'2009-4-28 10:11:33.98' )
COMMIT TRANSACTION good_insert
BEGIN TRY
BEGIN TRANSACTION bad_insert
INSERT INTO #test (
test_col
) VALUES (
'1752-4-28 10:7:40.120' );
COMMIT TRANSACTION bad_insert
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION bad_insert
DECLARE @identity INT
SELECT @identity = ISNULL(MAX(Id), 0) FROM #test AS T
-- this outputs the current values
DBCC CHECKIDENT('#test')
-- this will reset the identity value and tell you the change
DBCC CHECKIDENT('#test', reseed, @identity)
END CATCH
BEGIN TRANSACTION good_insert2
INSERT INTO #test (
test_col
) VALUES (
'2009-4-28 10:11:33.98' )
COMMIT TRANSACTION good_insert2
SELECT * FROM #test AS T
The only issue I would have with this is concurrency, you could potentially have another transaction run that inserts in there somewhere as well, unless you change your Isolation Level or provide some locking hints.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 8:21 am
April 28, 2009 at 8:21 am
Hi Jack,
Yeah concurrency is a problem with the solution provided.
The DBCC reseed to 0 then a reseed will always give you the next avaible seed value.
However I am with you in terms of why you the OP has to have consecutive id's.
If the OP needs this for later creating Reference numbers or some such data, then I woudl suggest creating another table that holds you Id and then holds a consecutive running number
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 8:27 am
Christopher Stobbs (4/28/2009)
The DBCC reseed to 0 then a reseed will always give you the next avaible seed value.
Duh, I didn't know that, AND I totally misread the code.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 8:35 am
Jack, Christopher
Concurrency is an issue. The transaction is controlled by the application, and the audit table will be hit quite hard.
We will chnage the Identity field to a regular int field, and use max([ID])+1. This gives the smoke-and-mirrors that all the numbers are sequential.
Regards
Des Norton
April 28, 2009 at 8:41 am
Consider what is supposed to happen if you have concurrent transactions going on.
1. Transaction A inserts, which produces identity value 100.
2. Transaction B inserts, which produces identity value 101.
3. Transaction A gets rolled back. There is a gap now between 99 and 101.
4. Transaction C starts. You say you want 100? Transaction C now precedes transaction B based on the identity value.
So, at the very least, you would lose the sequence of the transactions. But in that case, the number is meaningless, no different than using NEWID() to assign a guid
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2009 at 8:59 am
Thanks Bob
I agree that the actual number is irrelevant. However, the customer wants sequential numbers, so that's what he gets.
Regards
Des Norton
April 28, 2009 at 9:27 am
Understood.
Just make sure you're covered when they finally notice that they can't depend on the sequence. 😉
One other thought. In 2005 and up, you could use OUTPUT with your inserts to put the identity numbers (and all the other inserted values, if desired) into a table variable. In the event of a rollback, the values in the table variable survive unchanged, and you could insert those into your audit table with a "Rolled Back" flag. That way the sequence is preserved, and there are no gaps.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply