Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26

    Unanswered: 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?

  2. #2
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76

    Lightbulb Try using DBCC CHECKIDENT() and IDENT_CURRENT()

    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

  3. #3
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    thanks Kael,

    that worked fine. dbcc checkident

    heres what i was doing

    Code:
             .
             .
             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.

  4. #4
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    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

  5. #5
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    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

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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!

  7. #7
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    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?

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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!

  9. #9
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    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?

  10. #10
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    He must wait, the same for dbcc checkident.

  11. #11
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    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.

  12. #12
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •