NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-44
Locking
•
The following example locks a nonaudited table (SALES.PARTS) and then
explicitly unlocks it after processing:
EXEC SQL
LOCK TABLE SALES.PARTS IN EXCLUSIVE MODE;
EXEC SQL
CONTROL TABLE SALES.PARTS TABLELOCK ON;
...
EXEC SQL
UNLOCK TABLE SALES.PARTS;
EXEC SQL
CONTROL TABLE SALES.PARTS TABLELOCK ENABLE;
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
in order 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 the following 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. See Access Options
on page A-1 for more information.
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.