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

Introduction
HP NonStop SQL/MX Reference Manual—540440-003
1-13
Statement Atomicity
•
Rules for DML Statements on page 1-15
•
Effect of AUTOCOMMIT Option on page 1-15
•
Concurrency on page 1-15
•
Transaction Access Modes on page 1-21
•
Transaction Isolation Levels on page 1-21
Statement Atomicity
To maintain database consistency, transactions must be controlled so that they either
complete successfully or are aborted. SQL/MX Release 1.8 automatically aborted
transactions if an error occurred while performing an SQL statement. SQL/MX Release
2.x by default does not automatically abort transactions following an error, in most
situations.
SQL/MX Release 2.x guarantees that an individual SQL statement within a transaction
either completes successfully or has no effect on the database. To retain the behavior
of SQL/MX Release 1.8, use the UPD_ABORT_ON_ERROR default. For more
information, see Statement Atomicity on page 10-64.
When an INSERT, UPDATE, or DELETE statement encounters an error, that
transaction is not aborted, but continues. The effect of the SQL/MX statement is rolled
back, so the statement has no effect on the database, but the transaction is not
aborted. This functionality is provided through an internal feature called savepoints.
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, 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.
•
The AUTOCOMMIT option must be set to ON.
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.