NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-3
Access Options on DDL Statements
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 about concurrency, see Concurrency
on page C-60.
The following table shows how access options affect concurrency by showing 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.
Transaction Concurrency Depending on Access Option
Because the previous table does not show lock duration, STABLE and REPEATABLE
access options appear similar for Transaction B. See Locking on page L-44 for more
information about lock duration, about modifying default locking, and about the effects
of locking on concurrency.
TRANSACTION B TRANSACTION A
(Access Request)
(Data Currently
Accessed)
STABLE REPEATABLE
Lock TypeSESE
BROWSE
SELECT or cursor None IIII
STABLE OR REPEATABLE
SELECT exclusive E WWWW
SELECT share S IWIW
SELECT default
1
1
W
1
W
FETCH for update E
2
I WWW
FETCH no update S
2, 3
IWIW
INSERT, UPDATE,
or DELETE
E WWWW
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.
1
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.
2
Records that do not satisfy a WHERE clause are examined and not returned; these records are
always locked with a shared lock.
3
Records returned are locked EXCLUSIVE if a DELETE WHERE CURRENT statement has been
issued or if FOR EXCLUSIVE was specified.