|
A client wants us to create functionality in SQL Server to mimic something Oracle is better at.
When you execute SQL code inside a transaction with serializable as the isolation level, that means you would get the same, predictable results. Even though the database is multithreaded, serializable transactions act like there is only one CPU that completes one transaction before starting the next. There are no phantoms and there are no non repeatable reads.
But Oracle takes this a step further. What they say is logically true, but I can't see how it matters? When you use an identity column (Access calls these "AutoNumber") that generates sequential numbers, this sequence isn't protected from other transactions in SQL Server or MS Access, but it is in Oracle. If you insert a row in a transaction that gets rolled back, whatever ID value it gets becomes a black hole. If it would be #3, then row #3 is destroyed, and you might have #1, #2, and #4, but never a #3. This can lead to "data islands" and they want to avoid that.
Now this is a client that pays their bills on time, so apparently we don't ask questions why they want something. It won't be that difficult to build, there will be a tracking table of ID sequence values and their status, and some stored procedures to manage that, to generate a new value, and so on. And then they'll be able to generate sequence numbers in a serial seeming way that will be isolated from other transactions without breaking uniqueness in table values.
But why on earth is that desirable?
|
