SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-25
Access Options and Isolation Levels
standard. Isolation levels for transactions are SQL:1999 compliant; you set transaction
isolation levels with the SET TRANSACTION statement.
Isolation Levels Contrasted Between NonStop SQL/MP and NonStop
SQL/MX
•
BROWSE (NonStop SQL/MP) and READ UNCOMMITTED (NonStop SQL/MX)
have the same implementation. This option is available only for the SELECT
statement.
•
STABLE (NonStop SQL/MP) and STABLE (NonStop SQL/MX) have the same
implementation. This option is available only for the SELECT statement.
•
REPEATABLE (NonStop SQL/MP and NonStop SQL/MX) and SERIALIZABLE
(NonStop SQL/MX) have the same implementation; this option holds locks on data
until the end of the transaction.
STABLE Access
How STABLE Access Works
In both SQL/MX and SQL/MP cursor operations using STABLE access, you are
guaranteed another transaction cannot update the row and that the row can be safely
updated without having to check versions of the row data (using UPDATE WHERE
CURRENT OF or DELETE WHERE CURRENT OF). The number of rows that are
locked depends on the value you have set for SYSTEM_DEFAULT attribute
MAX_ROWS_LOCKED_FOR_STABLE_ACCESS.
If you specify STABLE access for an updatable SELECT statement, NonStop SQL/MX
uses that access method. For other statements, NonStop SQL/MX changes the access
method to READ COMMITTED. NonStop SQL/MX does not choose STABLE access
for rowsets.
How READ COMMITTED Differs From STABLE Access
If you choose READ COMMITTED, read a row, and decide to update that row, in the
time between reading the row and making the update, the value might change because
no lock is acquired on the row. If your applications use STABLE access, you might
want to evaluate that use and decide whether you should change the access to
Note. Unless you are writing applications for portability, use isolation levels at the statement
level. The SET TRANSACTION statement might cause a dynamic recompilation of DML
statements within the next transaction. Further, NonStop SQL/MX does not have the
opportunity to optimize query execution plans as it does during static compilation. You can
achieve better performance if you specify isolation levels for individual DML statements.
Note. If your SQL/MP application uses the BROWSE, STABLE, and REPEATABLE keywords,
NonStop SQL/MX accepts the keywords as synonyms for READ UNCOMMITTED, STABLE,
and SERIALIZABLE.