SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-46
Considerations—LOCK TABLE
SHARE or EXCLUSIVE
specifies the locking mode:
If you request a SHARE lock on a table locked with an EXCLUSIVE lock by
another user, your request waits until the EXCLUSIVE lock is released.
If you request an EXCLUSIVE lock on a table and any part of the table is locked by
another user, your request waits until the lock is released, or until your lock request
times out and SQL returns an error message.
Considerations—LOCK TABLE

LOCK TABLE requires authority to read the table. Locking a view requires authority
to read the view and its underlying tables.

A SELECT statement automatically acquires SHARE locks unless you specify
BROWSE access. (DELETE, INSERT, and UPDATE automatically acquire
EXCLUSIVE locks.) You can use LOCK TABLE with the EXCLUSIVE option to
force use of EXCLUSIVE locks for a subsequent SELECT, but LOCK TABLE will
lock the whole table.

Follow each LOCK TABLE statement with a CONTROL TABLE TABLELOCK ON
directive for the same table. This directive specifies that the table will be locked at
execution time. The compiler uses this information to select the most efficient
execution path for your data.

Audited tables never need to be explicitly unlocked. An audited table can be locked
only within a TMF transaction and is automatically unlocked when the transaction
ends. (UNLOCK TABLE does not affect audited tables.)
You unlock nonaudited tables with UNLOCK TABLE or FREE RESOURCES. In
SQLCI (but not in host programs), nonaudited tables are sometimes unlocked
automatically. If the AUTOWORK session option (without AUDITONLY) is on,
locked nonaudited tables are unlocked after the next DML statement. Locked
tables are also unlocked when you issue COMMIT WORK or ROLLBACK WORK
(without AUDITONLY) to end a user-defined transaction, when you press the Break
key when BREAK_KEY is on, or when your SQLCI session ends.

LOCK TABLE attempts to lock all partitions and indexes of any table it locks. If a
partition or index is not available or if the lock request times out, LOCK TABLE
displays a warning (in SQLCI) or returns the name of the unavailable partition or
index to the SQLCA (in host programs) and continues to request locks on other
partitions and indexes.
SHARE Others can read, but not delete, insert, or update the table or
view.
EXCLUSIVE Others can read with BROWSE access, but cannot read with
STABLE or REPEATABLE access and cannot delete, insert, or
update.