How to Actually Diagnose a Postgres Deadlock in a Spring Boot App

#java #postgres #springboot #database

Prerequisites: Spring Boot 3.x, Postgres via Docker, basic JPA.

The pager goes off: PSQLException: deadlock detected. Your first instinct is to slap @Retryable on the service and go back to sleep. Sometimes that even works — which is exactly why it's dangerous. Retry hides the bug; the deadlock keeps happening, just quieter. In this post I'll show you how to reproduce a Postgres deadlock in five minutes, read the error properly, map it back to your code, and fix the root cause — a lock-ordering bug — instead of papering over it with retries.

Reproduce it in 5 minutes

Two @Transactional methods locking the same rows in opposite order. Docker Postgres, Spring Boot 3.x:

@Service
public class TransferService {

    @PersistenceContext
    private EntityManager em;

    // Thread A: locks account 1, then account 2
    @Transactional
    public void transferAtoB(Long a1, Long a2, BigDecimal amount) {
        Account from = em.createQuery(
            "SELECT a FROM Account a WHERE a.id = :id", Account.class)
            .setParameter("id", a1)
            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
            .getSingleResult();
        sleep(500); // widen the race window so it deadlocks reliably
        Account to = em.createQuery(
            "SELECT a FROM Account a WHERE a.id = :id", Account.class)
            .setParameter("id", a2)
            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
            .getSingleResult();
        from.debit(amount); to.credit(amount);
    }

    // Thread B: locks account 2, then account 1 — opposite order
    @Transactional
    public void transferBtoA(Long a1, Long a2, BigDecimal amount) {
        // ... same shape, locks a2 first, then a1
    }
}

Run both concurrently. You'll get the deadlock on demand.

Read the error — actually read it

Postgres tells you everything in the DETAIL line:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 9012.
Process 9012 waits for ShareLock on transaction 1234; blocked by process 1234.

Two processes, each waiting on the other. That's the whole story: a lock-ordering bug, not a database problem.

Find it while it's happening

Three things to run against Postgres during (or right after) an incident:

-- 1. Who is blocked right now?
SELECT pid, usename, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
  AND wait_event_type = 'Lock';

-- 2. The full blocker → blocked graph
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.relation = blocked_locks.relation
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

And to catch the next one with full context, enable this once:

ALTER SYSTEM SET log_lock_waits = ON;
SELECT pg_reload_conf();

Now every lock wait over deadlock_timeout lands in the Postgres log with the offending query attached.

Once you have the blocked query text, trace it back to your code. The query in blocked_query is the raw SQL Hibernate issued — table names, locked columns, and the WHERE clause it locked on. Enable SQL logging in dev (spring.jpa.show-sql=true or a DataSource proxy like p6spy) so you can see every query your app issues with the repository method that triggered it. Take the SQL from the blocker query, grep it against your query log, and you'll land on the exact repository methods or @Query annotations on both sides of the conflict — the two code paths whose lock acquisition order forms the cycle. In our example, the log would show one path locking account rows ordered 1 → 2 and another locking them 2 → 1, and that's your bug: the two methods.

The fix: order your locks

The rule: within a transaction, always acquire row locks in the same deterministic order — e.g., ascending ID:

@Query("SELECT a FROM Account a WHERE a.id IN :ids ORDER BY a.id")
@Lock(LockModeType.PESSIMISTIC_WRITE)
List<Account> lockAllOrdered(@Param("ids") List<Long> ids);

Both threads now lock account 1 before account 2. No cycle, no deadlock. One thread waits briefly; nobody dies.

Two more things people get wrong:

Isolation levels. Postgres defaults to READ COMMITTED, which is correct for most of this. Reaching for REPEATABLE READ or SERIALIZABLE increases deadlock/failure rates — don't escalate isolation to fix a locking bug.
Retry. Retry is for the unavoidable remainder (two genuinely concurrent writers), not the systematic case. And if you retry, be specific:

@Retryable(
  retryFor = { DeadlockLoserDataAccessException.class,
               CannotAcquireLockException.class },
  maxAttempts = 3,
  backoff = @Backoff(delay = 200, multiplier = 2.0)
)

Retrying on generic DataAccessException will retry things that will never succeed. Be precise.

TL;DR

Deadlocks are a lock-ordering bug in your code, not a Postgres tuning problem. Reproduce → read the DETAIL → map it to the two code paths → lock in a consistent order → retry only the narrow exception types.

I'm turning this into a full field guide — 10 production incidents like this (N+1s, pool exhaustion, OOMs, Kafka lag…) each with diagnosis steps, fixes, and prevention checklists, plus a complete Spring Boot → AWS deployment walkthrough. Preorder is $49 (goes to $79 at launch): https://cjettoostudent.gumroad.com/l/spring-boot-production-rescue-kit

DEV Community