Re: Row locking and serializability

In terms of SQL and serialisability, yes one of them is wrong (and having read the documents suggested by previous posters) I now have to agree that Oracle does allow non-serialisable transactions when the isolation level is set to serialisable.

There is a 'work around' If you look you the SERIALIS(Z?)ABLE init.ora parameter, and set this to TRUE, then Oracle reverts to version 5 behaviour I think, and as with RDB you would not be able to insert the second row.

This plays merry hell with performance, though.

Of course, this failing in proper serialisability is a consequence of the massive performance advantage you get from Oracle's ability to keep readers and writers from blocking each other, but it does mean you have to relay on other mechanisms (e.g. integrity constraints) to avoid the side-effects of errors due to the example you quote.

Of course, the DEADLOCK message in RBD really ought to match Oracle's message - 'Unable to serialize transaction'.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

David Cressey wrote in message ...
>From the absence of further messages on this thread, I'm guessing
>the thread has pretty much played itself out. Please forgive me
>if what follows is beating a dead horse.
>
>
>user A> set transaction read write;
>user B> set transaction read write;
>user A> select count(*) from passengers;
>user B> select count(*) from passengers;
>user A> insert into passengers values (...);
>user B> insert into passengers values (...);
>user A> commit;
>user B> commit;
>
Received on Sun Jul 04 1999 - 13:29:28 CDT