Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: serializable isolation level behavior question
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." > > Are we still talking about TRUNCATE? Serializing it is like serializing > drop table. It does not make sense. I don't know what part was I not > clear about.
You have been very clear, but you are missing the point.
A serializable transaction must not get different results when issuing the same query twice. Period.
This must hold, no matter what any other transaction does, and irrespective of that other transaction's isolation level.
To be standard compliant, you must maintain serializability even if somebody else does a DROP TABLE or TRUNCATE TABLE. The fact that TRUNCATE is not part of the SQL standard has no implications in this case!
Just because Oracle does not do it doesn't mean it cannot be done. There are different approaches to a solution: a) somehow pretend to the serializable transaction that the data are still
there.
b) throw an error.
c) use shared table locks that block DML statements.
To include the usual flamebait, '(some) other database systems handle this correctly'.
Incidentally, it can be argued that Oracle handles the 'DROP TABLE' case correctly because you will get an error if you issue the same query again.
I think the whole thing is nothing to get upset about, it's just yet another case where Oracle is not standard compliant.
Yours,
Laurenz Albe
Received on Wed Oct 18 2006 - 04:03:10 CDT