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

Introduction
HP NonStop SQL/MX Reference Manual—523725-004
1-13
User-Defined and System-Defined Transactions
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-225. However, even if a
transaction is initiated implicitly, you must end a transaction explicitly with the COMMIT
WORK statement or the ROLLBACK WORK statement.
Rules for DML Statements
•
DML statements executing on audited tables, views of audited tables, and mixed
views must be performed within a transaction, except when reading data with
READ UNCOMMITED ACCESS.
•
If deadlock occurs, the DML statement is canceled, but the transaction continues.
Audited and Nonaudited Tables
The TMF product works only on audited tables, so a transaction does not protect
operations on nonaudited tables. The simplest approach is to make all tables audited.
The AUDIT file attribute is the default when a table is created.
Nonaudited tables are not protected by transactions and follow a different locking and
error handling model than audited tables. Certain situations such as DML error
occurrences can lead to inconsistent data within a nonaudited table or between a
nonaudited table and its indices.
Effect of AUTOCOMMIT Option
AUTOCOMMIT is an option that can be set in a SET TRANSACTION statement. It
specifies whether NonStop SQL/MX will commit automatically, or roll back if an error
occurs, at the end of statement execution. This option applies to any statement for
which the system initiates a transaction. See SET TRANSACTION Statement on
page 2-223.