Database Serializable Isolation Level

Hi all,

I have a question on the serializable isolation levels in SQL Server 7 and Oracle 8i and also databases in general. Consider the following scenario under the serializable isolation level:

One thread updates a row in a table and does other processing. While this is happening, a second thread tries to update the same row. As the first thread has a lock on this row, the second thread blocks. Then the first thread completes the transaction and terminates. What happens next?

From my experiments, under SQL Server, the second thread can continue on happily as the row is now freed. However, under Oracle, the second thread will be returned an error stating that the transaction can no longer be serialised as the row has been updated by another transaction.

I got the following from the O'Reilly "Oracle Essentials" book, "If the transaction commits changes to the data, an operation executing with a SERIALIZABLE isolation level will return an error indicating that it cannot serialize operations. This error makes sense, because the transaction will have changed the state of the data from the beginning of the SERIALIZABLE transaction, making it impossible to perform any more write operations on the changed rows. In this situation, an application programmer will have to add logic to this or her program to return to the start of the SERIALIZABLE transaction and begin it again".

Note that no error is returned in SQL Server when the same test is executed. Why do these two databases treat the serializable isolation level in two different ways? How do other databases such as DB2, etc. deal with this?
Any help is appreciated,

Charles Gamble.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 31 2000 - 11:34:18 CDT