Re: serializable isolation level behavior question

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. 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 Received on Sat Oct 21 2006 - 18:51:48 CDT