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-5
Setting Attributes for Transactions
Isolation Level Setting
The isolation level specifies the level of data consistency defined for the transaction
and the degree of concurrency the transaction has with other transactions that use the
same data. The isolation level of a transaction can be READ UNCOMMITTED, READ
COMMITTED, or SERIALIZABLE (or REPEATABLE READ). For more information, see
the SQL/MX Reference Manual.
In an embedded SQL program, avoid using the SET TRANSACTION ISOLATION
LEVEL statement because:
•
It always starts a compiler process.
•
It could cause automatic recompilation.
•
It could adversely affect UPDATE, INSERT, and DELETE statements at run time
within its control flow scope.
The SET TRANSACTION ISOLATION LEVEL statement, regardless of whether it was
statically compiled, always executes dynamically and applies its setting at run time.
Therefore, this statement always starts a compiler process. To avoid performance
costs, use a CONTROL QUERY DEFAULT ISOLATION_LEVEL statement instead.
For more information on coding CONTROL statements, see Using CONTROL
Statements on page 2-12.
The SET TRANSACTION ISOLATION LEVEL statement could cause automatic
recompilation of DML statements in the next transaction. If a DML statement is
statically compiled and does not specify an explicit access option (for example, READ
COMMITTED, SERIALIZABLE, and so on), its access option at compile time is
determined by the ISOLATION_LEVEL setting, if present, or by the system-defined
isolation level, which is READ COMMITTED. If NonStop SQL/MX executes the DML
statement after executing a SET TRANSACTION ISOLATION LEVEL statement with a
different isolation level setting, the SQL/MX executor automatically recompiles the DML
statement. To avoid automatic recompilation, specify explicit access options in
individual DML statements. The access options in DML statements override the
isolation level of any containing transactions. See Precedence of Transaction Isolation
Levels on page 14-6.
If you set the isolation level to READ UNCOMMITTED, the access mode becomes
READ ONLY by default. As a result, INSERT, UPDATE, and DELETE statements
within the scope of a SET TRANSACTION or CONTROL QUERY DEFAULT statement
fail to compile or execute. INSERT, UPDATE, and DELETE statements require the
access mode to be READ WRITE. To avoid compilation or run-time errors, make sure
that subsequent transactions do not contain INSERT, UPDATE, and DELETE
statements if you specify READ UNCOMMITTED in a SET TRANSACTION or
CONTROL QUERY DEFAULT statement. Instead of using those statements, consider
specifying READ UNCOMMITTED as the access option in each SELECT statement.