SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Transaction Management
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
14-6
Starting a Transaction
Precedence of Transaction Isolation Levels
NonStop SQL/MX determines the transaction isolation level, based on these settings,
in order of precedence, from highest to lowest:
1. If you specify an access option explicitly in a DML statement, the SQL/MX compiler
compiles the statement with the access option. This access option overrides the
isolation level of any containing transactions.
2. If there are no individual statement access options and you issue a SET
TRANSACTION ISOLATION LEVEL statement, the SQL/MX compiler uses the
setting determined by this SET TRANSACTION statement as the isolation level for
the next transaction.
3. If you do not specify a SET TRANSACTION statement and you issue a CONTROL
QUERY DEFAULT ISOLATION_LEVEL statement, the CONTROL QUERY
DEFAULT statement determines the isolation level.
4. If you do not issue a CONTROL QUERY DEFAULT ISOLATION_LEVEL
statement, NonStop SQL/MX uses the ISOLATION_LEVEL setting in the
SYSTEM_DEFAULTS table if it exists.
5. If you do not specify isolation-level settings, NonStop SQL/MX uses the system-
defined isolation level, which is READ COMMITTED.
Default Transaction Attributes
If you do not explicitly set the transaction attributes, the embedded SQL program uses
these default attributes for the next transaction in a program:
•
Autocommit: OFF by default in embedded SQL in C and COBOL programs. ON by
default at the start of an MXCI session.
•
Isolation Level: READ COMMITTED
•
Access mode: READ WRITE
•
Size of diagnostics area: Thirty conditions
Starting a Transaction
Use a BEGIN WORK statement to start a transaction explicitly:
EXEC SQL BEGIN WORK;
The transaction consists of the sequence of SQL statements that begins immediately
after BEGIN WORK and ends with the next COMMIT or ROLLBACK statement.
If you do not use the BEGIN WORK statement, NonStop SQL/MX automatically starts
a transaction for a statement, provided that an active transaction does not already exist
and that the statement supports implicit transactions. For information on implicit (or
system-defined) transactions, see the SQL/MX Reference Manual.