Re: serializable isolation level behavior question

"joeNOSPAM_at_BEA.com" <joe.weinstein_at_gmail.com> wrote in message news:1161127151.363381.11970_at_b28g2000cwb.googlegroups.com...
>
>
> 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
>

The problem is that truncate is not a DML. You cannot serialize it. Use delete instead. Received on Tue Oct 17 2006 - 18:28:51 CDT