Showing posts with label rollback. Show all posts
Showing posts with label rollback. Show all posts

Wednesday, March 7, 2012

identity column rollback?

I am using a stored procedure to insert data to a table.

If there is any error then i rollback the transaction. this works fine.

but the identity column gets incremented, i dont want any of the values to be skipped due to an error as that number has to be accounted for.

do you know anyway in which this is possible to rollback the identity value from the DB?nihar,

I don't know your stored procedure processing fully, but I think using DBCC CHECKIDENT() will help you out.

Depending on what the current identity value is for the table in question in relation to any record gaps, you may want to examine IDENT_CURRENT() too.

Check BOL for details...

Hope this helps!

Kael|||thanks Kael,

that worked fine. dbcc checkident

heres what i was doing

.
.
begin transaction
insert into sometab values (somevalues)
set @.outparam = @.@.identity

insert into someother tab values (@.outparam...)
if @.@.error <> 0
begin
rollback transaction
dbcc checkident('sometab') --this is what i have added now
end
else
begin
commit transaction
end

this i have done as due to rollback the identity should not have increased.

thanks again.|||Hmmm...that's funny. I'm trying to image by looking at your code how the identity would increase, even though the transaction is being rolled back, but I can visualize it. Oh well. Looks like that DBCC command worked for you, so I'd quit while I'm ahead!

Kael|||hey it increase the value as soon as i insert into the table.

try this:
begin trans
insert into a table with identity column
select @.@.identity
rollback
insert again
check identity column it will have increased skipping the one which rolled back.

if u want i can give u the entire stored proc attached: its 200 odd lines :D|||Identity is designed for multitasking.
If you insert values 1-3, others can insert 4-5. If you rollback and they don't, they must have values 4-5. So you must write your own multitasking code for values without gaps.

Good luck!|||If that is the case then what is the purpose of identity.

Can you tell me what will happen if i insert values 1-3 then rollback, others insert 4-5 and save.
after rollback i call the 'dbcc checkident' proc. what happens then? Is it correct to do that?

if not proper what alternatives do I have to consider?|||You can use dbcc checkident reseed, but only if you are ADMISTRATOR.
No user can use it even by trigger. I recomend something like SP with

begin tran
INSERT TABLEX(XID)
select max(XID)+1
from TABLEX (XLOCK)
.
.
.
if ...
ROLLBACK
else
COMMIT

I am not sure about the level of locking used. I cannot use BOL now.

Good luck!|||Tell me what happens when u lock the insert max, and someone else call the max of whatever.. if u get 3, he will also get 3 since u havent committed yet.

in this case what happens, you have to trap a primary key violation and call insert again?

u will have to lock the table in that case..

or what else?|||He must wait, the same for dbcc checkident.|||it Wouldnt be ideal as conflicts will also arise when someone is editing the table.

where time would be the essence this wont really work. i have seen it happen.. even though the lock is for the minimal of time, any procedure which has to wait for another to release isnt the ideal construct.|||A. 1, 2, 3 -> one user or else high locking
B. 1, 7, 45 -> an identity and no special problems

You must decide. Sometimes you must choose A :)

Sunday, February 19, 2012

identity and rollback

This is more of an academic question and it related to SQL Server 2000.
I have a table that has an identity column. If i try to insert a row and
decide to roll back the transaction, the next insert will have a gap in the
numbering sequence of the identity column.
Is there a way to avoid the gap in the numbering?
Thanks.
ShahriarUnfortunately there is no way to avoid these gaps using an identity column.
You would have to implement your own solution according to your requirements
.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Shahriar" wrote:

> This is more of an academic question and it related to SQL Server 2000.
> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in th
e
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
> Thanks.
> Shahriar|||> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in
> the
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
Yes, don't use IDENTITY.
The advantage of IDENTITY is that it does not lock the entire table to
insert a row. You get "assigned" a value when you start your insert, and
since others may start an insert after that (but before you commit or
rollback), SQL Server has no choice but to resign your number as "taken"...
Why do you care about gaps?
The IDENTITY value itself has no real meaning - after all, the system
generated it for you. So, if you have a rollback, what does it mean, and to
whom, that there is no row where the surrogate identifier is = 4?
http://www.aspfaq.com/2523