NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-64
Constraints
experiencing concurrency problems, use CONTROL TABLE to disable VSBB. You can
use EXPLAIN to find out 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 the following 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 the following directive:
CONTROL TABLE * SEQUENTIAL UPDATE OFF
•
Sequential insert operations
For sequential insert operations, the disk process acquires a range protector lock on
the row that follows the last row inserted. If the last row inserted is at the end of the
file, the range protector lock is placed at the end of the file; consequently, other
servers cannot insert rows at the end of the table or view.
For inserts into a key-sequenced table that uses a SYSKEY column or a timestamp
as the primary key, VSBB is the usual method for insert operations. If concurrent
servers are inserting records into the table, a high percentage of lock waits and
timeouts might occur.
If you are experiencing concurrency problems during insert operations, you should
disable VSBB by specifying the following directive:
CONTROL TABLE * SEQUENTIAL INSERT OFF
An application designed for NonStop SQL/MP version 1 might experience
concurrency problems under version 2 or later because NonStop SQL/MP extended
VSBB to update and insert operations in version 2. If a change in concurrency
occurs when you move an application from version 1, check VSBB usage.
Constraints
Constraints are SQL objects that help to protect the integrity of data in a table by
specifying a condition or conditions that all the values in a particular column of the table
must satisfy.
Adding a constraint allows you to determine whether values exist that violate the
constraint because SQL rejects the constraint if such values exist. Adding the constraint