SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-2
Access Options on DDL Statements
STABLE access provides sufficient consistency for any process that does not require a
repeatable read capability.
REPEATABLE Access Option on DML Statements
The REPEATABLE access option locks all data accessed through the DML statement
and holds the locks on data in audited tables until the end of the transaction.
For audited tables, REPEATABLE access uses shared locks for unmodified rows and
exclusive locks for modified rows—but all locks are held by the TMF transaction and
not released until the transaction ends. For audited tables, REPEATABLE access
prevents other users from inserting or modifying rows in the range of rows examined
by the DML statement.
For nonaudited tables, locks are held by the executing process, not the transaction, but
ending a transaction normally releases locks on both audited and nonaudited tables.
To hold locks on nonaudited data across multiple transactions, specify the AUDITONLY
option on COMMIT WORK, ROLLBACK WORK, and FREE RESOURCES statements
within the transaction.
For nonaudited tables outside of transactions in host programs, locks acquired with
REPEATABLE access remain in effect until the program releases them with UNLOCK
TABLE or FREE RESOURCES. In programs that use cursors, a lock on a row takes
effect when the FETCH for the row executes.
For nonaudited tables, if sequential block buffering (either RSBB or VSBB) is used to
access a nonaudited table, a table lock is always acquired regardless of the access
option (stable or repeatable) or the CONTROL TABLE TABLELOCK option
specification.
Within SQLCI-defined transactions when AUTOWORK is ON without the AUDITONLY
option, REPEATABLE access locks nonaudited tables only until SQLCI commits or
rolls back the transaction. If AUTOWORK is ON with the AUDITONLY option,
REPEATABLE access locks nonaudited tables until you release them.
REPEATABLE does not provide full, repeatable read-protection for nonaudited tables
within SQLCI or a host program. REPEATABLE does not prevent other users from
inserting rows in the originally selected range of rows. Use the LOCK TABLE statement
if you need such protection.
Access Options on DDL Statements
The only access option available on DDL statements is the WITH SHARED ACCESS
option, which allows users to control whether or not concurrent DML statements can
acquire write access to the SQL objects being changed by the DDL operation. Only a
few DDL statements currently include the WITH SHARED ACCESS option.
DDL operations performed with the WITH SHARED ACCESS option allow DML
operations by other processes to acquire read and write access to objects being
changed during all but the final phase of the DDL operation. Most DDL operations