NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-84
Considerations—CONTROL TABLE
performance of SQL statements by reducing the number of messages exchanged and
the amount of data transferred between the file system and the disk process.
The following guidelines apply to sequential block buffering operations enabled by
the SEQUENTIAL option:
°
Sequential INSERT buffering applies to INSERT operations performed in
sequential primary key, clustering key, or SYSKEY order.
°
Sequential READ buffering applies to explicit or implicit READ operations
performed in sequential primary key, clustering key, SYSKEY, or index order.
Note that buffering of READ operations can occur implicitly with a SELECT,
UPDATE, DELETE, or cursor statement.
°
Sequential UPDATE buffering applies to UPDATE WHERE CURRENT
operations and other UPDATE operations performed on a set of sequential rows.
°
For INSERT and UPDATE operations on audited tables, any errors returned by
the disk process in flushing the buffer cause the current transaction to abort. For
nonaudited tables, errors returned by the disk process do not abort the
transaction, but might leave the table and its indexes inconsistent. In this case
SQL reports a possible loss of data by returning file system error 122.
°
After a sequential INSERT or UPDATE operation has begun, any other DML
operation on the same table (directly or through a view using the same
underlying table) within the same process flushes the buffer and interrupts the
sequential operation.
°
For INSERT and UPDATE operations, any errors the disk process encounters
while flushing the buffer are returned to the statement that triggers the buffer
flush, rather than to the INSERT or UPDATE statement. For more information,
see Conditions that flush or invalidate the buffer
on page C-85.
•
VSBB can reduce concurrency
For sequential read operations that use STABLE access, virtual sequential block
buffering (VSBB) can reduce concurrency for other applications that need exclusive
locks on the rows in a block. (STABLE access normally provides greater
concurrency than REPEATABLE access for sequential read operations, but this is
not true with VSBB because the disk process does not release locks on any rows
within a virtual block until the cursor moves to the next block.)
To disable VSBB for read operations, use this directive:
CONTROL TABLE * SEQUENTIAL READ OFF
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
applications are inserting into the table, a high percentage of lock waits and timeouts
might occur. (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,
preventing other servers from inserting rows at the end of the table or view.)