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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-157
Examples of LOCK TABLE
You can use LOCK TABLE with the EXCLUSIVE option to force the use of
EXCLUSIVE locks for a subsequent SELECT; however, keep in mind that LOCK
TABLE locks the entire table.
Unlocking Locked Tables
Audited tables do not need to be explicitly unlocked. An audited table can be locked
only within a transaction and is automatically unlocked when the transaction ends.
You can unlock nonaudited tables by using UNLOCK TABLE. However, locked tables
are unlocked automatically when you issue COMMIT WORK or ROLLBACK WORK to
end a user-defined transaction or when your MXCI session ends. Only SQL/MP tables
can be nonaudited.
Effect of AUTOCOMMIT Option
At the start of an MXCI session, the AUTOCOMMIT option is ON by default. When this
option is ON, NonStop SQL/MX automatically commits any changes, or rolls back any
changes, made to the database at the end of statement execution. When you issue a
LOCK TABLE statement in MXCI without turning off AUTOCOMMIT, NonStop SQL/MX
locks the table temporarily, and then commits the transaction at the end of the LOCK
TABLE statement and releases the locks. If you use LOCK TABLE in MXCI, turn off
AUTOCOMMIT by using the SET TRANSACTION statement. See SET
TRANSACTION Statement on page 2-218.
Partitions and 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 and continues to request locks on other partitions and indexes.
Examples of LOCK TABLE
•
Lock an audited table with an EXCLUSIVE lock (at a time when few users need
access to the database) to perform a series of updates:
BEGIN WORK;
LOCK TABLE persnl.employee
IN EXCLUSIVE MODE;
UPDATE persnl.employee
SET salary = salary * 1.05
WHERE jobcode <> 100;
COMMIT WORK;
COMMIT WORK automatically unlocks the table when it ends the transaction.