Re: Serializable transactions get rolled back while there are no othertransactions running?!

Oliver Zeigermann wrote:

> Folks!
>
> Is it possible a serializable transaction gets rolled back becasue it
> can not be serialized when there is *no other* transaction running in
> parallel?
>
> Because I get those transactions rolled back for that reason and toad
> keeps telling me there is no other connection and then no other
> transaction running on it.
>
> Is toad trustworthy? Am I going crazy?

Hi, yes it is possible. I've seen it more than once. If oracle runs out of it's per-table configured memory it needs for the bookkeeping it does during a serializable tx, then the DBMS will throw the cannot-serialize exception even if there's no other user logged into the DBMS. The solution is either to configure more of this memory for the table(s) involved, or simply not to use the serializable isolation level. In fact, oracle's implementation of serializable isolation does not offer what most people assume. Particularly, Any data that is read by a serializable tx can be altered and committed by another tx before the first one completes, and if the first tx rereads the changed data it will still see the old values, even though the real data has been changed. At the commit of this first tx there will be no indication that the data it read had already changed.
Joe Weinstein at BEA
>
> Thanks for any help (any psychiatrists around)?
>
> Oliver
Received on Thu Jun 17 2004 - 13:18:50 CDT