RE: [Info-ingres] Friday is coming ...

At 6:09 AM +1000 7/21/2005, Paul White wrote:
>DDLs operate in a different dimension from DDLs. The resource required to
>complete are not just row, page or table level. They lock entire catalogs
>and databases. Schema changes can cause entire tables to be re-written.
>They are executed by the schema owner / dba / God (I mean the IT manager).
>There is a limit to the number of DDLs you can include in a single
>transaction. Some DDLS cannot be rolled back.

None of these are arguments for making DDL non-transactional.
Some are reasonable arguments for not indiscriminately interspersing
DDL randomly. Some of your statements are a bit hyperbolic, such as
the bit about locking entire catalogs and databases. (Entire catalogs
aren't locked unless lock escalation takes place. I can't think of any
DDL that locks an entire database. Schema changes won't rewrite entire
tables unless you make it do so.) It's true that some DDL will take
exclusive control locks on tables.

What DDL can't be rolled back? Modify on a temporary table is all
that I can think of. (and it's rolled back like any operation on a
temporary table -- the table is dropped.)

By the way, the DDL limit-per-transaction is eliminated in r3.
That was purely an implementation issue, as are some of the other
points raised.

>
>As a rule, I try to ensure DML statements are executed, well outside of
>"normal" transactions, when the system is quiet. I prefer to use temp
>session tables instead of real tables.

Nothing wrong with that. I still expect DDL to be subject to transaction
rules just like any other non-session-control SQL statement.

Karl
.

Relevant Pages

  • Deadlock in single session
    ... I'm running a script that applies the delta DDL that is required to get ... existing sequences, and renaming two other sequences (which are created ... deadlock detected while trying to lock object CHS.HARNLOC1 ... lock user session count mode flags ...
    (comp.databases.oracle.misc)
  • RE: [Info-ingres] Friday is coming ...
    ... Here's some examples of dubious DDL rollback with procedures: ... aren't locked unless lock escalation takes place. ... DDL that locks an entire database. ...
    (comp.databases.ingres)
  • Re: Data/index locking in SQL server
    ... Can you post the actual DDL including the indexes for that table? ... "Alex Drobyshev" wrote in message ... > By "lock on the table" I actually meant a lock on a specific row in the ... here are lock acquisition sequences I observed: ...
    (microsoft.public.sqlserver.programming)