NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-42
Considerations—LOCK TABLE
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
•
Authorization requirements
LOCK TABLE requires authority to read the table. Locking a view requires
authority to read the view and its underlying tables.
•
Modifying default locking
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.
•
Improving efficiency
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.
•
Unlocking locked tables
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.
•
Partitions or indexes
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.
Examples—LOCK TABLE
•
The following example locks an audited table with an EXCLUSIVE lock
(presumably at a time when few users need access to the database) to perform an