Re: serializable isolation level behavior question

Laurenz Albe wrote:
>
> 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'.

Looks like Mr. Kyte is going to have to update a chapter or two from his latest book.

Has anyone submitted this case over to asktom yet?

>
> 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.
>

It is interesting. The whole TRUNCATE thing is so far outside the standards that it's more of an academic question than one that will affect many important online applications.

If you are designing a system with serializable transactions against tables that are truncated more often than outside of batch only processing periods you have more than one set of issues to contend with from the get go. Received on Wed Oct 18 2006 - 07:14:55 CDT