Re: serializable isolation level behavior question

On Oct 17, 3:42 pm, "Bob Jones" <e..._at_me.not> wrote:
> "Laurenz Albe" <inv..._at_spam.to.invalid> wrote in messagenews:1161071595.908950_at_proxy.dienste.wien.at...
>
>
>
>
>
> > Bob Jones <e..._at_me.not> wrote:
> >>> Session 2 (isolation level serializable)
>
> >>> Set transaction isolation level serializable
>
> >>> Select * from dd; (2 rows returned at this point)
>
> >>> Now truncate the table from session1:
> >>> Truncate table dd; (issue implicit commit)
>
> >>> Now again run the following select from session 2:
>
> >>> Select * from dd; (This should still shows 2 rows as
> >>> read data from rollback segments since in serializable mode, it need to
> >>> show the pre-image before the truncate but it is showing 0 rows)
>
> >>> Is this expected behavior that in second session it returns 0 rows and
> >>> not 2 rows?
>
> >> Yes, truncate does not generate any undo. In this case, you should use
> >> delete instead.
>
> > True, that explains why anybody who knows Oracle could expect this
> > behaviour.
>
> > But that is not standard compliant, is it?What standard? What does the standard say?- Hide quoted text -- Show quoted text -

What happens when you try to commit the serializable tx? If it succeeds,
then that's seriously wrong. However, serializable isolatiion level includes
the repeatable-read isolation level which guarantees that such a transaction
will always get the same result for the same query, if repeated, for the life
of the current transaction, and this example seems to show a failure in that
already. I would expect oracle to either retain and return the same query
results or throw a 'cannot serialize' exception during the repeat query.

Joe Weinstein at BEA Systems Received on Tue Oct 17 2006 - 18:19:11 CDT