NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-47
Lock Mode
escalate the row locks to a table lock. If a process holds many row locks on the same
partition of a partitioned table, SQL might escalate the row locks to a partition lock. A
partition lock applies only to the specific partition and not to the entire table. For a
nonpartitioned table, a partition lock is a table lock. If you do not want lock escalation,
use the TABLELOCK OFF option on the CONTROL TABLE statement.
Lock Mode
Lock mode controls access to locked data. You can control lock mode only for rows that
are read.
SHARE lock mode allows multiple users to lock and read the same data. EXCLUSIVE
lock mode limits access to locked data to the lock holder and to other users who specify
BROWSE (but not STABLE or REPEATABLE) access. Lock modes are the same
whether you choose STABLE or REPEATABLE access.
Lock mode is sometimes determined by SQL: SQL ensures that an exclusive lock is in
effect for write operations and usually acquires a shared lock for operations that access
data without modifying it. You choose lock mode in these instances:
•
For queries only, you can choose BROWSE access.
•
On the LOCK TABLE statement, you can choose either EXCLUSIVE or SHARE.
•
On the SELECT statement, you can specify IN EXCLUSIVE MODE or IN SHARE
MODE.
Lock Holder
The lock holder of an object depends on whether the object is audited or nonaudited:
•
Locks on audited objects are held by the TMF transaction in which the request to
access the data was made.
•
Locks on nonaudited objects are held by the process that opens the object: either
SQLCI or a host program.
In host programs, the lock holder is identified by the name of the process acquiring the
lock concatenated with the name of the table or view being locked. For example, if
process PROG holds a lock on a row in protection view VIEW1, the lock holder is
PROG.VIEW1 and not just PROG. If PROG also holds a lock on a row in table
TABLE1, the lock holder is PROG.TABLE1. For shorthand views, the lock holder is the
process name concatenated with the name of the underlying table or tables.
Only the lock holder can release a lock as follows:
•
A TMF transaction releases the locks it holds at the end of the transaction.
•
A process can hold a lock over the duration of one (or more) transactions, or the
process can release the lock before the transaction completes. A process releases the
locks it holds by issuing statements that affect the locks.
An AUDITONLY option is available for the SQLCI AUTOWORK session command
and for the following SQL statements: