SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-65
Concurrency
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 that is already being accessed is given access or placed in a wait queue)
depends on the purpose of the access (read or update), on the access mode, and on
whether virtual sequential block buffering (VSBB) is used for the access.
NonStop SQL/MP provides concurrent database access for most operations,
controlling access through the locking mechanism and the mechanism for opening and
closing tables. For DML operations and for some DDL operations, the access options
and locking options you select affect the degree of concurrency. For more information
about these options, see Access Options on page A-1, Locking on page L-48, and
WITH SHARED ACCESS OPTION on page W-4.
Concurrent access is not possible for all DDL and utility operations, especially those
that change timestamps of SQL objects. To maximize concurrency for operations that
would not otherwise allow it, NonStop SQL/MP performs certain DDL operations in
phases:

ALTER INDEX, ALTER TABLE, and CREATE INDEX operations that use WITH
SHARED ACCESS allow concurrent access by DML statements throughout all but
a relatively brief commit phase at the end of the operation.

ALTER TABLE, ALTER INDEX, CREATE CONSTRAINT, UPDATE STATISTICS,
and CREATE INDEX operations without WITH SHARED ACCESS allow
concurrent access by DML statements that use SELECT with BROWSE or
SHARED access during an initial scan phase, but lock out DML accesses during a
later update phase.
These three tables shows the limits on concurrency that are imposed by DDL and
utility operations:

Table C-5 on page C-65 shows DDL operations that limit (or are limited by) DML
operations. For DDL operations that occur in two phases, the table shows both
scan and update (change timestamp) phases.

Table C-6 on page C-67 shows the forms of the ALTER statement that change the
timestamp of an object. The concurrency of an ALTER operation depends on
whether ALTER changes a timestamp.

Table C-7 on page C-68 shows utility operations that limit concurrency.
Table C-5. Summary of Concurrent DDL and DML Operations (page 1 of 3)
DML Operations in Progress
DDL Operations You
Can Start
SELECT
BROWSE
SELECT
SHARED
SELECT
EXCLUSIVE
DELETE/INSERT
UPDATE
ALTER