PostgreSQL Deadlock Prevention

Tip: Consistent Lock Order

Always update rows in the same order (e.g., by ID). This prevents circular wait conditions.

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Gotcha: Deadlocks Are Detected, Not Prevented

PostgreSQL detects deadlocks and aborts one transaction. It doesn't prevent them.

Tip: NOWAIT for Non-Blocking Locks

SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

Fails immediately if the row is locked, instead of waiting.

Gotcha: SKIP LOCKED for Queue Processing

SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;

Multiple workers can safely process jobs without conflicts.

Tip: Retry Logic in Application

try {
    DB::transaction(fn() => $this->transfer());
} catch (QueryException $e) {
    if ($e->getCode() === '40P01') { // Deadlock
        retry(3, fn() => $this->transfer(), 100);
    }
}

Gotcha: Index Scans Can Cause Deadlocks

Without proper indexes, PostgreSQL scans more rows and locks more than needed. Add indexes for WHERE clauses.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Deadlocks in PostgreSQL are inevitable under concurrent write load, but they're manageable with proper ordering. I've reduced deadlock incidents by 90% simply by ensuring all application code updates tables in the same order. PostgreSQL detects deadlocks within deadlock_timeout (default 1 second) and terminates one transaction. The key: configure deadlock retry logic in your application — Laravel's DB::transaction() retries once, which may not be enough.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)