SQL/MX 2.x Reference Manual (H06.04+)

Introduction
HP NonStop SQL/MX Reference Manual—540440-003
1-14
User-Defined and System-Defined Transactions
When NonStop SQL/MX attempts to insert, update, or delete multiple rows, it uses
savepoints and if it encounters an error during the operation it issues a warning, rolls
back that statement, and continues.
For more information on the UPD_ABORT_ON_ERROR default, see Statement
Atomicity on page 10-64.
If the default INSERT_VSBB is set to USER, NonStop SQL/MX will not use statement
atomicity. Unless you are inserting only a few records you should not disable
INSERT_VSBB to use statement atomicity because performance will be affected.
Perform UPDATE STATISTICS on the tables so that row estimates are correct.
To see what rollback mode NonStop SQL/MX is choosing, you can prepare the query,
then perform DISPLAY_EXPLAIN.
display_explain options 'f' my_query;
The OPT column displays token upd_action_on_error: on_rollback. A value of “x”
means that the transaction will be rolled back. Any other value means the transaction
will be aborted. See DISPLAY_EXPLAIN Command on page 4-13 for details. For
details about these defaults, see INSERT_VSBB on page 10-61 and
UPD_ABORT_ON_ERROR on page 10-64.
For more information about the differences in auto-abort behavior between
NonStop SQL/MP and NonStop SQL/MX, see the SQL/MX Comparison Guide for
SQL/MP Users.
User-Defined and System-Defined Transactions
User-Defined Transactions
Transactions you define are called user-defined transactions. To ensure that a
sequence of statements either executes successfully or not at all, you can define one
transaction consisting of these statements by using the BEGIN WORK Statement and
COMMIT WORK Statement. You can abort a transaction by using the ROLLBACK
WORK Statement.
System-Defined Transactions
In some cases, NonStop SQL/MX defines transactions for you. These transactions are
called system-defined transactions. Most DML statements initiate transactions implicitly
at the start of execution. See Implicit Transactions on page 2-220. However, even if a
transaction is initiated implicitly, you must end a transaction explicitly with the COMMIT
WORK statement or the ROLLBACK WORK statement.