SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-17
Access Option
Each row locked with repeatable access stays locked until the corresponding
transaction is committed.
•
Stable access
Stable access requires that when a row is retrieved, it must not be locked in an
exclusive manner by another transaction. Stable access locks all data accessed
through the DML statement. This access is useful for applications that want to read
only committed data. Stable access is also known as cursor stability or test lock.
In general, each row locked with stable access is released when the next row is
read, if the row was accessed but not updated. For standalone SELECT
statements with stable access, SQL releases the lock as soon as the row is
returned to the application.
Stable access is the default.
I/O buffering options influence access option behavior. For example, stable access with
VSBB locks all rows and does not release them until SQL is finished with the entire
block. For more information, see Reducing Messages With Buffering Options on
page 4-21.
The option you specify can influence the resources required for a query. In general,
these rules apply:
•
Use browse access if possible, but use it with caution—only if inconsistent data is
acceptable.
The BROWSE ACCESS option enables you to read data that is currently being
updated or deleted. This option allows you to read data for which an associated
transaction might not have been committed. This option is especially effective for a
read-only database.
BROWSE ACCESS requires no lock management and allows other techniques
such as RSBB to be used. Many queries (such as those used for application
navigation through a database) can use BROWSE ACCESS successfully.
If potentially inconsistent data is unacceptable, do not specify browse access, but
make sure repeatable access is used only where necessary. (To determine current
access path, review the EXPLAIN plan as shown in Section 6, Analyzing Query
Performance.) If you do use data accessed with BROWSE ACCESS, supply
update protection by comparing the BROWSE data with actual database contents
during update and delete operations to make sure that you do not lose interim
updates.
If you omit BROWSE ACCESS, the default locking mode is STABLE. The executor
obtains a shared lock on each row to ensure that the rows are not changed while
they are being read. If the database is not audited by TMF, stable access is
especially expensive because it prevents the executor from using some of its more
efficient scanning mechanisms.