Locks a table.
LOCK TABLE name [, ...] IN lockmode MODE [ NOWAIT ]
lockmode is one of the following items:
ROW SHARE | ROW EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE
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
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-O 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
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.
|name||The name of the table to be locked. The name can be schema-qualified.
|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,
|NOWAIT||Specifies that the
All forms of
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.