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
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
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
|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
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.