Re: serializable isolation level behavior question

Bob Jones wrote:
> "Laurenz Albe" <invite_at_spam.to.invalid> wrote in message
> news:1161071595.908950_at_proxy.dienste.wien.at...
> > Bob Jones <email_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?

"The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable." So maybe it doesn't apply to a concurrent read committed transaction anyways. The test needs to be between two sessions with the same isolation level to have the standard apply, I believe. You are welcome to search:

http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/ansi-iso-9075-2-1999.pdf

Perhaps the standard implies a serialization failure should be returned, rather than zero rows:

"The execution of a <rollback statement> may be initiated implicitly by an SQL-implementation
when it detects the inability to guarantee the serializability of two or more concurrent SQLtransactions. When this error occurs, an exception condition is raised: transaction rollback - serialization failure."

Of course, it says "may," so maybe Oracle does follow the standard.

jg

-- 
@home.com is bogus.
http://www.newsoftheweird.com/archive/index.html