Re: truncate randomly failing

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<athodl$4qv$1_at_babylon.agtel.net>...
> Well, I still don't see how A is violated by a truncate (or by any
> other DDL, to that matter).
> >
> > Matthias and Jonathan pointed it out, but to be a bit more clear, it
> > tries to maintain ACID by doing the commit, but after the commit there
> > may be issues. Bug or design issues? Perhaps both. Erika's problem
> > sure sounds like a bug, assuming it really happens to a single user.
> > And I've seen similar things when I've been the only user on a new db.
>
> Indeed, any DDL implicitly commits before and after itself (or rolls back
> if failed for some reason). This makes changes done by DDL itself pretty
> atomic. As of inability to rollback a truncate operation - can you roll back
> any other successful DDL? No, because it was successful - and it already
> committed all changes to the dictionary and related structures. If it was
> unsuccessful - it already rolled itself back. That's the way DDL works, and
> it works atomically even though it may look as though it doesn't - because
> it always commits before itself, then does its own transaction, and either

I think the violation happens because there is a transient condition during its own transaction that makes the combination of commit/transaction/commit wrong. In other words, maybe it's really a C violation, since a truncate is not happening because the table is not accessible, but it should be. Whether the transient condition is an A depends on what is really happening, which I don't know. I suspect that it is.

> commits or rolls back again depending on the outcome. As of Jonathan's
> example - indeed this may be a problem, but does it demonstrate a
> violation of any ACID letter? Session 1 waits for a lock on table, session
> 2 issues truncate over the table - truncate internally commits releasing
> all session 2 locks and session 1 acquires the lock it waits for, session 2
> then attempts to acquire the lock, too, and fails - truncate never takes
> place. Where the atomicity is violated in this scenario?

I think the problem in the scenario isn't atomicity, but rather programmers assumptions of atomicity in that sort of scenario.

>
> As of 8i reference saying that "...because the information in the V$ views is
> dynamic, read consistency is not guaranteed for these views", I believe this
> statement only relates to *dynamic performance* views and not to dictionary
> views, which are pretty static and are always read-consistent.

Ahhh, but what about v$access (or tables underlying synonyms)? Not static - how can it be read-consistent? If DDL such as whether you can truncate is based upon it, doesn't that potentially ruin A or C? Couldn't that explain random ORA-0942's in a truncate procedure?

I'll repeat, this is all wild speculation on my part. We're all interested in how it really works. If all DDL is based on static dictionary tables, cool. But I suspect there is some dynamism and trade-offs in Oracle. I also suspect there's a lot of stuff left over from manual DBA times, when it didn't matter because you could just try it again, that sometimes show up when trying to automate or proceduralize DBA tasks.

jg

--
@home is bogus.
The X$ Files - "The Truth Is In There."