Locks a table.

Syntax

LOCK TABLE name [, ...] IN lockmode MODE [ NOWAIT ]

Where lockmode is one of the following items:

ROW SHARE | ROW EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE

Description

You can use the LOCK TABLE command to acquire a table-level lock. If conflicting locks exist, the command waits until all of the conflicting locks are released and locks the table by default. If you specify NOWAIT, the LOCK TABLE command does not wait to acquire the desired lock. If the lock cannot be immediately acquired, the command ends and an error occurs. After you obtain a lock, the lock is held until the current transaction ends. The UNLOCK TABLE command is unavailable. Tables remain lock until transactions come to an end.

When acquiring automatic locks for the commands that reference tables, PolarDB databases compatible with Oracle use the least restrictive lock mode possible. LOCK TABLE is provided for cases when you need more restrictive locking. For example, an application runs a transaction at the isolation level of read committed and the stability of data in a table needs to be ensured during the transaction. To achieve this, you can lock the table in the SHARE mode before querying. This prevents concurrent data changes and ensures a stable view of committed data for subsequent table reads because the SHARE lock mode conflicts with the ROW EXCLUSIVE lock acquired by writers. Your LOCK TABLE name IN SHARE MODE statement waits until concurrent holders of ROW EXCLUSIVE locks commit or roll back. Therefore, after you obtain the lock, no uncommitted writes exist. In addition, none can perform operations on the table until you release the lock.

To achieve a similar effect when running a transaction at the serializable isolation level, you must run the LOCK TABLE statement before running data modification statement. A serializable transaction view of data is frozen after its first data modification statement begins. A later LOCK TABLE will still prevent concurrent writes, but the values that the transaction reads may differ from the latest committed values.

If a serializable transaction is going to change data in the table, it needs to lock the table in the SHARE ROW EXCLUSIVE mode instead of SHARE mode.

This ensures that only one transaction of this type runs at a time. Otherwise, a deadlock may occur. Two transactions may lock the table in the SHARE mode at the same time, and then neither of them can acquire the lock in the ROWEXCLUSIVE mode to perform updates. Note that locks never conflict within a transaction, so a transaction can lock a table in the ROW EXCLUSIVE mode when it holds the SHARE mode. However, a transaction cannot acquire the ROW EXCLUSIVE lock if another transaction holds the SHARE lock. To avoid deadlocks, make sure that all transactions acquire locks on the same objects in the same order. If a single object allows multiple lock modes, transactions must acquire the most restrictive mode first.

Parameters

Parameter Parameter
name The name of the table to be locked. The name can be schema-qualified.

The LOCKTABLE a, b command is equivalent to LOCK TABLE a; LOCK TABLE b. The tables are locked one by one in the order specified in the LOCK TABLE command.

lockmode The lock mode that specifies the locks with which this lock conflicts.

If no lock mode is specified, the server uses the most restrictive mode, ACCESS EXCLUSIVE. ACCESS EXCLUSIVE is not compatible with Oracle databases. In PolarDB databases compatible with Oracle, this mode ensures that no other transaction can access the locked table in any manner.

NOWAIT Specifies that the LOCKTABLE command does not wait for conflicting locks to be released. If you cannot immediately acquire the specified lock, the transaction ends.

Notes

All forms of LOCK require UPDATE and/or DELETE privileges.

LOCK TABLE is useful only inside a transaction block because the lock is dropped when the transaction ends. A LOCK TABLE command that is used outside a transaction block forms a self-contained transaction, so the lock will be dropped when you obtain it.

LOCK TABLE only deals with table-level locks, so the mode names containing ROW are all misnomers. These mode names are read as indicating that the user intend to acquire row-level locks within the locked table. In addition, a ROW EXCLUSIVE lock is a sharable table lock. All the lock modes have identical semantics when LOCK TABLE is concerned, and are different only in the rules for checking conflicts.