SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-17
STABLE Access
•
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 to decide whether you should change the access to
SERIALIZABLE or whether you should adjust your error handling procedures. You can
rely on the fact that when you read a row, no other transaction can change the row
while you are processing it. Thus, you are guaranteed not to cause any integrity issues
when you do update the row.
READ COMMITTED also guarantees integrity but in a different way. If you update the
row acquired using READ COMMITTED access and another transaction has updated it
since you first read it, a warning is returned to the application on the update. You can
check the warning code returned and take appropriate action (abort the transaction if
necessary, display an appropriate message, retry the read and update, and so on). By
changing your error handling procedures, you can benefit from the increased
concurrency.
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.