sql - Excluding a table from a transaction rollback -

we have table , set of procedures used generating pk ids. table holds last id, , procedures gets id, increments it, updates table, , returns newly incremented id.

this procedure potentially within transaction. problem if have rollback, potentially rollback id before id's came use during transaction (say generated different user or thread). when id incremented again, cause duplicates.

is there way exclude id generating table parent transaction prevent happening?

to add detail our current problem...

first, have system preparing migrate lot of data into. system consists of ms-sql (2008) database, , textml database. sql database houses data less 3 days old, while textml acts archive older. textml db relies on sql db provide ids' particular fields. these fields identity pk's currently, , generated on insertion before publishing texml db. not want wash our migrated data through sql since records flood current system, both in terms of traffic , data. @ same time have no way of generating these id's since auto-incremented values sql server controls.

secondly, have system requirement needs able pull old asset out of texml database , insert sql database original id's. done correction , editing purposes, , if alter id's break relations downstream on clients system have no control over. of course issue because id columns identity columns.

procedures gets id, increments it, updates table, , returns newly incremented id

this cause deadlocks. procedure must increment , return in 1 single, atomic, step, eg. using output clause in sql server:

update ids set id = id + 1 output inserted.id name= @name; 

you don't have worry concurrency. fact generate ids way implies 1 transaction can increment id, because update lock row exclusively. cannot duplicates. complete serialization of operations (ie. no performance , low throughput) different issue. , why should use built-in mechanisms generating sequences , identities. these specific each platform: auto_increment in mysql, sequence in oracle, identity , sequence in sql server (sequence in denali) etc etc.

updated
read edit, reason why want control of generated identities able insert archived records. possible, use identity_insert:

allows explicit values inserted identity column of table

turn on when insert old record, turn off:

set identity_insert recordstable on; insert recordstable (id, ...) values (@oldid, ...); set identity_insert recordstable off; 

as why manually generated ids serialize operations: transaction generates id exclusively lock row in ids table. no other transaction can read or write row until first transaction commits or rolls back. therefore there can 1 transaction generating id on table @ moment, ie. serialization.