Deadlocks (last 5m), Supabase

​

At a glance

The number of deadlocks Postgres has detected and resolved in the last five minutes. A deadlock is two or more transactions each holding a lock the other needs, a circular wait that can never resolve itself, so Postgres breaks it by killing one transaction with a deadlock detected error. On a healthy database this number is zero, effectively always. Any non-zero reading means application transactions are colliding over the same rows in incompatible lock orders, and at least one user’s write just failed. This is why the alert fires at the first deadlock, not at some tolerance band: deadlocks are a code-shaped problem, not a capacity one, and even one is a bug worth chasing.

​

Calculation

deadlocks_last_5m = deadlocks(now) - deadlocks(now - 5 minutes)

• It counts resolved deadlocks, not lock waits. Ordinary lock contention (transactions queuing politely for a lock and getting it) does not increment the counter. Only a true circular wait that Postgres had to break does. So a non-zero reading is unambiguous: a transaction was killed.
• The window is deliberately short. Deadlocks arrive in bursts (a hot-path code path under concurrency), so a 5-minute window surfaces the spike clearly. A longer average would dilute three deadlocks in one minute into a near-zero rate and hide the incident.
• One deadlock equals at least one failed user action. The aborted transaction’s work is rolled back. Whether the user sees an error depends on whether the application retries; many do not, which is why a deadlock often shows up first as a customer complaint.

​

Worked example

ERROR: deadlock detected
DETAIL: Process 18432 waits for ShareLock on transaction 99214; blocked by process 18519.
        Process 18519 waits for ShareLock on transaction 99220; blocked by process 18432.
        Process 18432: UPDATE inventory SET qty = qty - 1 WHERE sku = 'A';
                       UPDATE inventory SET qty = qty - 1 WHERE sku = 'B';
        Process 18519: UPDATE inventory SET qty = qty - 1 WHERE sku = 'B';
                       UPDATE inventory SET qty = qty - 1 WHERE sku = 'A';

The fix is in application code, not the database:
  - Enforce a consistent lock order. Sort the SKUs before updating:
      UPDATE inventory ... WHERE sku = ANY(ARRAY['A','B'] ORDER BY sku)
    so every transaction acquires A before B, breaking the cycle.
  - Add a bounded retry on SQLSTATE 40P01 so a victim transaction
    automatically retries once rather than surfacing an error to the user.
  - Result: deadlock count returns to 0; the lunchtime spike no longer
    produces failed orders.

1. A deadlock is a code defect, not a capacity problem. Sizing up compute will not fix it; in fact more compute means more concurrency, which can make deadlocks more frequent. The cure is consistent lock ordering and retries in the application.
2. They appear under concurrency, so they hide in testing. A deadlock needs two transactions to interleave at just the wrong moment. That is rare at low load and common at peak, which is exactly when it hurts most. Treat the first production deadlock as a real bug even if you cannot reproduce it locally.
3. The count tells you it happened; the log tells you why. The card is the alarm. The Postgres deadlock log entry (which names both queries and both lock sets) is the diagnosis. Always pull the log for the window before changing anything.

​

Sibling cards

​

Reconciling against the source

SQL Editor / psql is the ground truth for the counter:

SELECT datname, deadlocks FROM pg_stat_database WHERE datname = current_database();

Supabase Studio → Logs → Postgres holds the detailed deadlock entries (search for deadlock detected), which name both colliding queries and the locks each held. pg_locks joined to pg_stat_activity shows current lock waits, useful for catching contention before it becomes a deadlock.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre