SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-68
Effect of VSBB on Concurrency
Effect of VSBB on Concurrency
NonStop SQL/MP provides virtual sequential block buffering (VSBB) for read, update,
and insert operations. Although often more efficient than operations that do not use
VSBB, VSBB can cause increased lock waits and timeouts. In general, if you are
experiencing concurrency problems, use CONTROL TABLE to disable VSBB. You can
use EXPLAIN to determine if SQL is choosing VSBB for your application.

Sequential read operations
For sequential read operations that use VSBB, the disk process locks all rows
within the block (rather than a row at a time). Consequently, SQL operations that
use VSBB, even with STABLE access, can acquire locks that remain in place
longer than operations that do not use VSBB.
If you are experiencing concurrency problems during read operations, disable
VSBB by specifying this directive:
CONTROL TABLE * SEQUENTIAL READ OFF

Sequential update operations
For sequential update operations, SQL performs a sequential read before
performing the update. Consequently, the same problem occurs as for sequential
read operations. If you are experiencing concurrency problems during update
operations, disable VSBB by specifying this directive:
CONTROL TABLE * SEQUENTIAL UPDATE OFF

Sequential insert operations
Table C-7. Limits on Concurrent Utility and DML Operations
DML Operations
Utility Operations
SELECT
BROWSE
SELECT
SHARED
SELECT
EXCLUSIVE
DELETE/INSERT
UPDATE
COPY from a table
Without SHARE option A A A
1
N
With SHARE option A A A A
DUP a table A N N N
LOAD from a table
Without SHARE option A A A
1
N
With SHARE option A A A A
A Allowed
N Not allowed
1
Intermittent conflict can occur. If a SELECT locks a row for five minutes, the utility access to the row can time
out and abort the utility. Also, a SELECT attempting to access a row can abort if a utility locks the row for longer
than the SELECT time out.