Re: serializable isolation level behavior question

From: Jim Kennedy <jim>
Date: Wed, 18 Oct 2006 05:58:15 -0700
Message-ID: <MN-dnWrd0IqPuavYnZ2dnUVZ_qCdnZ2d@comcast.com>

"Laurenz Albe" <invite_at_spam.to.invalid> wrote in message news:1161162187.460298_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."
> >
> > 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!

>
I don't know of any database that will serialize a transaction while another session drops the table. dropping the table requires an exclusive lock on the table. I could see the drop table being blocked but not the drop being allowed and then the other session's select returning the results. Jim

> 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 - 07:58:15 CDT