NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-43
Examples—LOCK TABLE
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;
•
The following transaction deletes all rows of the JOB table with a job code that is
not assigned to any employee. In this example, suppose 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