March 14, 2008 at 9:30 am
I have a table, having an identity column. This table is a part of the transaction block. When a rollback happens, though the row insert is rolled back, the identity column value does not get rolled back.
Following is the code sample.
---Table code
CREATE TABLE [dbo].[ErrorTestDataTable]
(
[ColA] [int] IDENTITY(1,1) NOT NULL,
[ColB] [tinyint] NULL,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
[ColA] ASC
)
----------------------------------
---Code in the StoreProc.
CREATE PROCEDURE [dbo].[AddData]
AS
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
insert into ErrorTestDataTable (colB) values (3)
insert into ErrorTestDataTable (colB)
values (56565656532424234)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
select error_number(),error_message()
END CATCH
--======================
Order of execution.
-------------------
insert into ErrorTestDataTable (colB) values (1)
insert into ErrorTestDataTable (colB) values (2)
--Two rows are inserted with identity column incremented
--accordingly. ie CoLA has values 1 and 2.
--======executing the store proc having an error statment.
exec AddData
--The procedure has an error insert statment, which causes an
--arithamatic overflow error.
--Transaction is rolled back.
insert into ErrorTestDataTable (colB) values (3)
--Tried to insert a correct row.
--==============================
When another insert statement is executed,
the value for the identity column is 5, rather than 3.
Is identity column value not a part of transaction..??
How can i handle this, and avoid breaks in my identity column sequence.
March 14, 2008 at 9:47 am
Even though the transaction has been rolled back, the seed is still altered. You would have to reseed to get a consistant flow of ids.
This is the code to reset the seed. You can test by adding this after the failed execution of the stored procedure
DBCC CHECKIDENT ('ErrorTestDataTable', RESEED, 2)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply