SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Introduction
HP NonStop SQL/MX Reference Manual—523725-004
1-12
Statement Atomicity
Statement atomicity occurs if these conditions are met:
•
The UPD_ABORT_ON_ERROR default must be set to OFF (the default.)
•
The underlying table must not have referential integrity constraints, COMMENT
materialized view logs, END COMMENT or triggers.
•
The SQL query is not:
°
A publish/subscribe query with stream access
°
A CALL statement
°
A holdable cursor
°
A SELECT statement with an embedded UPDATE or DELETE
°
A DDL statement
°
An UPDATE STATISTICS statement
•
The query plan does not choose VSBB inserts or Executor Server Process (ESP)
parallelism.
If these conditions are not met, the transaction is aborted by NonStop SQL/MX if a
failure occurs. This behavior occurs for all INSERT, UPDATE, or DELETE statements
in SQL/MX prior to SQL/MX Release 2.x.
When NonStop SQL/MX attempts to perform an insert, update, or delete transaction
against a single row, it does not use savepoints. If the operation fails, NonStop
SQL/MX returns an error. Because no change was made to the database, nothing is
rolled back.
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 information on the UPD_ABORT_ON_ERROR default, see Statement Atomicity on
page 10-62.
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. For information, see DISPLAY_EXPLAIN Command
on page 4-13. For
information about these defaults, see INSERT_VSBB
on page 10-59 and
UPD_ABORT_ON_ERROR on page 10-62.