SQL LOCK

What does the SQL LOCK statement do in PostgreSQL?

Explicitly acquires a specified lock mode on one or more tables within the current transaction.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LOCK: PostgreSQL: Yes MySQL: Similar but different LOCK TABLES syntax Oracle: Supports LOCK TABLE ... IN MODE SQL Server: No direct equivalent (use locking hints) SQLite: Automatic locking only

SQL LOCK Full Explanation

LOCK is a PostgreSQL command that lets you take an explicit table-level lock inside an open transaction. While the database normally acquires locks implicitly, explicit locking is useful when you need predictable ordering of locks, stronger protection than the default isolation level, or to prevent deadlocks. LOCK blocks until the requested lock can be granted, unless you add NOWAIT or SKIP LOCKED. The lock is held until the transaction ends (COMMIT or ROLLBACK). Choosing the correct lock mode is critical because stronger modes block more concurrent activity. Common modes include ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE. Because LOCK does not start a transaction for you, running it outside BEGIN...COMMIT raises an error. Use it sparingly because table-level locks can severely reduce concurrency.

SQL LOCK Syntax

BEGIN;
LOCK [ TABLE ] table_name [, ... ] [ IN lock_mode ] [ NOWAIT | SKIP LOCKED ];
-- your SQL work here
COMMIT;

SQL LOCK Parameters

table_name (regclass) - One or more tables to lock
lock_mode (enum) - One of the supported lock modes (default ACCESS EXCLUSIVE)
NOWAIT (keyword) - Raise error instead of waiting if lock unavailable
SKIP LOCKED (keyword) - Return immediately without locking unavailable tables

Example Queries Using SQL LOCK

-- Prevent other sessions from writing to orders while we reconcile
BEGIN;
LOCK TABLE orders IN SHARE MODE;
UPDATE orders SET reconciled = true WHERE processed_at < NOW() - INTERVAL '1 day';
COMMIT;

-- Acquire lock only if immediately available
BEGIN;
LOCK TABLE inventory IN EXCLUSIVE MODE NOWAIT;
-- do work
ROLLBACK;

-- Lock multiple tables to guarantee consistent read
BEGIN;
LOCK TABLE users, subscriptions IN ACCESS SHARE MODE;
SELECT u.id, s.plan FROM users u JOIN subscriptions s ON s.user_id = u.id;
COMMIT;

Expected Output Using SQL LOCK

If the lock is available, PostgreSQL grants it and the statement returns immediately
Other sessions attempting conflicting operations will wait or error until the transaction ends
With NOWAIT the command errors instantly if the lock cannot be granted

Use Cases with SQL LOCK

Serialize complex batch jobs that touch many tables
Avoid deadlocks by locking tables in a known order up front
Prevent writes while running large reporting queries
Guarantee that a migration runs without concurrent DML on the target tables

Common Mistakes with SQL LOCK

Running LOCK outside of a transaction block
Requesting an unnecessarily strong lock mode that blocks readers
Forgetting NOWAIT and hanging a session
Assuming LOCK releases automatically before COMMIT
Ignoring row-level locking needs and overusing table-level locks

First Introduced In

PostgreSQL 7.0

Frequently Asked Questions

Does LOCK start a transaction?

No. You must call LOCK inside an explicit BEGIN ... COMMIT or ROLLBACK block.

How long is a LOCK held?

The lock persists until the transaction that acquired it ends.

What happens if the lock is busy?

Without NOWAIT the session waits. With NOWAIT PostgreSQL raises an error immediately. With SKIP LOCKED it returns without locking the busy table.

Is LOCK the same as row locking?

No. LOCK is table-level. Use SELECT ... FOR UPDATE or FOR SHARE for row-level locks.