PostgreSQL Locks — A Detailed Guide

When working with PostgreSQL, understanding locks is essential for reasoning about concurrency and troubleshooting blocking queries.

Postgres uses two main categories of locks: row-level locks and table-level locks.
Both are always in play, but they serve different purposes.

---

Row-level locks

Row-level locks protect individual rows. They prevent two transactions from modifying the same row at the same time.

• UPDATE / DELETE
  When you update or delete rows, Postgres takes an exclusive row lock on each affected row.
  Example:

    UPDATE accounts SET balance = balance - 100 WHERE id = 42;
    DELETE FROM accounts WHERE id = 42;
  

  Until the transaction commits or rolls back, no other transaction can update or delete the same row.

• SELECT ... FOR UPDATE
  Explicitly requests an exclusive row lock, even without modifying data.
  Useful when you want to read now, modify later safely.

    SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
  

• SELECT ... FOR SHARE
  Takes a shared row lock. Multiple sessions can hold it together, but writers (who need exclusive locks) must wait.

    SELECT * FROM accounts WHERE id = 42 FOR SHARE;
  

• Plain SELECT
  Takes no row locks.
  PostgreSQL uses MVCC (Multi-Version Concurrency Control) to give you a snapshot of the data at query start.
  This is why readers don’t block writers and writers don’t block readers.

---

Table-level locks

Every SQL command also acquires a table lock to protect the table’s existence and schema.
These are automatic — you don’t explicitly request them.

Common table lock modes

• ACCESS SHARE
  Acquired by plain SELECT.

• Blocks schema changes that require an ACCESS EXCLUSIVE lock (DROP, TRUNCATE, heavy ALTERs), but does not block DML (INSERT, UPDATE, DELETE) or maintenance commands like VACUUM/ANALYZE.

    SELECT * FROM accounts;
  

• ROW EXCLUSIVE
  Acquired by INSERT, UPDATE, DELETE.
  Blocks some schema changes (e.g., VACUUM FULL, certain ALTER TABLE), but allows concurrent reads and writes.

    INSERT INTO accounts(id, balance) VALUES (43, 1000);
    UPDATE accounts SET balance = 500 WHERE id = 42;
    DELETE FROM accounts WHERE id = 42;
  

• SHARE UPDATE EXCLUSIVE
  Acquired by maintenance commands like VACUUM and ANALYZE, and by some ALTER TABLE.
  Allows normal queries (SELECT, INSERT, UPDATE, DELETE) to continue, but blocks heavy structural changes (e.g., VACUUM FULL, CLUSTER).

    VACUUM accounts;
    ANALYZE accounts;
  

• ACCESS EXCLUSIVE
  The strongest table lock. Blocks all concurrent queries: reads (SELECT), writes (INSERT, UPDATE, DELETE), and other schema changes (ALTER TABLE, CREATE INDEX, etc.).
  Acquired by commands that restructure or remove the table.

    DROP TABLE accounts;
    TRUNCATE accounts;
    VACUUM FULL accounts;
  

---

Putting it together: quick cheat sheet

---

Why this matters

• Normal reads don’t block each other because of MVCC snapshots.

• Every query still acquires a table lock, even SELECT. These protect against concurrent schema changes.

• Schema changes can feel like outages. On a large table, an ALTER TABLE that takes a strong lock (like ACCESS EXCLUSIVE) can block readers and writers until it finishes.

---

Summary

PostgreSQL locks operate on two layers:

• Row locks — control concurrent writes on the same rows.

• Table locks — control concurrent access to the table’s structure.

Knowing which lock your query takes helps you predict blocking and design safe concurrent access patterns.