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

ManualsBrandsHP ManualsServerHP NonStop G-Series

Introduction

HP NonStop SQL/MX Reference Manual—523725-004

1-11

Transaction Management

A transaction (a set of database changes that must be completed as a group) is the

basic recoverable unit in case of a failure or transaction interruption. Transactions can

be defined during an MXCI session or in a host program. The typical order of events is:

1. Transaction is started.

2. Database changes are made.

3. Transaction is committed.

If, however, the changes cannot be made or if you do not want to complete the

transaction, you can abort the transaction so that the database is rolled back to its

original state.

All SQL/MX tables must be audited, and although SQL/MP tables can be nonaudited,

HP recommends that they be audited. Transactions are managed by the HP

NonStop Transaction Management Facility (TMF). This product simplifies the task of

maintaining data consistency for databases being updated by concurrent transactions.

For more information on TMF, see the Transaction Management Facility (TMF)

Introduction.

This subsection discusses these considerations for transaction management:

•

Statement Atomicity on page 1-11

•

User-Defined and System-Defined Transactions on page 1-13

•

Rules for DML Statements on page 1-13

•

Effect of AUTOCOMMIT Option on page 1-13

•

Concurrency on page 1-14

•

Transaction Access Modes on page 1-19

•

Transaction Isolation Levels on page 1-20

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. See Statement

Atomicity on page 10-62 for information on this default.

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.