SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-48
Locking
Locking
To protect the integrity of the database, SQL provides locks on data. For example, SQL
locks a row when an executing process (either SQLCI or a host program) accesses a
row to modify it. The lock ensures that no other process simultaneously modifies the
same row.
Default locking normally protects data but reduces concurrency. If your application has
problems with lock contention (as indicated by MEASURE LOCKWAIT counts or by the
value in the SQLSA WAITS field), you might want to use options that control the
characteristics of locks.
Locks have these characteristics:

Duration (short or long)

Granularity (table lock, partition lock, subset of rows, or single row)

Mode (exclusive, shared, no lock)

Holder (transaction or process)
Lock Duration
Lock duration controls how long a lock is held. You can specify lock duration only for
the read portion of a statement. All write locks are held until the end of the transaction
(for audited tables) or until the program releases the locks (for nonaudited tables).
You choose lock duration by specifying access options: STABLE for a short time or
REPEATABLE for a long time. For more information, see Access Options on page A-1.
You can also use the LOCK TABLE statement to lock a table. How long the lock is held
depends on whether the locked table is audited or nonaudited and whether the data is
locked by a cursor.
Table L-1 on page L-49 lists SQL operations that release locks and shows the effects
of STABLE and REPEATABLE access mode on lock duration. The table assumes
default locking (shared locks for reads and exclusive locks for updates) and also
assumes the locking strategy uses the minimum number of locks. SQL can use
additional locks (either row locks or table locks) if it determines that additional locks are
necessary to protect data integrity or to provide faster data access.
If a partitioned table is randomly accessed with STABLE ACCESS, the lock is held until
the next access to the same partition (not the next access to the table). This can cause
a lock to be held longer than expected.
When using sequential access with Sequential Block Buffering and STABLE access,
SQL holds the lock until it attempts to access a row that is not in the buffer. Thus, rows
at the beginning of the buffer remain locked while subsequent rows are being fetched
by the program.