SQL/MX Programming Manual for Java

SQL/MX Programming Considerations
HP NonStop SQL/MX Programming Manual for Java—523726-003
4-45
Setting Attributes for Transactions
isolation level of any containing transactions. See Precedence of Transaction Isolation
Levels on page 4-45.
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 an access option in each SELECT statement.
Precedence of Transaction Isolation Levels
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, SQL/MX uses the ISOLATION_LEVEL setting in the
SYSTEM_DEFAULTS table if it exists.
5. If you do not specify isolation-level settings, 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 SQLJ run time uses these
default attributes for the next transaction in a program:
•
Autocommit: ON (or the value specified by the JDBC connection)
•
Isolation level: READ COMMITTED
•
Access mode: READ WRITE