"serializable" isolation level not really serializable?

Hi,

In general, it's understood that the "serializable" isolation level prevents three inconsistency scenarios (dirty reads, unrepeatable reads and phantom reads) from occurring.

but the application developer's guide, in chapter3 writes:

<oracledoc><b>Referential Integrity and Serializable Transactions</b>
Because Oracle does not use read locks, even in SERIALIZABLE transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level should not assume that the data they read will not change during the execution of the transaction (even though such changes are not visible to the transaction). Database inconsistencies can result unless such application-level consistency checks are coded carefully, even when using SERIALIZABLE transactions.
</oracledoc>

I can imagine unrepeatable reads are prevented by checking control data on the re-read (e.g. transaction 1 reads data item A, transaction 2 writes data item A with a new value and commits and transaction 1 re-reads A to find that it has changed and so fails with an oracle error no). But what happens if no re-read occurs? If the first transaction commits, surely the repeatable read hasn't been achieved (even though no re-read has been done)?

Is the scenario above possible with Oracle and if so is the only way around it to use select for update? If so, aren't "select for updates" against the philosophy of optimistic locking and hence not scalable and to be avoided?

cheers,
Pak Received on Sat Jul 21 2001 - 16:30:10 CDT