SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013

A-1

Access Options

Access options on DDL or DML statements determine the locking or access mode that

SQL uses when executing the statements. Access options affect the consistency of the

data your application views and the degree of concurrency your program has with

other programs that use the same data.

Access Options on DML Statements

The DML statements SELECT, INSERT, UPDATE, and DELETE include access

options that control lock duration and have row-level granularity. The possible settings

for access options on DML statements are:

BROWSE access is not available on DML statements that modify the database.

STABLE access and REPEATABLE access are available on DML statements that

modify the database. STABLE access and REPEATABLE access are discussed in

detail in the coming subsections.

STABLE Access Option on DML Statements

STABLE locks all data accessed through the DML statement but releases locks on

unmodified data as soon as possible. STABLE access locks modified data in audited

tables until the end of the transaction.

In host programs that use cursors, STABLE locks an unmodified row only when the

row is in the current position and releases the lock at the next FETCH on the cursor.

CLOSE cursor releases the lock from the last FETCH.

For modified rows in audited tables, STABLE access uses exclusive locks held by the

HP NonStop Transaction Management Facility (TMF) transaction that are released

only when the entire transaction ends.

For nonaudited tables, all locks are held by the process (not the transaction) and

STABLE access releases locks when the statement finishes.

BROWSE Does not lock data. Reads locked data that might be inconsistent.

Used for browse access or when a table is locked. Minimum

consistency, maximum concurrency.

STABLE Locks all data accessed but releases locks on unmodified rows

without waiting for the end of the transaction. The default for read

operations.

REPEATABLE Locks all data accessed until the end of the transaction. Maximum

consistency, minimum concurrency. The default for update

operations.