Re: serializable isolation level behavior question

"joeNOSPAM_at_BEA.com" <joe.weinstein_at_gmail.com> wrote in message news:1161474708.682103.243060_at_e3g2000cwe.googlegroups.com...
>
>
> On Oct 21, 3:52 pm, "Bob Jones" <e..._at_me.not> wrote:
>> "joeNOS..._at_BEA.com" <joe.weinst..._at_gmail.com> wrote in
>> messagenews:1161458880.026777.264620_at_i42g2000cwa.googlegroups.com...
>>
>> > On Oct 21, 10:36 am, "Bob Jones" <e..._at_me.not> wrote:
>> >> "Martin T." <bilbothebagginsb..._at_freenet.de> wrote in
>> >> messagenews:1161426546.350950.93250_at_m7g2000cwm.googlegroups.com...
>>
>> >> > DA Morgan wrote:
>> >> >> Bob Jones wrote:
>> >> >> > "Martin T." <bilbothebagginsb..._at_freenet.de> wrote in message
>> >> >> >news:1161326760.938515.185890_at_m73g2000cwd.googlegroups.com...
>> >> >> >> DA Morgan wrote:
>> >> >> >>> joeNOS..._at_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.
>>
>> > 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.
>>
>> 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.
>
> Well, no, you *can* blame the DBMS if it allows the tx to
> 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. Received on Sat Oct 21 2006 - 22:12:11 CDT