The possibility of this happening seems extremely unlikely to me because of the problems it could cause, but I figured I'd ask the question anyway...
Imagine a transaction where an autoincrement ID is involved and a value is assigned. Prior to COMMIT, the involved code caches a copy of the assigned ID for later reference. Then the transaction is committed.
Assuming no direct client intervention (deletion or altering of the record), is there any database or situation that would ever automatically alter the ID value immediately upon COMMIT, making the cached ID incorrect? Is it always safe to cache the ID mid-transaction?
One hypothetical case where I can imagine this happening is if some RDBMS implementation inexplicably decided it was necessary to have gapless and time-dependent autoincrement values (since I see many examples of people wanting this). In this hypothetical case, I can imagine some magical shuffling of IDs might be done to fill in gaps caused by post-ID-assignment rollbacks in another transaction (or other gap causer). This would invalidate the cached value.
Anyone know of such an implementation, or other cache killer?
The implementation of generated id values usually involves incrementing a counter value in a short atomic operation. This value is then used for by the requesting transaction and even if that transaction would roll back, the reserved value will never be given back to the pool of free values. So in this light I dont think the situation described is very likely. Also, in pl/sql type of programs you really do need the generated value to be right in order to insert other dependent rows to child tables.
As for the people who are wanting time-ordered gapless id values: the sole purpose of autoincrement/surrogate key is to create an artificial identification for a row. It should have nothing to do with determining the order in which rows were created. There are far better ways to do this, for example using a creation timestamp.