Exclusive lock in PostgreSQL

Date: 23 November 2025

If you work with PostgreSQL in any high-traffic system, understanding how locks behave is essential to preventing performance issues and deadlocks.

When a transaction holds an exclusive lock, PostgreSQL guarantees that, no other transaction can modify the same table, meaning INSERT, UPDATE, DELETE, TRUNCATE must wait.

PostgreSQL uses MVCC (Multi-Version Concurrency Control), meaning:

Readers don’t block writers
Writers don’t block readers
Readers read a snapshot of the table

So even if a table is exclusively locked for writing, SELECT queries read old versions safely

If your transaction needs a consistent state without any changes, e.g.:

Rebuilding indexes
Table maintenance
Schema changes
Batch operations that must not be interrupted

But deadlocks happen when lock ordering is inconsistent. Deadlocks don’t occur just because two transactions use the same tables. They happen when Transaction A and Transaction B lock the same objects but in different orders.

Instead of locking entire tables, you can use Row-Level Locks

SELECT ... FOR UPDATE;

Row-level locking reduces contention, increases concurrency, and prevents your application from blocking other transactions unnecessarily.

For example:

If you are building a bus ticket booking platform.

Two people attempt to book Seat #14 at the same time.

Step 1: Check seat availability with a lock

BEGIN;
SELECT * FROM seats 
WHERE bus_id = 55 AND seat_no = 14 
FOR UPDATE;

This does two important things, locks only that seat row and prevents any other transaction from locking or modifying the same seat

Now Transaction A has the lock. Transaction B must wait.

Step 2: Confirm booking

UPDATE seats SET status = 'booked', booked_by = 101 
WHERE bus_id = 55 AND seat_no = 14;

Once the row updates, the booking is secured.

When Transactions B runs

SELECT ... FOR UPDATE;

It hangs until transaction A commits, then B can proceed, or Transaction A rolls back

By the time Transaction B gets the row lock, it sees that the seat is already booked and stops the booking process.

This is how PostgreSQL ensures no two users can book the same seat

Things keep in mind:

Try to avoid calling external APIs inside transactions, long transactions cause long wait time.
Always lock rows in the same order. Inconsistent order can cause deadlocks
Index the columns you use in FOR UPDATE queries (e.g. bus_id, seat_no).

Postgres has to scan the entire table to find matching rows. During this scan, it may lock more rows or acquire heaver locks. This increases wait times and deadlock chances. So Postgres uses the index to jump directly to the needed rows. Only the target rows get locked.