Excluding a table from a transaction rollback?

This will cause deadlocks. Procedure must increment and return in one single, atomic, step, eg. By using the OUTPUT clause in SQL Server.

Procedures gets the id, increments it, updates the table, and then returns the newly incremented id This will cause deadlocks. Procedure must increment and return in one single, atomic, step, eg. By using the OUTPUT clause in SQL Server: update ids set id = id + 1 output inserted.Id where name= @name; You don't have to worry about concurrency. The fact that you generate ids this way implies that only one transaction can increment an id, because the update will lock the row exclusively.

You cannot get duplicates. You do get complete serialization of all operations (ie. No performance and low throughput) but that is a different issue.

And this why you should use built-in mechanisms for generating sequences and identities. These are specific to each platform: AUTO_INCREMENT in MySQL, SEQUENCE in Oracle, IDENTITY and SEQUENCE in SQL Server (sequence only in Denali) etc etc. Updated As I read your edit, the only reason why you want control of the generated identities is to be able to insert back archived records. This is already possible, simply use IDENTITY_INSERT: Allows explicit values to be inserted into the identity column of a table Turn it on when you insert back the old record, then turn it back off: SET IDENTITY_INSERT recordstable ON; INSERT INTO recordstable (id, ...) values (@oldid, ...); SET IDENTITY_INSERT recordstable OFF; As for why manually generated ids serialize all operations: any transaction that generates an id will exclusively lock the row in the ids table.No other transaction can read or write that row until the first transaction commits or rolls back.

Therefore there can be only one transaction generating an id on a table at any moment, ie. Serialization.

This is some good information, particularly considering our current procedure is not atomic. I am not sure I understand the serialization issues you mentioned. The problem with using the built in mechanisms is that they offer no control on when to generate an id outside of a row insertion.

I upvoted the your answer, and would love to mark it as the answer, but I am not sure it really addresses the intial question, or offers a solution to the issue at hand. I have also expanded the question to add more details to the problem. – swestner May 11 at 13:52 Turning off the Identity Insert only solves the second part of the problem.

There is still the issues dealing with the migration and the need for migrated data to obtain valid id's without having to go through the system. It is too bad we are not using denali, since sequences would be ideal for this. MS workarounds look like this - blogs.msdn.Com/b/sqlcat/archive/2006/04/10/….

– swestner May 12 at 14:07 All that being said, your info lead to a solid workaround, so thanks! – swestner May 12 at 14:36.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.