LOCK TABLE

Locks a table, giving the caller's session exclusive access to certain operations.

Locks a table, giving the caller's session exclusive access to certain operations. Tables are automatically unlocked after the current transaction ends—that is, after COMMIT or ROLLBACK. LOCK TABLE can be useful for preventing deadlocks.

To view existing locks, see LOCKS.

Note

READ COMMITTED isolation (default) and SERIALIZABLE isolation automatically handle locks for you, and the vast majority of users can rely on them exclusively; LOCK TABLE is only for advanced users who need granular control over locks for more complex workloads.

To implement pessimistic concurrency without manually locking tables, you can use SELECT...FOR UPDATE to acquire an EXCLUSIVE (X) lock on the table.

Syntax

LOCK [ TABLE ] [[database.]schema.] table [,...]
    IN { lock_type } MODE
    [ NOWAIT ]

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

table

The table to lock.

lock-type

The type of lock, one of the following:

SHARE
INSERT
INSERT VALIDATE
SHARE INSERT
EXCLUSIVE
NOT DELETE
USAGE
OWNER

Note

LOCK TABLE does not currently support D (drop partition) locks.

NOWAIT

If specified, LOCK TABLE returns and reports an error immediately if it cannot acquire the lock. Otherwise, LOCK TABLE waits for incompatible locks to be released by their respective sessions, returning an error if the lock is not released after a certain amount of time, as defined by LockTimeout.

Privileges

Required privileges depend on the type of lock requested:

Lock	Privileges
SHARED (S)	SELECT
INSERT (I)	INSERT
SHARE INSERT	SELECT, INSERT
INSERT VALIDATE (IV)	SELECT, INSERT
EXCLUSIVE (X)	UPDATE, DELETE
NOT DELETE (T)	SELECT
USAGE	All privileges
Owner	All privileges

Examples

See Lock examples.