Reference Guide > TDV SQL Script > SQL Script Procedures and Structure > Independent Transactions
 
Independent Transactions
An independent transaction in SQL Script is a set of work that can be rolled back or committed on its own, regardless of what happens to the main transaction.
Syntax
INDEPENDENT [<option> …] TRANSACTION
Remarks
• Options (<option> ...) are not case-sensitive.
• The following table describes the option flags for an independent transaction.
Option Flag
Significance
ROLLBACK_ON_FAILURE |
BEST_EFFORT
This pair of flags indicates whether the transaction should be rolled back if a failure occurs during COMMIT (ROLLBACK_ON_FAILURE, the default) or not (BEST_EFFORT). You cannot set both of these flags at the same time.
With ROLLBACK_ON_FAILURE, failure to commit any part of the transaction causes uncommitted parts to be discarded, and causes already committed parts to be compensated (according to the COMPENSATE/NOCOMPENSATE option).
With BEST_EFFORT, even if one part of the transaction cannot be committed, as many other parts as possible are still committed. The failed parts are logged.
COMPENSATE | NOCOMPENSATE
This pair of flags indicates whether the compensation blocks should be run if the transaction rolls back (COMPENSATE, the default) or not (NOCOMPENSATE). You cannot set both of these flags at the same time.
NOCOMPENSATE improves performance at the risk of compensation. However, setting this to COMPENSATE has no performance cost unless you define a compensation block.
IGNORE_INTERRUPT |
LOG_INTERRUPT |
FAIL_INTERRUPT
This group of flags indicates what the system should do if the server goes down or is interrupted when the transaction commit is partially complete. You cannot set more than one of these flags at a time.
• IGNORE_INTERRUPT (the default) causes the server to take no special action on restart.
• LOG_INTERRUPT causes the server to store basic transaction information before beginning to commit so that on restart it can detect any transactions in progress and log their failure. This option requires two meta-commits per transaction (start and stop).
• FAIL_INTERRUPT causes the server to store enough information to perform the requested failure model upon server startup for any in-progress transactions. This option is expensive, because it requests meta-commits for start of transaction, for end of transaction, and between each pair of sources it commits to.
• The BEGIN statement can be followed by a transaction specifier. (See Compound Statements for information on using BEGIN in a compound statement.) If there is no specifier, the block runs within its parent’s transaction, and any work it performs is part of the parent transaction.
• When a compound statement is declared as having an independent transaction, all actions in that scope are part of the transaction. See Compound Statements for information on declaring a compound statement.
• Calling COMMIT is recommended but not required. See COMMIT.
• A normal exit from the scope commits the transaction.
• Exiting the scope through an unhandled exception causes a transaction rollback.
• Exiting through any handled exception does not implicitly roll back the transaction. You must explicitly roll back the transaction if that is what you want. See ROLLBACK.
Example
You can use the BEST_EFFORT and NOCOMPENSATE options as follows in SQL Script:
PROCEDURE myProcedure ( )
BEGIN INDEPENDENT BEST_EFFORT NOCOMPENSATE TRANSACTION
-- Add your code here
END
Error
The following table describes the error that can occur while resolving a transaction.
Error Message
Cause
Conflicting options
Two mutually exclusive options have been declared.