SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-20
Waiting For Locks
LOCK TABLE is an executable SQL statement. If you use LOCK TABLE, the SQL
compiler is not aware of the table lock before compiling subsequent statements
because each SQL statement is compiled independently. The LOCK TABLE statement
might even be in a separate program from subsequent statements.
CONTROL TABLE is a compiler directive. If you use CONTROL TABLE, the SQL
compiler is aware of the table lock before compiling queries that reference the
specified table.
Requesting a table lock eliminates the overhead of row locking and therefore reduces
the overhead on queries that access many rows. By requesting a table lock (or by
specifying browse access), you make it more likely that VSBB and parallel execution
will be selected. Table locking, however, inhibits concurrency; other users cannot
access the table while it is locked. For batch-type queries without OLTP activity, an
application should request table locks with the CONTROL TABLE directive.
Waiting For Locks
You can use the RETURN/WAIT IF LOCKED option to enable or disable the wait
mechanism for lock requests on data that is already locked by other users. This
example enables the wait mechanism so that a lock request on locked data is held for
60 seconds (the default) before a timeout occurs:
CONTROL TABLE SALES.CUSTOMER WAIT IF LOCKED TIMEOUT DEFAULT
Performance Implications
By changing the characteristics of locks, you might increase the number of
transactions that can be handled in a given time frame. The more concurrency, the
greater number of transactions that can complete.
Access option (browse, stable, repeatable) and lock mode (exclusive or shared) can
affect the query execution plan chosen by the optimizer. Consider these:
•
Exclusive mode disables index-only access because exclusive mode usually
indicates that the base table row will be deleted or updated.
•
Access option can affect whether sequential block buffering is chosen. For
example, real sequential block buffering (RSBB) is used only when browse access
is specified, or the entire table is locked with a CONTROL TABLE directive.
•
Stable access often prohibits parallel execution of queries.