serializable isolation level behavior question

[harvi...@gmail.com]

Hi,

We are testing concurrency performance and trying to isolate the issue
using sqlplus and some dummy tables and need some clarification.
Following is the scenario:

Session 1 (isolation level read committed)

Insert into dd values(1);
Commit;
Insert into dd values(2);
Commit;

Session 2 (isolation level serializable)

Set transaction isolation level serializable

Select * from dd; (2 rows returned at this point)

Now issue another insert from session 1
Insert into dd values(3);
Commit;

Now run the select again from session2:
Select * from dd; (still return 2 rows)

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?

Thanks
--Harvinder

[joe.we...@gmail.com]

Hi, no, I would not have expected that. Serializable isolation
encompasses repeatable reads...
Joe

[Bob Jones]

<harvi...@gmail.com> wrote in message
news:1161031234.4...@m7g2000cwm.googlegroups.com...

Yes, truncate does not generate any undo. In this case, you should use
delete instead.

[Laurenz Albe]

Bob Jones <em...@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?

Yours,
Laurenz Albe

[Bob Jones]

"Laurenz Albe" <inv...@spam.to.invalid> wrote in message
news:11610715...@proxy.dienste.wien.at...

What standard? What does the standard say?

[joe.we...@gmail.com]

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

[Bob Jones]

"joeN...@BEA.com" <joe.we...@gmail.com> wrote in message
news:1161127151....@b28g2000cwb.googlegroups.com...

The problem is that truncate is not a DML. You cannot serialize it. Use
delete instead.

[joel garry]

"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

[Bob Jones]

"joel garry" <joel-...@home.com> wrote in message
news:1161128322.5...@h48g2000cwc.googlegroups.com...

[Bob Jones]

"joel garry" <joel-...@home.com> wrote in message
news:1161128322.5...@h48g2000cwc.googlegroups.com...
>

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.

[HansF]

On Tue, 17 Oct 2006 07:53:17 +0000, Laurenz Albe wrote:

>
> But that is not standard compliant, is it?
>

When did the truncate command become part of the SQL standard?

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

[Laurenz Albe]

Bob Jones <em...@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

[hpuxrac]

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.

[Jim Kennedy]

"Laurenz Albe" <inv...@spam.to.invalid> wrote in message

news:11611621...@proxy.dienste.wien.at...

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

[joe.we...@gmail.com]

On Oct 17, 6:07 pm, HansF <Fuzzy.Greybe...@gmail.com> wrote:
> On Tue, 17 Oct 2006 07:53:17 +0000, Laurenz Albe wrote:
>
> > But that is not standard compliant, is it?
>
>When did the truncate command become part of the SQL standard?

The issue at hand is not whether truncate is part of standard
SQL. The issue is that if a standard SQL client is doing a
serializable transaction*, and some other client does a truncate
or anything else, standard or not, should the tx client expect oracle
to either deliver on the specified isolation level guarantees or notify
the tx client of a failure? Is it acceptable that Oracle allow a silent
failure of the tx? As described, if a serializable tx gets different
results for repeats of the same query, that is already a silent
failure.

Joe Weinstein at BEA Systems

* (which does include a guarantee of repeatable reads)

[DA Morgan]

I would question the reason for would allowing two different isolation
levels within a single application. What is the business case?
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[joe.we...@gmail.com]

On Oct 18, 10:07 am, DA Morgan <damor...@psoug.org> wrote:

> joeNOS...@BEA.com wrote:
>
> > On Oct 17, 6:07 pm, HansF <Fuzzy.Greybe...@gmail.com> wrote:
> >> On Tue, 17 Oct 2006 07:53:17 +0000, Laurenz Albe wrote:
>
> >>> But that is not standard compliant, is it?
> >> When did the truncate command become part of the SQL standard?
>
> > The issue at hand is not whether truncate is part of standard
> > SQL. The issue is that if a standard SQL client is doing a
> > serializable transaction*, and some other client does a truncate
> > or anything else, standard or not, should the tx client expect oracle
> > to either deliver on the specified isolation level guarantees or notify
> > the tx client of a failure? Is it acceptable that Oracle allow a silent
> > failure of the tx? As described, if a serializable tx gets different
> > results for repeats of the same query, that is already a silent
> > failure.
>
> > Joe Weinstein at BEA Systems
>
> > * (which does include a guarantee of repeatable reads)
> I would question the reason for would allowing two different isolation
> levels within a single application. What is the business case?
> --
> Daniel A. Morgan

Hi Dan. I didn't notice where anyone was limiting to one application
or specifying multiple isolation levels. Let us posit one application
that hopes to use Oracle's serializable isolation level, and one
rogue/bumbling admin that mistakenly truncates a table. Should
Oracle behave as claimed for the application's serializable tx?
Joe

[joe.we...@gmail.com]

On Oct 18, 10:39 am, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
wrote:

> On Oct 18, 10:07 am, DA Morgan <damor...@psoug.org> wrote:
>
>
> > joeNOS...@BEA.com wrote:
>
> > > On Oct 17, 6:07 pm, HansF <Fuzzy.Greybe...@gmail.com> wrote:
> > >> On Tue, 17 Oct 2006 07:53:17 +0000, Laurenz Albe wrote:
>
> > >>> But that is not standard compliant, is it?
> > >> When did the truncate command become part of the SQL standard?
>
> > > The issue at hand is not whether truncate is part of standard
> > > SQL. The issue is that if a standard SQL client is doing a
> > > serializable transaction*, and some other client does a truncate
> > > or anything else, standard or not, should the tx client expect oracle
> > > to either deliver on the specified isolation level guarantees or notify
> > > the tx client of a failure? Is it acceptable that Oracle allow a silent
> > > failure of the tx? As described, if a serializable tx gets different
> > > results for repeats of the same query, that is already a silent
> > > failure.
>
> > > Joe Weinstein at BEA Systems
>
> > > * (which does include a guarantee of repeatable reads)
> > I would question the reason for would allowing two different isolation
> > levels within a single application. What is the business case?
> > --

> > Daniel A. MorganHi Dan. I didn't notice where anyone was limiting to one application

> or specifying multiple isolation levels. Let us posit one application
> that hopes to use Oracle's serializable isolation level, and one
> rogue/bumbling admin that mistakenly truncates a table. Should
> Oracle behave as claimed for the application's serializable tx?

> Joe-

Ie: tx starts, reads table, admin truncates table, tx rereads table...

[Martin T.]

DA Morgan wrote:
>
> ...

>
> I would question the reason for would allowing two different isolation
> levels within a single application. What is the business case?
>

I used serializable/read-only once for single-point-in-time reporting
of data that was collected via a rather complicated PL/SQL ...

[DA Morgan]

Bumbling or not ... if that admin truncates a production table while
people are using the app I would expect that to be their last day of
employment.

You are correct no one limited it to a single app. But if it was two
different apps I would expect that they would never be hitting the
same objects.

[Jonathan Lewis]

"Laurenz Albe" <inv...@spam.to.invalid> wrote in message

news:11611621...@proxy.dienste.wien.at...

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

The TRUNCATE looks like a case of "no-one thought
of that one". I'd raise an SR with Oracle with the argument
that it's returning the wrong result - that's always a high
priority. They may, of course, give you the workaround
that you should 'delete table' regardless of the performance
impact.

I'm not sure that you're right about there being a requirement
for serialisability being maintained across a schema change though,

--
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

[Bob Jones]

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

Again, truncate is not a query, it is not even a dml.

> This must hold, no matter what any other transaction does, and
> irrespective
> of that other transaction's isolation level.
>

That's true for things like select, update or delete.

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

Show me one database that can serialize truncate.

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

Which one is standard compliant?

> To include the usual flamebait, '(some) other database systems handle this
> correctly'.
>

Which ones?

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

I would love to see it if anyone can serialize TRUNCATE in any database.

[hast...@hotmail.com]

> > A serializable transaction must not get different results when issuing the
> > same query twice. Period.
> >
>
> Again, truncate is not a query, it is not even a dml.
>

I really cannot get your point, Bob...

The OP transaction performed casher DML statements.
It had transaction level serializable.
But nevertheless got phantom reads generated by the concurrent
execution of *another* transaction.

That *other* transaction indeed happens to be the single
DDL statement "truncate". However I cannot see anywhere the
restriction that a serializable transaction is not protected against
ddl
statements performed by other sessions.

Now, I didnt look very hard, because the fine points dont really matter
:
*** If a casher serializable transaction can see phantom reads, then
the
whole point of that isolation level is lost. ***

I would expect Oracle to at least generate some error "cannot
serialize",
from a QOI point of view.

(Not that I really care. Never used serializable :-)

[joe.we...@gmail.com]

> > JoeBumbling or not ... if that admin truncates a production table while

> people are using the app I would expect that to be their last day of
> employment.
>
> You are correct no one limited it to a single app. But if it was two
> different apps I would expect that they would never be hitting the
> same objects.
> --
> Daniel A. Morgan
> University of Washington

You're welcome! I think we agree that idiocy or malice is required to
trigger this issue.
To narrow back in on the point, it is still something I would report
to Oracle for them to harden their transaction-safety in this regard.
The innocent ongoing serializable transaction should not be allowed
to silently corrupt. If a drunk drives his SUV the wrong way onto the
freeway, he is fully responsible for the results, but if he should hit
another SUV, the occupants of this second SUV should rightly
expect that at least their seatbelts and airbags will work as
advertised.
The SUV vendor would certainly be very interested in preventing any
circumstance where an accident could occur without the car's safety
devices deploying or working when they could have helped.

[vc]

That a prudent choice especially taking into account that Oracle does
not support SERIALIZABLE even with ordinary DML statements. Oracle's
SERIALIZABLE is a misnamed SNAPSHOP.

[hpuxrac]

Bob Jones wrote:
> >> 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.
> >
>
> Again, truncate is not a query, it is not even a dml.
>

Why not go back and read the original posting and the questions posed
in it instead of repeatedly posting items not related?

The query was a SELECT in the serializable transaction.

After that you go off on some kind of unrelated rant.

[DA Morgan]

joeN...@BEA.com wrote:

Bob Jones is pointing out, correctly, that TRUNCATE is DDL: Not DML.

Given that he is correct about this then I would expect that the
behaviour given a truncate should be the same as that from DROP TABLE.
With that consideration how do you feel about what you are observing?

--
Daniel A. Morgan
University of Washington

[joe.we...@gmail.com]

On Oct 19, 9:20 am, DA Morgan <damor...@psoug.org> wrote:

> > Joe Weinstein at BEA SystemsBob Jones is pointing out, correctly, that TRUNCATE is DDL: Not DML.

>
> Given that he is correct about this then I would expect that the
> behaviour given a truncate should be the same as that from DROP TABLE.
> With that consideration how do you feel about what you are observing?
> --
> Daniel A. Morgan

Well, I suspect that the OP wouldn't care if the miscreant used
DDL, DML, or DDT. As long as it's a public Oracle API, the question
is whether Oracle should react by terminating the unsuspecting and
innocent application's transaction in a visible, atomic way rather than
permit it to continue/fail silently. In fact, the DBMS needn't fail the
tx
instantly during the second read. As far as I would say, if the DBMS
simply ensures that the serializable tx fails later during commit
(preferably
with a clear message about someone DDL'ing away their data) there
is nothing for Oracle to fix.

Joe

[joel garry]

My opinion at this point is that Oracle should fail or lock out the DDL
with a
"Hey, someone is using this stuff in a transaction!" since that gives
the admin
the motivation to check out who.

Waiting for DML to fail during the commit - man, that could be way in
the future.
So could a rollback.

Then again, maybe flashback could be physically used by the DML to
finish the transaction,
or reading out of the recycle bin if enabled? So many possibilities...
so many
performance problems.

jg
--
@home.com is bogus.

Imagine possibilities! http://catless.ncl.ac.uk/Risks/24.45.html#subj9

[Bob Jones]

<hast...@hotmail.com> wrote in message
news:1161242937.7...@k70g2000cwa.googlegroups.com...

>> > A serializable transaction must not get different results when issuing
>> > the
>> > same query twice. Period.
>> >
>>
>> Again, truncate is not a query, it is not even a dml.
>>
>
> I really cannot get your point, Bob...
>
> The OP transaction performed casher DML statements.
> It had transaction level serializable.

No, the session that was serializable was doing a truncate that is NOT a
DML.

> But nevertheless got phantom reads generated by the concurrent
> execution of *another* transaction.
>
> That *other* transaction indeed happens to be the single
> DDL statement "truncate". However I cannot see anywhere the
> restriction that a serializable transaction is not protected against
> ddl
> statements performed by other sessions.
>

Again back to the beginning, truncate does not write to rollback. How do you
get the pre-image?

> Now, I didnt look very hard, because the fine points dont really matter
> :
> *** If a casher serializable transaction can see phantom reads, then
> the
> whole point of that isolation level is lost. ***
>
> I would expect Oracle to at least generate some error "cannot
> serialize",
> from a QOI point of view.
>
> (Not that I really care. Never used serializable :-)
>

That is if you consider a DDL which cannot be rolled back, a transaction.

[Bob Jones]

"hpuxrac" <johnb...@sbcglobal.net> wrote in message
news:1161275070.3...@m7g2000cwm.googlegroups.com...

>
> Bob Jones wrote:
>> >> 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.
>> >
>>
>> Again, truncate is not a query, it is not even a dml.
>>
>
> Why not go back and read the original posting and the questions posed
> in it instead of repeatedly posting items not related?
>

Please do.

> The query was a SELECT in the serializable transaction.
>

No, session 1 was not set to serializable, but that is not the point anyway.

> After that you go off on some kind of unrelated rant.
>

Please show some prove.

[hast...@hotmail.com]

Bob Jones wrote :

> <hast...@hotmail.com> wrote in message

> >
> > The OP transaction performed casher DML statements.
> > It had transaction level serializable.
>
> No, the session that was serializable was doing a truncate that is NOT a
> DML.
>

Well, the way I read the OP description copied below,
the serializable transaction (in session 2) issued only selects.

----------------------------------------------------------------------------------------------------------

Session 1 (isolation level read committed)

Insert into dd values(1);
Commit;
Insert into dd values(2);
Commit;

Session 2 (isolation level serializable)

Set transaction isolation level serializable

Select * from dd; (2 rows returned at this point)

Now issue another insert from session 1
Insert into dd values(3);
Commit;

Now run the select again from session2:
Select * from dd; (still return 2 rows)

[Martin T.]

*I* feel that this should be considered a bug:

Compare 1,2,3,4,5,6A,7
with 1,2,3,4,5,6B,7
(Oracle 9i2)

cheers,
Martin

-- SESSION 1

-- 1)
create table test_table as
select object_id myid, object_name myname from all_objects
where rownum < 100;

-- 2)
select count(*)
from test_table;
-- ... 99

-- 3)
commit;

-- 6 A)
drop table test_table;

-- 6 B)
truncate table test_table;

-- *********************************

-- SESSION 2

-- 4)
commit

ALTER SESSION set isolation_level=serializable

--5)
select count(*)
from test_table
-- ... result = 99

-- 7)
select count(*)
from test_table
-- A) ... ORA-00942: table does not exist
-- B) ... result = 0 !!

[joe.we...@gmail.com]

On Oct 19, 11:46 pm, "Martin T." <bilbothebagginsb...@freenet.de>
wrote:
> DA Morgan wrote:

> > With that consideration how do you feel about what you are observing?*I* feel that this should be considered a bug:

I agree with you, and I suggest you open a
TAR/CR with oracle support and/or get Tom
Kyte to discuss the issue.
You can simplify the issue by simply stating that
there is a table with rows, and that one session
truncates the table while another is doing a
serializable tx and has already read from the
table. A second read should either fail or get the
same results as the first but it doesn't.

[Bob Jones]

<hast...@hotmail.com> wrote in message
news:1161323230.9...@e3g2000cwe.googlegroups.com...

>
> Bob Jones wrote :
>
>> <hast...@hotmail.com> wrote in message
>> >
>> > The OP transaction performed casher DML statements.
>> > It had transaction level serializable.
>>
>> No, the session that was serializable was doing a truncate that is NOT a
>> DML.
>>
>
> Well, the way I read the OP description copied below,
> the serializable transaction (in session 2) issued only selects.
>
>

Okay, I got it backwards, but it still makes no difference to the argument.

[Bob Jones]

"Martin T." <bilbotheb...@freenet.de> wrote in message
news:1161326760.9...@m73g2000cwd.googlegroups.com...

I can't drive my car on water. There must be a bug.

For the last time, if you want this to work, either use delete or table
lock. Serializable does not affect DDLs.

Don't complain to the manufacturer if your SUV cannot run on water.

[HansF]

On Sat, 21 Oct 2006 00:34:22 +0000, Bob Jones wrote:

>
>>
>> *I* feel that this should be considered a bug:
>>
>
> I can't drive my car on water. There must be a bug.
>

You mean you didn't check the specs before you bought it? <g>

[DA Morgan]

I have to confess I too am shaking my head in amazement.

Hopefully not betraying my ignorance but my understanding is the same as
yours. I can't understand why anyone would use DDL and expect it to
behave transactionally.

--
Daniel A. Morgan
University of Washington

[xho...@gmail.com]

Laurenz Albe <inv...@spam.to.invalid> wrote:

> Bob Jones <em...@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?

Does the standard specify whether the *DDL* of other connections are
allowed to silently breach the serializability of an unrelated transaction?

Anyway, there are more serious ways in which Oracle's transaction isolation
level serializable are not truly serializable.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

[xho...@gmail.com]

DA Morgan <damo...@psoug.org> wrote:
>
> I have to confess I too am shaking my head in amazement.
>
> Hopefully not betraying my ignorance but my understanding is the same as
> yours. I can't understand why anyone would use DDL and expect it to
> behave transactionally.

The person who wanted the transactionality is not the person who issued the
DDL. A *different* person issued the DDL.

[Serge Rielau]

DA Morgan wrote:
>> For the last time, if you want this to work, either use delete or
>> table lock. Serializable does not affect DDLs.
>>
>> Don't complain to the manufacturer if your SUV cannot run on water.
>
> I have to confess I too am shaking my head in amazement.
>
> Hopefully not betraying my ignorance but my understanding is the same as
> yours. I can't understand why anyone would use DDL and expect it to
> behave transactionally.

Proof how value systems depend on ones upbringing.
I'm not sure what is more interesting about this thread.
The facts I'm learning or the reaction by the experts.
Either way a very interesting read...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

[hast...@hotmail.com]

Bob Jones wrote :

> >> >
> >> > The OP transaction performed casher DML statements.
> >> > It had transaction level serializable.
> >>
> >> No, the session that was serializable was doing a truncate that is NOT a
> >> DML.
> >>
> >
> > Well, the way I read the OP description copied below,
> > the serializable transaction (in session 2) issued only selects.
> >
> >
>
> Okay, I got it backwards, but it still makes no difference to the argument.

It changes a lot, in my opinion, Bob.

Let's wear the hat of the innocent Oracle user/developer.

We write a nice serializable transaction, that uses only casher DDL.

Being good programmers, we envision that Oracle sometimes raises
all sorts of errors, and make sure that our transaction is rolled back
if one happens.

Now, assume that while our innocent, well-written code is running
our transaction, somebody else truncates the temporary table we
are using, perhaps by error.

And ***horror***, our transaction does not get an error. It does not
get rolled back. It continues quietly, with wrong data, perhaps
printing out a wrong financial report, or storing in the database
incorrect clinical data.

This is just plain wrong !!!

You know, one of the things I like most with Oracle is its
philosophy - and reality - that either its promises to a transaction
are fulfilled, or an error is raised.

But the behavior here discussed is just not on par. I dont care
about serializable transactions. But I do care about the philosophy,
because my data depends on it.

Have a nice day.

[hast...@hotmail.com]

hast...@hotmail.com wrote :

> We write a nice serializable transaction, that uses only casher DDL.

Oops. Please read DML instead of DDL :-))

[Martin T.]

DA Morgan wrote:
> Bob Jones wrote:
> > "Martin T." <bilbotheb...@freenet.de> wrote in message
> > news:1161326760.9...@m73g2000cwd.googlegroups.com...
> >> DA Morgan wrote:
> >>> joeN...@BEA.com wrote:

> (snipped)

> >
> > For the last time, if you want this to work, either use delete or table
> > lock. Serializable does not affect DDLs.
> >
> > Don't complain to the manufacturer if your SUV cannot run on water.
>
> I have to confess I too am shaking my head in amazement.
>
> Hopefully not betraying my ignorance but my understanding is the same as
> yours. I can't understand why anyone would use DDL and expect it to
> behave transactionally.
> --

As others in this thread have said, and what you and Bob seem to
continue to ignore is: The transaction that started as serializable did
not issue any DDL. In fact it only issues select statements, it's not
even trying to change anything. So:
* Is Oracle giving us a useful answer when we delete: Yes - The
serialization works.
* Is Oracle giving us a useful answer when we drop: Yes - we get an
error.
* Is using truncate on a table where anyone else works on bad design:
Yes!
* Is Oracle giving us a useful answer when we query the truncated
table: No, it violates the serialization instead of raising an error.

What's the point of serialization if you can't rely on it to produce a)
the right answer or b) an error.

cheers,
Martin

[Bob Jones]

<hast...@hotmail.com> wrote in message
news:1161414027.4...@i42g2000cwa.googlegroups.com...

Yes, it is a business philosophy indeed.

1. If you are running a transaction and at the same time someone else is
doing a destructive DDL, that tells a lot about the design and security.
Your data could be wrong even before the transaction began. Serializable is
not the solution.

2. If you do not want anybody else to DDL the table during your transaction,
lock the table. Serializable only works when all parties are performing
transactions. That was why I suggested using delete.

[Bob Jones]

<xho...@gmail.com> wrote in message
news:20061020214827.032$N...@newsreader.com...

> DA Morgan <damo...@psoug.org> wrote:
>>
>> I have to confess I too am shaking my head in amazement.
>>
>> Hopefully not betraying my ignorance but my understanding is the same as
>> yours. I can't understand why anyone would use DDL and expect it to
>> behave transactionally.
>
> The person who wanted the transactionality is not the person who issued
> the
> DDL. A *different* person issued the DDL.
>

Yes. DDLs do not write to rollback, therefore no before image for the person
performing DML.

[Bob Jones]

"Martin T." <bilbotheb...@freenet.de> wrote in message

news:1161426546....@m7g2000cwm.googlegroups.com...

> DA Morgan wrote:
>> Bob Jones wrote:
>> > "Martin T." <bilbotheb...@freenet.de> wrote in message
>> > news:1161326760.9...@m73g2000cwd.googlegroups.com...
>> >> DA Morgan wrote:
>> >>> joeN...@BEA.com wrote:
>> (snipped)
>> >
>> > For the last time, if you want this to work, either use delete or table
>> > lock. Serializable does not affect DDLs.
>> >
>> > Don't complain to the manufacturer if your SUV cannot run on water.
>>
>> I have to confess I too am shaking my head in amazement.
>>
>> Hopefully not betraying my ignorance but my understanding is the same as
>> yours. I can't understand why anyone would use DDL and expect it to
>> behave transactionally.
>> --
>
> As others in this thread have said, and what you and Bob seem to
> continue to ignore is: The transaction that started as serializable did
> not issue any DDL. In fact it only issues select statements, it's not
> even trying to change anything. So:

Yes, it was the other party issued a DDL. That's why the serializable does
not work because, I repeat, DDLs do not write to rollback.

> * Is Oracle giving us a useful answer when we delete: Yes - The
> serialization works.
> * Is Oracle giving us a useful answer when we drop: Yes - we get an
> error.
> * Is using truncate on a table where anyone else works on bad design:
> Yes!
> * Is Oracle giving us a useful answer when we query the truncated
> table: No, it violates the serialization instead of raising an error.
>
> What's the point of serialization if you can't rely on it to produce a)
> the right answer or b) an error.
>

Isolation levels are not one-sided affairs, and only apply when all parties
are transactional. "Serializable" does not mean no one else can do anything
to the table during your transaction, it just decides what your transaction
reads in the next operation.

[hpuxrac]

Bob Jones wrote:
> Please do.
>
> > The query was a SELECT in the serializable transaction.
> >
>
> No, session 1 was not set to serializable, but that is not the point anyway.

The session that was doing the selects was serializable. Read the
original posting.

You are not making any points since you don't understand the original
posting.

>
> > After that you go off on some kind of unrelated rant.
> >
>
> Please show some prove.

Whatever.

[Bob Jones]

"hpuxrac" <johnb...@sbcglobal.net> wrote in message

news:1161453264.6...@i3g2000cwc.googlegroups.com...

>
> Bob Jones wrote:
>> Please do.
>>
>> > The query was a SELECT in the serializable transaction.
>> >
>>
>> No, session 1 was not set to serializable, but that is not the point
>> anyway.
>
> The session that was doing the selects was serializable. Read the
> original posting.
>
> You are not making any points since you don't understand the original
> posting.
>

Ok, do you think it is going to matter even they were both serializable?
Choose any combination, my point is still valid as long as the other guy is
doing truncate.

[joe.we...@gmail.com]

On Oct 21, 10:36 am, "Bob Jones" <e...@me.not> wrote:
> "Martin T." <bilbothebagginsb...@freenet.de> wrote in messagenews:1161426546....@m7g2000cwm.googlegroups.com...

>
>
> > DA Morgan wrote:
> >> Bob Jones wrote:

> >> > "Martin T." <bilbothebagginsb...@freenet.de> wrote in message

> >> >news:1161326760.9...@m73g2000cwd.googlegroups.com...
> >> >> DA Morgan wrote:

Isolation means just that, isolation. It is designed and intended
as protection from others. The spec for serializable includes the
next lower isolation level, which is repeatable read. Yes, it doesn't
mean someone else can't do something to a table. It doesn't mean
someone else can't take a sledge hammer to the disk. As you say,
Serializable defines what your tx reads next. It says that the DBMS
will either ensure your repeated read gets the same data or ensure
that your transation cannot commit.
Joe

[Bob Jones]

"joeN...@BEA.com" <joe.we...@gmail.com> wrote in message
news:1161458880.0...@i42g2000cwa.googlegroups.com...

Exactly, if someone sledge hammer the disk, you cannot get the same data
regardless of the isolation level. You can't blame the database for not
being standard compliant because that someone was not doing a transaction.

[joe.we...@gmail.com]

On Oct 21, 3:52 pm, "Bob Jones" <e...@me.not> wrote:
> "joeNOS...@BEA.com" <joe.weinst...@gmail.com> wrote in messagenews:1161458880.0...@i42g2000cwa.googlegroups.com...

Well, no, you *can* blame the DBMS if it allows the tx to
continue and commit despite the disk being in shards.
DDL *is* a transaction. The DBMS either executes it
completely, successfully, atomically, or it doesn't. The
restriction is that DDL can't be included with other statements
in a multi-statement transaction. DDL is like every individual
update statement done via a connection in auto-commit mode.
Each is it's own transaction, auto-committed on success, or
rolled back (not allowed to partially complete) on failure.
Joe

[Bob Jones]

"joeN...@BEA.com" <joe.we...@gmail.com> wrote in message

news:1161474708.6...@e3g2000cwe.googlegroups.com...

No, the transaction cannot continue and commit if the disk is not accesible.

> DDL *is* a transaction. The DBMS either executes it
> completely, successfully, atomically, or it doesn't. The
> restriction is that DDL can't be included with other statements
> in a multi-statement transaction. DDL is like every individual
> update statement done via a connection in auto-commit mode.
> Each is it's own transaction, auto-committed on success, or
> rolled back (not allowed to partially complete) on failure.
> Joe
>

Here is where we don't agree. A DDL is NOT a transaction. You cannot
rollback a DDL that's precisely why it cannot be in a multi-statement
transaction.

[hast...@hotmail.com]

Of course, there is no way to fully protect against mistakes.

But good design, good security *and* good error detection from
software components - including Oracle ! - together go a long way
to mitigate the risks.

> 2. If you do not want anybody else to DDL the table during your transaction,
> lock the table. Serializable only works when all parties are performing
> transactions. That was why I suggested using delete.

Agreed, but we are not discussing whether the transaction should
complete successfully after a DDL, Bob. Everybody agrees it should not.

We are discussing whether an error should be reported by Oracle.

And IMO it should.

Because pretty no application wants DDL to be performed against tables
it is using. And nevertheless these applications do *not* lock their
tables,
for concurrency reasons. They all rely on Oracle to either prevent the
DDL or report an error when the invariants are no longer true.

And that's as it should be.

[Bob Jones]

<hast...@hotmail.com> wrote in message
news:1161496075.5...@e3g2000cwe.googlegroups.com...

The DBMS software can only go so far by providing the necessary tools. It is
just a matter of whether they are used appropriately.

>> 2. If you do not want anybody else to DDL the table during your
>> transaction,
>> lock the table. Serializable only works when all parties are performing
>> transactions. That was why I suggested using delete.
>
> Agreed, but we are not discussing whether the transaction should
> complete successfully after a DDL, Bob. Everybody agrees it should not.
>

In a way we are. We would not have this discussion if the DDL failed.

> We are discussing whether an error should be reported by Oracle.
>
> And IMO it should.
>

Actually we were discussing about standard compliant. So far it has not been
proven it is not. Should Oracle generate a error? It does not have to
because "serializable" makes no sense to non-transactions.

> Because pretty no application wants DDL to be performed against tables
> it is using. And nevertheless these applications do *not* lock their
> tables,
> for concurrency reasons.

Not sure what you meant by "these applications". Only OP's simple select
statement does not lock the table.

> They all rely on Oracle to either prevent the
> DDL or report an error when the invariants are no longer true.
>
> And that's as it should be.
>

Oracle cannot do it unless instructed to with the right code.

[HansF]

On Sat, 21 Oct 2006 22:47:55 -0700, hasta_l3 wrote:

> We are discussing whether an error should be reported by Oracle.

Why is this even being discussed any more?

If there is a question that this is incorrect behavious, open a Service
Request and get Oracle's official answer. Let Oracle provide an official
response.

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

[joe.we...@gmail.com]

On Oct 21, 8:12 pm, "Bob Jones" <e...@me.not> wrote:
> "joeNOS...@BEA.com" <joe.weinst...@gmail.com> wrote in messagenews:1161474708.6...@e3g2000cwe.googlegroups.com...

> > continue and commit despite the disk being in shards.No, the transaction cannot continue and commit if the disk is not accesible.

>
> > DDL *is* a transaction. The DBMS either executes it
> > completely, successfully, atomically, or it doesn't. The
> > restriction is that DDL can't be included with other statements
> > in a multi-statement transaction. DDL is like every individual
> > update statement done via a connection in auto-commit mode.
> > Each is it's own transaction, auto-committed on success, or
> > rolled back (not allowed to partially complete) on failure.
> > Joe
>
> Here is where we don't agree. A DDL is NOT a transaction. You cannot
> rollback a DDL that's precisely why it cannot be in a multi-statement
> transaction.

Right. You can't invoke DDL in an autoCommit(false) mode (ie: in a
multi-statement tx). You can only call DDL in an autoCommit(true) mode,
ie: each statement is it's own transaction. And yes, you cannot roll it
back,
but in that same context you cannot roll back a DML statement. It is
also
executed as it's own single-statement transaction. There is nothing
inherent in the semantics of DDL that precludes it from inclusion in
multistatement transactions, it is just that Oracle didn't want to
implement
it to be. I don't care one way on another, but I believe that *nothing*
that an Oracle user can do with a public API should be an excuse for
Oracle silently dropping it's standard transactional guarantees for
other
users. Like I said before, Ford wouldn't hope to get away with telling
crash victims that their airbags and seatbelts are only supposed to be
functional if they are in collisions with other law-abiding drivers.
Joe

[Laurenz Albe]

DA Morgan <damo...@psoug.org> wrote:
> I would question the reason for would allowing two different isolation
> levels within a single application. What is the business case?

This is a theoretical discussion about standard compliance, at least
the way I'd like to have it.
It does not need a business case.

If you like to argue that one should not care about standard compliance
as long as there are no 'business cases', I'm not willing to follow you
there :^)

I think that everybody agrees that the 'business impact' of Oracle being
unable to serialize correctly is limited.

Yours,
Laurenz Albe

[Laurenz Albe]

Bob Jones <em...@me.not> wrote:
>>> 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.
>>
>
> Again, truncate is not a query, it is not even a dml.

As has been pointed out, 'the query' I am talking about is the select
statement that returns different results both times.

>> 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!
>>
>
> Show me one database that can serialize truncate.

PostgreSQL.

>> 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.
>>
>
> Which one is standard compliant?

All of them. That's the point. It does not matter how you do it.

>> To include the usual flamebait, '(some) other database systems handle this
>> correctly'.
>>
>
> Which ones?

PostgreSQL.

> I would love to see it if anyone can serialize TRUNCATE in any database.

Check it out and enjoy.

Yours,
Laurenz Albe

[Serge Rielau]

Laurenz Albe wrote:
> Bob Jones <em...@me.not> wrote:

>> I would love to see it if anyone can serialize TRUNCATE in any database.
> Check it out and enjoy.

MS SQL Server even allows ROLLBACK past a TRUNCATE.
Any utilities which perform truncate (LOAD/IMPORT REPLACE/ALTER TABLE)
in DB2 ensure that concurrent users have released any locks.
I fully expect IDS to behave similarly on TRUNCATE. IDS also does auto
commit (like Oracle).
So autocommit for DDL does not prevent proper semantics for DML.

FWIW when I request information from a database system in a specific
isolation level I expect exactly one of two behaviors:
1. Get the correct results
2. Get an error informing me that what I asked for couldn't be done.

I don't care what the error is and what it's cause is. But an error it
must be whether someone trips over the power cord or a rogue admistrator
chooses to pull a table from under me.

Anything else is kaput and to those who disagree I propose to sign up
for a Database 101 class (which is different from an Oracle class!) at a
reputable institution.

[Laurenz Albe]

Serge Rielau <sri...@ca.ibm.com> wrote:

> Laurenz Albe wrote:
>>> I would love to see it if anyone can serialize TRUNCATE in any database.
>>
>> Check it out and enjoy.

> MS SQL Server even allows ROLLBACK past a TRUNCATE.
> Any utilities which perform truncate (LOAD/IMPORT REPLACE/ALTER TABLE)
> in DB2 ensure that concurrent users have released any locks.
> I fully expect IDS to behave similarly on TRUNCATE. IDS also does auto
> commit (like Oracle).
> So autocommit for DDL does not prevent proper semantics for DML.
>
> FWIW when I request information from a database system in a specific
> isolation level I expect exactly one of two behaviors:
> 1. Get the correct results
> 2. Get an error informing me that what I asked for couldn't be done.
>
> I don't care what the error is and what it's cause is. But an error it
> must be whether someone trips over the power cord or a rogue admistrator
> chooses to pull a table from under me.
>
> Anything else is kaput and to those who disagree I propose to sign up
> for a Database 101 class (which is different from an Oracle class!) at a
> reputable institution.

That's what I said, just a little more elaborate and more polemic.

Yours,
Laurenz Albe

[DA Morgan]

That wasn't my point. I was assuming the issues not theoretical as they
have turned out to be.

[hast...@hotmail.com]

HansF wrote :

> On Sat, 21 Oct 2006 22:47:55 -0700, hasta_l3 wrote:
>
> > We are discussing whether an error should be reported by Oracle.
>
> Why is this even being discussed any more?
>

Because I am having a friendly talk with Bob, trying to understand
his point of view, and learning some stuff in the process.

That's what a discussion system is for, isn't-it ? :-)

[HansF]

Two points here:

1) whether this should be opened as a SR. If that is the objective, it
should have been opened a long time ago - and that is what my comment is
about;

2) a general discussion on whether the action is correct, or acceptable,
or unreasonable, or ... - from that view, I am watching this one as well.

To often I've seen people dither around for days before opening a SR.
After a point (usually 2 hours, IMO) dithering amounts to a total waste of
tme and energy.

Even in this case, a SR should have been opened so we could get an
official response or reference rather than go around in these theoretical
circles.

[hast...@hotmail.com]

Bob Jones wrote :

> The DBMS software can only go so far by providing the necessary tools.

Yes, I can accept this argument, Bob. Especially since we are
talking detection of mistakes.

> Actually we were discussing about standard compliant. So far it has not been
> proven it is not. Should Oracle generate a error?

Oh ? While I think it is more of a QOI issue, my very uninformed
opinion,
for what it is worth, is :

- The Sql 92/99 standard forbids non repeatable/phantom reads
for serializable transactions, even when DDL is performed by
other transactions

- The standard is of little formal guidance here, given the
level of non-conformity involved.

- But Oracle claims that serializable transactions will not
experience non repeatable/phantom reads either, never.

The reading is as follows :

A : Is the OP actually in a transaction ?

A.1 In Sql-99, according to my reading :

a) SET TRANSACTION is not initiating a transaction. Its use
is actually prohibited inside a transaction.

b) To actually start the transaction, the OP would need to issue
a START TRANSACTION (or a single row select)

c) Oracle does not have and does not claim to have a
START TRANSACTION statement.

Therefore, the OP cannot achieve the desired effect with
Oracle in a Sql-99 conformant way.

A.2 In Oracle, according to my reading of the SQL reference manual :

SET TRANSACTION must be the first statement of a transaction,
and actually starts it.

Conclusion : The OP is in transaction, albeit in a non-conformant way.

B. Is a serializable transaction protected against truncate ?

B.1 In Sql-99 :

a) A transaction isolation level is guaranteed against other
transactions
which execute sql-data or sql-schema operations. Note that sql-schema
operations include ALTER or DROP TABLE, which do initiate transactions.

The overall intent is certainly to protect against DDL.

b) but TRUNCATE is not an SQL-99 statement.

B.2 In Oracle :

Oracle claims that "The SERIALIAZABLE setting specifies serializable
transaction isolation mode as defined in the SQL92 standard."

Conclusion : The OP cannot expect anything from the letter of the
standard, since TRUNCATE is not standard. However, the intent of
the standard's isolation modes - which oracle claims to follow - is
certainly to protect against DDL.

(Incidentaly, Oracle's claim is specifically for Sql-92 while it
usually refers to Sql-99 otherwhere. I couldnt find a relevant
difference between 92 and 99, so I assume it is some leftover)

C. What is the level of protection. Should an error be raised ?

C.1 Sql-92 & 99 tell us :

"all read operations are repeatable within an SQL-transaction at
isolation
evel SERIALIZABLE, except for (1) the effects of changes to SQL-data or
schemas
and its contents made explicitly by the SQL-transaction itself (2) the
effects
of differences in parameter values supplied to procedures, and (3) the
effects
of references to time-varying system variables such as CURRENT_DATE and

CURRENT_USER. "

Note that there is no exception for changes performed by
SQL-Schemas/DDL
in other transactions. Reads must be repeatable.

C.2 Oracle tell us

"The SERIALIAZBLE setting specifies serializable transaction isolation
mode as defined in the SQL92 standard. If a serializable transaction
contains data manipulation language (DML) that attempts to update any
resource that may have been updated in a transaction uncommitted at the

start of the serializable transaction, then the DML statement fails."
(SET TRANSACTION reference manual)

"Serializable transactions see only those changes that were committed
at the
time the transaction began, plus those changes made by the transaction
itself
through INSERT, UPDATE, and DELETE statements. Serializable
transactions do
not experience nonrepeatable reads or phantoms." (concept manual)

Thus, Oracle claims conformance with Sql-92, which guarantees
repeatable
read even when DDL is performed by other transactions. Oracle also
claims
that serializable transactions do not experience nonrepeatable reads.
Never.

On the other hand, oracle claims that an error is raised if a
serializable
transaction attempts to update an otherwise modified resource. But
nowhere is
it claimed that this is the complete set of failures.

One might object that a DDL statement is not part of a transaction and
thus does
not commit. However, Oracle claims : "A transaction in Oracle begins
when the first
executable SQL statement is encountered. An executable SQL statement is
a SQL
statement that generates calls to an instance, **including DML and DDL
statements.**"
(Overview of Transaction Management)

Overall, I think that the Sql standard forbids non repeatable and
phantom
reads in serializable transaction, even when DDL has been performed by
other
sessions, and that Oracle claims the same...

References :

http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/ansi-iso-9075-2-1999.pdf
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Oracle9i SQL Reference Release 2 (9.2) (SET TRANSACTION)
Oracle9i Database Concepts Release 2 (9.2) (20. Data Concurrency and
Consistency)
Oracle9i SQL Reference Release 2 (9.2) (B. Oracle and Standard SQL)

[hast...@hotmail.com]

HansF wrote :

> On Mon, 23 Oct 2006 09:51:51 -0700, hasta_l3 wrote:
>
> > HansF wrote :
> >
> >> On Sat, 21 Oct 2006 22:47:55 -0700, hasta_l3 wrote:
> >>
> >> > We are discussing whether an error should be reported by Oracle.
> >>
> >> Why is this even being discussed any more?
> >>
> >
> > Because I am having a friendly talk with Bob, trying to understand
> > his point of view, and learning some stuff in the process.
> >
> > That's what a discussion system is for, isn't-it ? :-)
>
> Two points here:
>
> 1) whether this should be opened as a SR. If that is the objective, it
> should have been opened a long time ago - and that is what my comment is
> about;
>
> 2) a general discussion on whether the action is correct, or acceptable,
> or unreasonable, or ... - from that view, I am watching this one as well.
>
>
> To often I've seen people dither around for days before opening a SR.
> After a point (usually 2 hours, IMO) dithering amounts to a total waste of
> tme and energy.
>
>
> Even in this case, a SR should have been opened so we could get an
> official response or reference rather than go around in these theoretical
> circles.
>

Yes, Hans, I agree with you...

I didnt think of opening an SR myself. But will open
one (out of curiousity :-) if the OP confirms that he has
not done so already.

[Mark Townsend]

>
> I didnt think of opening an SR myself. But will open
> one (out of curiousity :-) if the OP confirms that he has
> not done so already.
>

When you do open one please post the number here (or send to me)

[hast...@hotmail.com]

Mark Townsend wrote :

SR 5913602.993

[Mark Townsend]

Development looked at this and agree it's a bug. Likely fix will be to
raise an error on the select after the truncate (as no roll back to
reconstruct the rows). No fixed by yet.

[HansF]

Thanks for the update Mark. Looking forward to the fix.

[DA Morgan]

Thanks.

[harvi...@gmail.com]

Thanks a lot to everyone to post the feedback.
It is great that SR is already created and will be fixed soon. (I
monitored the posts for first few days and forget about this issue so
was not able to answer some questions.)
I just want to mention 1 point that it was mentioned many times in
discussion that use "delete" statement instead of "truncate", I already
post a seperate issue on that on google and opened a SR that truncate
is taking 15-20 times longer than delete in 10.2.0.2 and so far oracle
is not considering it as a bug but i am working with them on this
issue.

Thanks
--Harvinder

On Oct 31, 12:14 pm, DA Morgan <damor...@psoug.org> wrote:
> Mark Townsend wrote:

> > hasta...@hotmail.com wrote:
> >> Mark Townsend wrote :
>
> >>>> I didnt think of opening an SR myself. But will open
> >>>> one (out of curiousity :-) if the OP confirms that he has
> >>>> not done so already.
>
> >>> When you do open one please post the number here (or send to me)
>
> >> SR 5913602.993
>
> > Development looked at this and agree it's a bug. Likely fix will be to
> > raise an error on the select after the truncate (as no roll back to
> > reconstruct the rows). No fixed by yet.Thanks.
> --
> Daniel A. Morgan
> University of Washington

> damor...@x.washington.edu