Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: serializable isolation level behavior question

Re: serializable isolation level behavior question

From: Bob Jones <email_at_me.not>
Date: Wed, 18 Oct 2006 00:16:54 GMT
Message-ID: <WneZg.14976$vJ2.12512@newssvr12.news.prodigy.com>

"joel garry" <joel-garry_at_home.com> wrote in message news:1161128322.578292.108700_at_h48g2000cwc.googlegroups.com...
>
> 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
>
Received on Tue Oct 17 2006 - 19:16:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US