# Understanding lock contention in PostgreSQL

> **Quick answer:** Lock contention is queries waiting on locks other
> transactions hold.  Find the culprit with `pg_blocking_pids(pid)`, watch for an
> `ACCESS EXCLUSIVE` request (from `ALTER`, `DROP`, `TRUNCATE`, `VACUUM FULL`)
> that queues and blocks *everything* behind it, and keep transactions short so
> locks release fast.  Real deadlocks PostgreSQL breaks on its own after
> `deadlock_timeout`.

## Find the blocker fast

When a query hangs, you do not need to parse `pg_locks` by hand. `pg_blocking_pids()`
(since PostgreSQL 9.6) gives you the blockers directly:

```sql
SELECT
    blocked.pid            AS blocked_pid,
    blocked.query          AS blocked_query,
    blocking.pid           AS blocking_pid,
    blocking.query         AS blocking_query,
    now() - blocking.xact_start AS blocking_txn_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;
```

That maps each waiting query to the transaction holding it up.  The raw
`pg_locks` view shows the underlying granted (`granted = true`) and waiting
(`granted = false`) locks if you need the detail.

## Lock modes and why they conflict

You do not memorize eight modes, you memorize the one that freezes the table.
PostgreSQL has eight table-level lock modes; the ones that matter most:

| Lock mode | Taken by | Conflicts with |
|---|---|---|
| `ACCESS SHARE` | `SELECT` | Only `ACCESS EXCLUSIVE` |
| `ROW EXCLUSIVE` | `INSERT` / `UPDATE` / `DELETE` | `SHARE` and stronger |
| `SHARE UPDATE EXCLUSIVE` | `VACUUM` (not full), `ANALYZE`, `CREATE INDEX CONCURRENTLY` | Itself and stronger |
| `SHARE` | `CREATE INDEX` (non-concurrent) | Writes (`ROW EXCLUSIVE`) |
| `ACCESS EXCLUSIVE` | `ALTER TABLE`, `DROP`, `TRUNCATE`, `VACUUM FULL` | **Everything**, including `SELECT` |

`ACCESS EXCLUSIVE` is the one that bites: it conflicts with every other mode.  And
because PostgreSQL queues lock requests, a **pending** `ACCESS EXCLUSIVE` (an
`ALTER` waiting behind one slow query) blocks the new requests lining up behind
it, so ordinary `SELECT`s start waiting too.  One migration plus one long query
can freeze a table. (See
[which ALTER TABLE statements lock](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/).)

## Deadlocks vs plain waiting

- **Plain contention:** A waits for B; B finishes; A proceeds.  Slow, not fatal.
- **Deadlock:** A holds lock 1 and wants lock 2; B holds lock 2 and wants lock 1.
  Neither can proceed.  PostgreSQL notices, after a wait passes `deadlock_timeout`
  (1s by default) it runs deadlock detection, finds the cycle, and **aborts one
  transaction** with a `deadlock detected` error so the other continues.

Deadlocks usually come from two code paths locking the same rows in **opposite
order**. Lock rows in a consistent order and they mostly disappear.

## How do I reduce contention?

- **Keep transactions short.** The less time a lock is held, the less anything
  waits.  Never hold a transaction open across a network call or user think-time.  That is the [idle in transaction](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)
  trap.
- **Lock in a consistent order** across your code to avoid deadlocks.
- **Set `lock_timeout`** so a blocked statement fails fast instead of queuing and
  stalling others.

## Why does aI-generated code create lock contention?

Your AI coding agent (Claude Code, Cursor) writes each transaction to be correct
on its own.  It cannot see that two endpoints update the same two tables in
opposite orders (a deadlock waiting to happen), or that a migration will take
`ACCESS EXCLUSIVE` while a report holds the table.  Lock interactions are a
property of concurrent execution across your whole system, not of any one
function the model reads.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the live lock picture, who is blocking whom via `pg_blocking_pids()`, which lock
modes are held, how old the blocking transaction is, so it can trace a stuck
query to its blocker and explain the contention.  It surfaces and explains; it
does not kill sessions or take locks itself. [Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**How do I find what is blocking a query?**
`pg_blocking_pids(pid)` (since PostgreSQL 9.6) returns the blocking process IDs;
join it to `pg_stat_activity` for the blocking query. `pg_locks` shows the raw
granted/waiting locks.

**Why does one ALTER TABLE block even simple SELECTs?**
`ALTER TABLE` needs `ACCESS EXCLUSIVE`, which conflicts with every mode including
the `ACCESS SHARE` a `SELECT` takes.  A pending request queues ahead of new ones,
so `SELECT`s behind it wait too.

**How does PostgreSQL handle deadlocks?**
It detects them: after a wait exceeds `deadlock_timeout` (1s default) it looks for
a cycle and aborts one transaction with a `deadlock detected` error.

**How do I reduce lock contention?**
Short transactions, consistent lock ordering, a `lock_timeout`, and no
idle-in-transaction sessions.

## Related

- [How to fix idle in transaction connections in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)
- [Which ALTER TABLE statements lock a PostgreSQL table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)
- [How to add a PostgreSQL index without downtime](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-index-without-downtime/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)