Sql-server – How to rollback the identity seed after deadlock

deadlockidentityrollbacksql servertransaction

Now that's an approximate sequence of operations Im performing:

SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
SELECT @oldID = <some simple logic here>
DBCC CHECKIDENT ('<table-name>', RESEED, @oldID) WITH NO_INFOMSGS;
SET IDENTITY_INSERT <table-name> OFF;

So, as you see, everything's simple: basically, I insert record with explicit ID, then I want to return the identity seed back to its previous value (because the insert will obviously shift it).

I need this kind of strange logic (from the first POW) becase I can have ID-s in a large range (billions of rows) of values where the first digit has some special meaning.

For instance, I have current identity seed 1000007777. Then I want to insert a record with ID 2000007777 (this will shift identity to 2000007778). But I want to turn it back to 1000007777, because I believe that I will never reach the point when an identity collision will occur (I just don't operate such volumes of data). As I said, the first digit serves special purposes.

Now, the problem is, if I rollback the transaction after insert, the identity will not get rolled back (as i got it, that's the design of SQL server, and I believe many DB providers).

I can handle this manually if I am the one who conciously performes the rollback.
But what if there is deadlock (or some error) right at the point before reseed?

How can I handle that (except handling the exception and performing unconditional reseed)? Any ideas?

Best Answer

I believe your best bet is going to be to use a TRY-CATCH. I've done two things here. First declare and get your "oldID" before your transaction begins. This will help to keep your transaction time down and avoid deadlocks as much as possible. Second I've put the "core" of the rest of it into a TRY block and then in the CATCH block put the reseed again. This way regardless of the error you will re-set the seed. I believe this will work even in a deadlock situation.

DECLARE @oldID bigint
SELECT @oldID = MAX(id) from identtest where id<3000

BEGIN TRANSACTION

BEGIN TRY
    SET IDENTITY_INSERT identtest ON;
    INSERT INTO identtest (id, x) VALUES (4000,'y');
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
    SET IDENTITY_INSERT identtest OFF;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

DBCC CHECKIDENT ('identtest', NORESEED)

All of that being said you may be better off switching your logic around a bit. Would it be possible for you to set your "normal" inserts to start at say 20000000 and then your IDENTITY_INSERTS to start at 10000000? This way regardless of your insert the seed will stay at the larger "normal" value. It sounds like you are a bit far in to do this, but you never know.

Related Question