LOCK

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 for PostgreSQL(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

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.