Hugo Kornelis (10/11/2012)
L' Eomot Inversé (10/11/2012)
the consequences for concurrency and performance of requiring the seed to be rolled back would be intolerable.
That's not even the biggest issue.
It's big enough to kill the idea of restoring seeds, though.
Consider this scenario:
Transaction 1 starts, inserts new row. Identity value used = 12345.
Transaction 2 starts, inserts new row. Identity value used = 12346.
Transaction 1 rolls back.
Transaction 2 inserts two more rows. What identity values to expect?
That's an illustration of why we would have to do something quite unpleasant to resolve the issues with restoring the seed. Actually, it's an overcomplicated illustration: no need to consider further work in Transaction 2, it's bad enough if it just commits its single update. I don't believe there's a reasonable solution. There are at least two unreasonable solutions (which I'm sure would never be implemented):
(i) Transaction 2 is blocked when it attempts to insert, and remains blocked until Transaction 1 commits or rolls back;
(ii) Transaction 2 is aborted immediately when transaction 1 rolls back; If Transaction 1 doesn't roll back, Transaction 2 is not committed until Transaction 1 commits; if Transaction 2 tries to commit it blocks, waiting for transaction 1.; if Transaction 1 rolls back Transaction 2 is rolled back.
In either case, we don't need an answer to your question - with (i) because Transaction 2's insert doesn't happen until after Transaction 1 commits or rolls back, with (ii) because Transaction 2 never gets to its second insert (it rolls back when Transaction 1 rolls back).
Of course we would have to do some things with dbcc checkident too.
(i) would be reasonably simple to implement (although a lock that blocks inserts but not updates would be new), (ii) rather less so (one can end up with an arbitrarily large group of transactions all of which must commit or roll back together).
The consequences of implementing either (i) or (ii) in order to allow the seed to be restored are what I consider intolerable.
Fortunately we don't have any of this nonsense - the seed is not part of the state that has to be restored by rollback.