Subject Re: [firebird-support] implementation questions -- how does firebird rollback a partially-executed statement?
Author Helen Borrie
At 05:16 PM 11/09/2005 -0700, you wrote:
>I think I see.
>Everything the transaction does (forward and backward) stays invisible
>until the TIP indicates the transaction has committed.

It's visible to the transaction that requested it.

>If a statement fails half-way through, Firebird first attempts to undo
>the changes of that statement (only) via an undo log. If that succeeds,
>then it tells the user the statement failed, but keeps the transaction
>moving forward;

No. If the statement failed on a specific operation, then that operation
simply doesn't happen; but, at that point, other changes are
unaffected. The transaction still sees them.

Consider, for example, a stored procedure that is executing a number of
statements, or is executing its way through a loop where one statement
affects many records. In PSQL, you can trap that exception as soon as it
occurs, and handle it, e.g. by simply bypassing that record, maybe writing
out a log record, and continue executing the SP. Because the exception was
handled in the SP, the client doesn't know about it, other than by reading
the log. Any work executed up to the point where the exception occurred is
wound back to the beginning of that BEGIN...END block (or the beginning of
the iteration, in the case of a looping operation).

Then, once the SP finishes and returns execution to the client, the
application might have been written to let the user inspect the log before
deciding to commit the work or roll it back. The log records (and the
effects of the SP's work) are visible only to that transaction. The are
visible to other transactions only in the sense that records affected by
the SP are locked to them.

In DSQL you can achieve affects similar to what the exception handling does
in SPs, by packaging stages of the logical task in named SAVEPOINT
blocks. The client can handle the exception by rolling back to a
particular named SAVEPOINT, without having to unwind the entire transaction
(which is what ROLLBACK does).

>the statement appears undone and everything's fine. If for some reason it
>fails to undo the statement's changes, it can always throw an error and
>kill the transaction.

No, you've got this wrong. The work that gets undone when an exception
occurs is exactly and only that bit that caused the exception to be
thrown. It's "undone" in the sense that it couldn't be completed for
whatever reason. For example, a particular request for a particular record
hits a wall during the execution of a trigger, say, through a validation
failure - any other work already done to that record, e.g. by other
triggers, is wound back. But previous work on other records succeeded and
it does not get wound back just because work on this record failed.

>Either the statement gets rolled back, or the transaction gets rolled
>back, but it's safe regardless. Sweet!

What gets rolled back (let's think of it as "undone", to avoid confusion
with the ROLLBACK statement) depends on what the client code instructs when
the time comes to commit work or to roll it back...