Partial transactions.

    A transaction is a logic unit of work. Either all statements (SQL commands) inside it are executed successfully or all of them are discarded. However, there are cases when a need to keep partial changes may be needed.

IB allows the use of exceptions and SUSPEND to control the extent of automatic operation undoing inside stored procedures and triggers. By default, when an exception is raised by a procedure or trigger or an error condition is detected by the engine, all actions in the BEGIN...END block containing the exception are undone. Like in OO languages, an absence of a handler makes the exception to go to the outer BEGIN...END block, looking for an exception handler and so on until a handler is found. If such handler is not found, the procedure is terminated and all its actions are undone. This means an error message will be returned to the application.

The exception handler is the WHEN statement. If WHEN appears in a BEGIN...END block, it must be the last instruction in a block, of course before the instruction or group of instructions that must be executed if the WHEN applies. If a condition in the WHEN catches an exception, execution resumes in the statement or block that follows the WHEN and from that point, the procedure continues its normal flow control and the exception is absorbed, so no error message is returned to the application.

The complete syntax of a WHEN statement is:
WHEN {<error> [, <error> ...] | ANY}
DO <compound_statement>
<error>= {EXCEPTION exception_name | SQLCODE number | GDSCODE errcode}
and it means not only an exception raised by a procedure or trigger can be trapped, but SQL errors and Interbase (GDS) errors. There's a degenerate form, ANY, that allows for trapping any kind of error conditions, but without being able to identify the specific cause of the problem.

In a procedure A that doesn't deal with errors, all actions are undone when an user exception or operational error (identified by SQLCODE or GDSCODE) happens. If A was called from another procedure B and there's no exception processing in B, then all actions in B are undone, too and the error conditions affects the caller of B. If such caller is not a procedure, then the error is reported to the calling application. It must be clear that the presence of an error handler doesn't suffice to catch errors. Same as in OO languages, if the exception handler specification doesn't catch the error, then the effect is the same than no exception block and the call stack will be unwind up to the previous caller.

In a FOR/INTO loop inside a procedure, all actions (including all previous occurrences in the loop) will be undone by an error condition, too, but this doesn't happen in real cases. The reason is that in a FOR/INTO loop, usually a SUSPEND instruction is issued because this combination is used in selectable procedures. When a SUSPEND clause is present and an error condition happens, actions are undone only up to the last executed SUSPEND instruction. 

It should be noted that SUSPEND cannot be used as a mere way to stop the scope of an automatic undo, because it means the stored procedure expects that the client retrieves a set of values before continuing after such SUSPEND. This is a reminder so you understand that in a typical FOR/INTO loop that is fetching values and uses a SUSPEND to deliver records to the client, if an UPDATE or DELETE or INSERT clause is used in the body of the loop and it fails, the undone actions are the ones that go the the previous SUSPEND call, so typically this will be the prior execution of the body in the loop.

So, the combination of exceptions, SQL errors and Interbase errors plus the SUSPEND instruction and/or WHEN instruction makes possible to control the extent of an automatic undo. Remember that a trapped error is not seen in the client application. One way to limit the effect of an error in a procedure or trigger is to have successive BEGIN/END blocks, each one with its own WHEN handler, so changes in previous blocks remain untouched. So, a client application that doesn't see an error can commit happily  the transaction.

A confusion should be cleared: partial transactions (for lack of a better name) aren't a replacement for nested transactions. There's a mechanism that unwinds the call stack and undoes actions when an error happens while Interbase is executing changes, but they cannot override transactions. In other words, if a procedure manages to catch an error and the application commits, the partial changes are written definitely in the database; but if the application rolls back, regardless of the actions done on the procedure and the scope of the undone actions, the rollback per se means all actions since the transaction started are discarded.

The WHEN statement is available only in stored procedures and triggers. DSQL commands sent from an application cannot use it. In this case, it's typical that the application inspect the return codes from the server and displays a message, for example. The WHENEVER statement that appears in the documentation is for the sole usage of Embedded SQL applications, so in the typical Delphi, BCB, three tiered or web application, it can't be used.

This page was last updated on 2001-02-15 19:29:40