SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-47
Examples—LOCK TABLE
Examples—LOCK TABLE

This example locks an audited table with an EXCLUSIVE lock (presumably at a
time when few users need access to the database) to perform an update. The
CONTROL TABLE statement ensures the most efficient operation. COMMIT
WORK automatically unlocks the table when it ends the transaction.
>> VOLUME $VOL1.PERSNL;
>> BEGIN WORK;
>> CONTROL TABLE EMPLOYEE TABLELOCK ON;
>> LOCK TABLE EMPLOYEE IN EXCLUSIVE MODE;
--- SQL operation complete
>> UPDATE EMPLOYEE SET SALARY = SALARY * 1.05
+> WHERE JOBCODE <> 100;
--- 45 row(s) updated.
>> COMMIT WORK;

This example deletes all rows of the JOB table with a job code that is not assigned
to any employee. Suppose that the JOB table is nonaudited and you want locks
held for several transactions. Because the EMPLOYEE table is audited and you
are locking it, you must define a TMF transaction. At the end of the transaction, the
EMPLOYEE table lock is released by the system. Unless AUTOWORK is set to
ON without AUDITONLY, you must use the UNLOCK TABLE command to release
the lock on the JOB table because the table is nonaudited.
>> VOLUME $VOL1.PERSNL;
>> BEGIN WORK;
>> CONTROL TABLE JOB TABLELOCK ON;
>> CONTROL TABLE EMPLOYEE TABLELOCK ON;
>> LOCK TABLE JOB IN EXCLUSIVE MODE;
--- SQL operation complete
>> LOCK TABLE EMPLOYEE IN SHARE MODE;
--- SQL operation complete
>> DELETE FROM JOB WHERE JOBCODE NOT IN (SELECT DISTINCT
+> JOBCODE FROM EMPLOYEE);
--- 8 row(S) deleted.
>> COMMIT WORK AUDITONLY;
...
>> UNLOCK TABLE JOB;
--- SQL operation complete

This example locks a nonaudited table (SALES.PARTS) and then explicitly unlocks
it after processing:
EXEC SQL
LOCK TABLE SALES.PARTS IN EXCLUSIVE MODE;
EXEC SQL
CONTROL TABLE SALES.PARTS TABLELOCK ON;
...
EXEC SQL
UNLOCK TABLE SALES.PARTS;
EXEC SQL
CONTROL TABLE SALES.PARTS TABLELOCK ENABLE;