SQL/MX 2.x Reference Manual (H06.04+)

Introduction
HP NonStop SQL/MX Reference Manual—540440-003
1-11
Lock Mode
You can prevent this by setting the TABLELOCK default to OFF. However, if the
number of rows locked reaches the value of MaxLocksPerTCB, that transaction will not
be able to obtain any more locks, and may be aborted. Depending on the operation,
some updates must be backed out which can seriously affect the application's
performance.
You can control the number of locks that a transaction can hold on a specific volume
and lock escalation by using SCF to change MaxLocksPerTCB to a maximum of
100,000. Use this command:
SCF ALTER $volume, MAXLOCKSPERTCB n
Lock Mode
Lock mode controls access to locked data. You can specify 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 READ UNCOMMITTED (but not READ COMMITTED or SERIALIZABLE)
access. Lock modes are the same when you choose READ COMMITTED or
SERIALIZABLE access.
Lock mode is sometimes determined by NonStop SQL/MX. NonStop SQL/MX 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:
•
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 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
MXCI or a host program.
Only the lock holder can release a lock:
•
A transaction releases the locks it holds at the end of the transaction in either of
these cases:
°
Locks on data read using SERIALIZABLE access
°
Locks on rows updated