SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013

A-3

Summary: Effect of Access Options on Concurrency

performed without the WITH SHARED ACCESS option allow read-only access for

concurrent DML operations during most of the DDL operation.

For more information, see WITH SHARED ACCESS OPTION on page W-4. See the

entry for the specific DDL statement you plan to use to determine if that statement

allows the WITH SHARED ACCESS option.

Summary: Effect of Access Options on Concurrency

Concurrency is access to the same data by two or more processes at the same time.

The degree of concurrency available (that is, whether a process that requests access

to data already being accessed is given access or placed in a wait queue) depends on

the purpose of the access (to read data or to update data), on the access option, and

on whether SQL uses VSBB for the access.

For more information, see Concurrency on page C-65.

Table A-1 indicates how access options affect concurrency by listing the accesses

allowed for one transaction (B) to rows currently being accessed by another

transaction (A). All operations indicated in this table use default locking.

Table A-1. Transaction Concurrency Depending on Access Option

TRANSACTION B TRANSACTION A

(Access Request) (Data Currently Accessed)

STABLE REPEATABLE

Lock Type S E S E

BROWSE

SELECT or cursor None I I I I

STABLE OR REPEATABLE

SELECT exclusive E W W W W

SELECT share S I W I W

SELECT default

FETCH for update E

I WWW

FETCH no update S

2, 3

IWIW

INSERT, UPDATE, or DELETE E W W W W

E = Exclusive lock (the default for update operations)

S = Shared lock (the default for read operations)

I = Immediate access

W = Wait until the lock is released

For cursors, FETCH acquires locks on the fetched row.

SQL determines lock mode. SHARE mode is in effect unless the program has updated the table or view, after

which EXCLUSIVE mode is in effect.

Records that do not satisfy a WHERE clause are examined and not returned; these records are always locked

with a shared lock.

Records returned are locked EXCLUSIVE if a DELETE WHERE CURRENT statement has been issued or if

FOR EXCLUSIVE was specified.