Compensating Transactions
A compensating transaction in SQL Script is a special handler that a COMPENSATE exception invokes to restore transactional integrity after a compound statement ends.
Remarks
• The presence of a handler for the COMPENSATE exception causes special behavior at run time. Unlike other exceptions, this exception cannot be handled by an ELSE clause; it can only be handled explicitly.
• The COMPENSATE exception is special because it is the only exception that can be raised after the compound statement ends. It can be called a long time after the statement ends. This exception is raised if the transaction is rolled back either explicitly by the transaction’s controller or by the system, if a failure occurs during commit.
• The COMPENSATE handler has access to all the variables that the block can see, like other exception handlers. This is a copy of those variables at the time the block exited.
• Compensation can be expensive because this additional storage of variable state has to be kept for every execution of the block. For example, if the block occurs in a loop that ran 1,000 times, 1,000 separate compensation states need to run. For this reason, monitor the COMPENSATE handler carefully.
• Only the current local data state is preserved for the handler. The global system state is not preserved. That is, if you call another procedure, it cannot be in the same state as it was the first time this block was run. For this reason, any required state should be captured during the normal run into variables so they can be used during the COMPENSATE handler.
Examples
PROCEDURE p ( )
BEGIN INDEPENDENT TRANSACTION
<statement>
END
The insert is automatically committed in the example below.
PROCEDURE p ( )
BEGIN INDEPENDENT TRANSACTION
INSERT INTO /shared/T (name, score) VALUES ('Joe”, 123);
END
The insert is automatically rolled back in the example below.
PROCEDURE p ( )
BEGIN INDEPENDENT TRANSACTION
DECLARE my_exc EXCEPTION;
INSERT INTO /shared/T (name, score) VALUES ('Joe”, 123);
RAISE my_exec;
END
The insert is automatically committed in the example below.
PROCEDURE p ( )
BEGIN INDEPENDENT TRANSACTION
DECLARE my_exc EXCEPTION;
INSERT INTO /shared/T (name, score) VALUES ('Joe”, 123);
RAISE my_exec;
EXCEPTION
ELSE
END