| Subject | Re: [ib-support] What's the diff between Commitretaining and Commit? |
|---|---|
| Author | Helen Borrie |
| Post date | 2002-08-29T07:47:10Z |
At 05:07 PM 29-08-02 +1000, you wrote:
CommitRetaining can be rolled back. CommitRetaining commits all the work
in the transaction, just like Commit, but instead of closing the cursor
completely, it causes a new transaction to be started *retaining* the
cursor and the context of the previous transaction. Unless the transaction
has ReadCommited isolation, the user will still see the "view" of the
database as it was when StartTransaction was originally called.
Also note that, because the transaction context is retained, the rows
affected cannot be garbage collected because the oldest active transaction
can't move forward. Thus it behoves you to do hard Commits from time to
time to avoid having the database grind to a halt from lack of housekeeping.
Understand, too, that all modifications performed by UPDATE statements
operate on the entire row, not on individual columns. The entire row gets
replaced by a new version if the commit is successful, and the old version
(the "delta") is retained in the database (but completely inaccessible)
until garbage collection occurs. This mechanism is referred to as a
"multi-generational architecture".
CommitRetaining never applies to DDL. The DDL statement language has a
whole subsystem of its own under the blankets, where all sorts of
dependency checks are done with the objective of updating the system
tables. Some types of committed DDL will often be retained and the actual
updating deferred until the conditions are safe to change the system
tables, i.e. no transactions are alive that depend on them. The process
that actually performs DDL changes works in a transaction of its own under
Table Stability isolation or something with equivalent concurrency
restriction.
In general, it is a very uncool idea to have user apps that perform DDL
when you have active DML going on.
heLen
>Hi,Nothing. If the CommitRetaining fails, then anything since the previous
>
>I have solved some problems by putting CommitRetaining after each
>ExecQuery. But
>if you start a transaction and do a number of things all followed by
>commitretaining, and then have to rollback, what can be rolled back?
CommitRetaining can be rolled back. CommitRetaining commits all the work
in the transaction, just like Commit, but instead of closing the cursor
completely, it causes a new transaction to be started *retaining* the
cursor and the context of the previous transaction. Unless the transaction
has ReadCommited isolation, the user will still see the "view" of the
database as it was when StartTransaction was originally called.
Also note that, because the transaction context is retained, the rows
affected cannot be garbage collected because the oldest active transaction
can't move forward. Thus it behoves you to do hard Commits from time to
time to avoid having the database grind to a halt from lack of housekeeping.
>Is it allOnce committed, nothing can be rolled back.
>mods to the values of fields, plus any deletes and inserts? Is it just
>DDL, and
>all DDL, that cannot be rolled back?
Understand, too, that all modifications performed by UPDATE statements
operate on the entire row, not on individual columns. The entire row gets
replaced by a new version if the commit is successful, and the old version
(the "delta") is retained in the database (but completely inaccessible)
until garbage collection occurs. This mechanism is referred to as a
"multi-generational architecture".
CommitRetaining never applies to DDL. The DDL statement language has a
whole subsystem of its own under the blankets, where all sorts of
dependency checks are done with the objective of updating the system
tables. Some types of committed DDL will often be retained and the actual
updating deferred until the conditions are safe to change the system
tables, i.e. no transactions are alive that depend on them. The process
that actually performs DDL changes works in a transaction of its own under
Table Stability isolation or something with equivalent concurrency
restriction.
In general, it is a very uncool idea to have user apps that perform DDL
when you have active DML going on.
heLen