SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-50
Lock Mode
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 non-audited
as follows:

Locks on audited objects are held by the TMF transaction in which the request to
access the data was made. The Transaction ID of the TMF Transaction that locked
the object is the lock holder.

Locks on nonaudited objects are held by the process that opens the object: either
SQLCI or a host program.
Process ID or Process name of the process (host program/SQLCI) which has
locked the object, is the lock holder.
The lock holder can release a lock only under the following scenarios:

A TMF transaction releases the locks it holds at the end of the transaction.

A process holds a lock over the duration of one (or more) transactions, or the
process releases 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 these SQL statements:
COMMIT WORK