Subject Re: [firebird-support] Re: Embedded Firebird : Transaction
Author Ann W. Harrison
Adam wrote:
> Transactions should be used to define your atomic set of operations.

You are absolutely correct, and the database system performance should
not dictate transaction semantics to the application.
> " ...When the volume of changes performed under a transaction-level
> savepoint is getting large (10^4-10^6
> records affected) the engine releases the transaction-level savepoint
> and uses the TIP mechanism to
> roll back the transaction if needed."
> That is where the commit every n transactions would have originated.

Right. There's been a long split in opinion among InterBase and
Firebird developers over the "fairness" of the garbage collection
mechanism, especially with regard to transactions that roll back. In
the original implementation, when a transaction rolled back, it changed
its state on the TIP page and went away, leaving whatever changes it had
made to be cleaned up by the next transaction that happened to try to
read the records.

That was "unfair" to the innocent transaction, but it avoided the need
to maintain a rollback segment/log/journal/whatever. Undo logs are
necessary for multi-record operations that must be atomic under the SQL
rules: mass inserts, updates, deletes, triggers, and stored procedures.
Since the mechanism existed, Borland decided in the interest of
"fairness" that every transaction should carry an undo log and, if
possible, undo its own changes on failure. They chose to burden every
transaction with the potential of significant memory usage to avoid
cooperative cleanup in the (relatively rare) case of failure.

The predictable result was that gbak could not longer restore large
databases, so they added a special transaction option for gbak that
turned off the undo log, and probably at the same time, caused a normal
transaction to release its undo log after it got to some size.

> But if I read this correctly, even if you were to insert 10 million
> records in a single transaction, Firebird would release on the
> transaction savepoint log.

[Considerable grumbling about the efficiency of building up a large
in-memory structure then dropping it has been suppressed]

> Out of curiousity, does the client application have to wait around
> while the 10000 to 1000000 changes are undone?