Re: serializable isolation level behavior question

On Oct 21, 8:12 pm, "Bob Jones" <e..._at_me.not> wrote:
> "joeNOS..._at_BEA.com" <joe.weinst..._at_gmail.com> wrote in messagenews: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.

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 Received on Sun Oct 22 2006 - 21:27:54 CDT