SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-19
Lock Mode
Lock Mode
Lock mode controls access to locked data. The two modes are these:
•
Exclusive. The lock owner can access and modify the data. Users who have
specified browse access can read the data. This is typically specified for multistep
updates or deletes, where data is selected and then updated or deleted, to
eliminate the need to convert a shared lock (for the select) to an exclusive lock (for
the update or delete).
Exclusive locking mode disables index-only access, because it indicates that the
base table row will be deleted or updated.
•
Shared. Multiple users can lock and read the same data. Inserts, updates, and
deletes, however, can only be done by the lock owner.
In the SELECT statement, you can specify IN EXCLUSIVE MODE or
IN SHARE MODE.
Exclusive and shared locks can be used at either the table or row level of granularity.
Lock modes are the same whether you choose stable access or repeatable access.
Lock mode is sometimes determined by the system. SQL ensures that an exclusive
lock is in effect for modified data. For data accessed but not modified, SQL usually
provides a shared lock.
Lock Granularity
Granularity controls the number of rows affected by a single lock. Granularity can refer
to a table, a partition, a subset of rows, or a single row.
The level of concurrency decreases as the size of the lock unit increases.
The LOCKLENGTH attribute for a base table controls the granularity of row locks for
the table. You can control table locks with the LOCK TABLE and CONTROL TABLE
statements; otherwise, the system determines the granularity by considering the
access option you specify, the table size and definition, and the estimated percentage
of rows that the query would access.
SQL might choose a different access path if a table lock can be used. In SQL, you can
specify the use of table locks with these commands:
•
In the LOCK TABLE statement, you can choose either the EXCLUSIVE or
SHARED option:
LOCK TABLE table-name
•
You can use the TABLELOCK option to control system-chosen table locks from an
SQL compile-time directive. For example, this statement instructs the SQL
compiler to use a table lock for any subsequently compiled DML statements that
specify the CUSTOMER table:
CONTROL TABLE table-name TABLELOCK ON