Use the UNLOCK TABLE statement to unlock tables that you locked manually by using the LOCK TABLE statement with the LONG option.
The LONG option allows you to hold a lock past the end of the transaction in which the lock was placed. Since there is no natural endpoint for the lock, you must explicitly release a LONG lock by using the UNLOCK TABLE statement.
If a lock is automatic, or if it is manual and not LONG, the server automatically releases the lock at the end of the transaction in which the lock was placed. Thus, there is no need to manually unlock those locks.
When the UNLOCK TABLE statement is used, it does not take effect immediately; instead, the locks are released when the current transaction is committed.
The LOCK/UNLOCK statements apply only to tables. There is no statement to manually lock or unlock individual records.
Parameters, clauses, keywords, and variables
▪ table-name: Name of the table to unlock. You can also specify the catalog and schema of the table by qualifying the table name. If you have a table that is named ALL, you must enclose the table name in double quotation marks.
▪ ALL: Releases all table-level locks on all tables.
Return values
Code
Description
10083
Table table‑name not locked.
13011
Table table‑name not found.
Examples
LOCK TABLE emp IN SHARED MODE;
LOCK TABLE emp IN SHARED MODE TABLE dept IN EXCLUSIVE MODE;
LOCK TABLE emp,dept IN SHARED MODE NOWAIT;
-- Get an exclusive lock that will persist past the end of the -- current- transaction. If you can't get an exclusive lock -- immediately, then wait up to 60 seconds to get it.
LOCK TABLE emp, dept IN LONG EXCLUSIVE MODE WAIT 60;
-- Make the schema changes (or do whatever you needed the -- exclusive lock for).
CALL DO_SCHEMA_CHANGES_1;
COMMIT WORK;
CALL DO_SCHEMA_CHANGES_2;
UNLOCK TABLE ALL; -- at the end of this transaction, release locks. ... COMMIT WORK; ... UNLOCK TABLE "ALL"; -- Unlock the table named "ALL".