LLM Best Practices

Deadlock

Last updated 2 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

A deadlock is a circular lock wait: transaction A holds a lock B needs while B holds a lock A needs, so neither can proceed. PostgreSQL detects the cycle once a lock wait exceeds deadlock_timeout (default 1 s), aborts one transaction with error 40P01, and lets the others continue. The application must catch 40P01 and retry the whole transaction. Deadlocks surface under concurrent load, usually when two code paths update the same rows in different orders.

Rules

  • Prevent them structurally: acquire locks in a consistent order everywhere, such as updating rows in ascending primary-key order. This is the PostgreSQL docs’ primary advice.
  • Which transaction is aborted “is difficult to predict and should not be relied upon”. Leave deadlock_timeout at its default unless profiling says otherwise: the cycle check is expensive, so a lower value adds overhead to every lock wait and a higher one delays reporting real deadlocks.
  • Log every 40P01. Recurring ones are a lock-ordering bug, not noise to retry forever.
  • Keep transactions short, use SELECT ... FOR UPDATE SKIP LOCKED for queue workers, and consider optimistic-locking for low-conflict read-modify-write.
  • Foreign key checks lock the referenced row and can join a cycle (foreign-key). Serializable and Repeatable Read raise serialization failures (40001), not deadlocks, but need the same retry logic (transaction-isolation).
-- Transaction A                          -- Transaction B
UPDATE accounts SET bal = bal - 50 WHERE id = 1;   UPDATE accounts SET bal = bal - 50 WHERE id = 2;
UPDATE accounts SET bal = bal + 50 WHERE id = 2;   UPDATE accounts SET bal = bal + 50 WHERE id = 1;
-- Each waits on the other; Postgres aborts one. Fix: always update the lower id first.

Backlinks