The name of the table to lock. You can also specify the catalog and schema of the table by qualifying the table name. You may only lock tables, not views.
SHARED
Shared mode allows others to perform read and write operations on the table. DDL operations are not allowed. Also, shared mode prohibits others from issuing an EXCLUSIVE lock on the same table.
EXCLUSIVE
If a D-table uses pessimistic locking, then an exclusive lock prevents other user from accessing the table in any way (for example, inserting or deleting data, DDL operations, acquiring a lock), except for SELECT statements.
If the case of M-tables (always pessimistic) and optimistic D-tables, an exclusive lock allows other users to perform SELECT and SELECT FOR UPDATE statements on the locked table but prohibits any other activity (inserting or deleting data, DDL operations, acquiring a lock) on that table.
LONG
By default, locks are released at the end of a transaction. If the LONG option is specified, then the lock is not released when the locking transaction commits. If the locking transaction aborts or is rolled back, then all locks, including LONG locks, are released. You must unlock long locks explicitly using the UNLOCK command. LONG duration locks are allowed only in EXCLUSIVE mode. LONG shared locks are not supported.
NOWAIT
Specifies that control is returned to you immediately even if any specified table is locked by another user. If requested lock is not granted, an error is returned.
WAIT
Specifies a timeout (in seconds) for how long system should wait to get requested locks. If requested lock is not granted within that time, an error is returned.
Note The WAIT option is effective on disk-based tables only.
Usage
The LOCK and UNLOCK commands allow you to manually lock and unlock tables. Putting a lock on a table limits access to that object. The LONG option allows you to extend the duration of a manual exclusive lock past the end of the current transaction; in other words, you can keep the table exclusively locked through a series of multiple transactions.
Manual locking is not needed very often. The server’s automatic locking is usually sufficient. For a detailed discussion of locking in general, and the server’s automatic locking in particular, see Concurrency control and locking.
Explicit locking of tables is primarily intended to help database administrators execute maintenance operations in a database without being disturbed by other users. For example, manual locking is typically used in advanced replication setups when making schema changes. For more details, see Upgrading the schema of a distributed system in the solidDB® Advanced Replication User Guide.
Table locks can be either SHARED or EXCLUSIVE.
An EXCLUSIVE lock on a table prohibits any other user or connection from changing the table or any records within the table. If you have an exclusive lock on a table, then other users/connections cannot do any of the following on that table until your exclusive lock is released:
▪INSERT, UPDATE, DELETE
▪ALTER TABLE
▪DROP TABLE
▪LOCK TABLE (in shared or exclusive mode)
Furthermore, if a D-table uses pessimistic locking, then an exclusive lock also prevents others users/connections from doing the following:
▪SELECT FOR UPDATE
Exclusive locks do not prevent other users from SELECTing records from that table. Most other database servers behave differently – they do not allow SELECTs on a table that is locked exclusively.
A SHARED lock is less restrictive than an exclusive lock. If you have a shared lock on a table, then other users/connections cannot do any of the following on that table until your shared lock is released:
▪ALTER TABLE
▪DROP TABLE
▪LOCK TABLE (in exclusive mode)
If you use a shared lock on a table, other users/connections may insert, update, delete, and select from the table.
Shared locks on a table are somewhat different from shared locks on a record. If you have a shared lock on a record, then no other user may change data in the record. If you have a shared lock on a table, then other users may still change data in that table.
More than one user at a time may have shared locks on a table. If you have a shared lock on the table, other users may also get shared locks on the table. However, no user may get an exclusive lock on a table when another user has a shared lock (or exclusive lock) on that table.
The LOCK command takes effect at the time it is executed. If you do not use the LONG option, then the lock will be released at the end of the transaction. If you use the LONG option, the table lock lasts until you explicitly unlock the table. The table lock will also be released if you roll back the transaction in which the lock was placed – LONG locks only persist across transactions if you commit the transaction in which you placed the LONG lock.
The LOCK/UNLOCK TABLE commands apply only to tables. There is no command to manually lock or unlock individual records within a table.
You can lock more than one table and specify different modes within one LOCK command. If the lock command fails, then none of the tables are locked. If the lock command was successful, then all requested locks are granted.
If the user does not specify a wait option (NOWAIT or WAIT seconds), then the default wait time is used. That is the same as the deadlock detection timeout. The WAIT option is effective on disk-based tables only.
To use the LOCK TABLE command to issue a lock on a table, you must have insert, delete or update privileges on that table. There is no GRANT command to give other users LOCK and UNLOCK privileges on a table.
Return values
Error code
Description
10014
Resource is locked.
13047
No privilege for operation.
13011
Table <tablename> is not found.
Examples
LOCK TABLE emp IN SHARED MODE;
LOCK TABLE emp IN SHARED MODE TABLE dept IN EXCLUSIVE MODE;