Right, so you’ve gotten your locks all lined up, and now everything has ground to a halt. Welcome to the deadlock, the database equivalent of a standoff in a Western movie, but with less dramatic music and more error logs. A deadlock happens when two or more transactions are each waiting for the other to release a lock, creating a perfect circle of pointless waiting. The database isn’t stupid; it won’t let this nonsense continue forever. One of you is going to get shot (figuratively, your transaction will be rolled back) so the other can live. Your job is to make sure it’s not your transaction that gets chosen and, more importantly, to write your code so these standoffs are rare and handled gracefully when they do occur.

How Your Database Breaks the Tie

When the database’s deadlock detector (a process that’s constantly drawing arrows between transactions on a whiteboard) finds this circular dependency, it doesn’t try to reason with anyone. It picks a victim and kills that transaction to break the cycle. This results in an error—in PostgreSQL, it’s ERROR: deadlock detected, and in MySQL, it’s ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.

How does it choose the victim? It’s not totally random, though it can feel that way. The database calculates the cost of rolling back each transaction. The transaction that has changed the least data (making its rollback the cheapest and least disruptive) is usually the one that gets the axe. This is why you might see a simple SELECT FOR UPDATE get rolled back in favor of a larger, more complex update—the SELECT had done nothing yet, so rolling it back is trivial.

The Only Correct Way to Handle a Deadlock Error

You must catch this specific error in your application code and retry the transaction from the beginning. This is non-negotiable. The entire unit of work needs to be restarted because the rollback has, well, rolled everything back. Picking up where you left off is impossible.

Here’s the pattern. This is pseudo-code, but the structure is what matters:

max_retries = 3
retries = 0

while retries < max_retries:
    try:
        with database.transaction(): # start a new transaction
            # ... all your SQL statements happen in here ...
            # e.g., SELECT FOR UPDATE, then UPDATE, etc.
        break # If we get here, success! Break out of the loop.
    except DeadlockDetectedError: # Catch the VERY specific deadlock error
        retries += 1
        logging.warning(f"Deadlock encountered. Retry attempt {retries} of {max_retries}.")
        # Optional: add a brief, random delay here to desynchronize competing threads
        time.sleep(random.uniform(0.1, 0.3))
        continue # Go back to the start of the loop and try again

if retries == max_retries:
    raise Exception("Failed to complete transaction after deadlock retries.")

Notice we retry the entire transactional block. You’re not just retrying the one statement that failed; you’re re-running the business logic. This is why your transactions need to be idempotent—meaning running them multiple times produces the same result as running them once. If your transaction inside the retry loop does something like INSERT ..., you might get a duplicate key error on the retry. You have to design for this.

Common Pitfalls That Invite Deadlocks

The classic deadlock scenario is updating resources in a different order. Imagine two transactions:

  • Transaction A: Locks row 1, then tries to lock row 2.
  • Transaction B: Locks row 2, then tries to lock row 1.

Boom. Deadlock. The simplest prevention pattern is therefore idiotically simple yet often ignored: always access and lock resources in a consistent, predictable order.

If you have a process that needs to update rows for two users, always lock them in order of their primary key. ORDER BY user_id is your best friend.

-- DO THIS: Consistent order prevents deadlocks.
SELECT * FROM accounts WHERE user_id IN (123, 456) ORDER BY user_id FOR UPDATE;

-- NOT THIS: The order is arbitrary and can differ between transactions.
SELECT * FROM accounts WHERE user_id = 123 FOR UPDATE;
SELECT * FROM accounts WHERE user_id = 456 FOR UPDATE;

Other major culprits are lack of indexes (causing full table scans and locking way more rows than you intended) and long-running transactions (which hold locks open for longer, increasing the window for a conflict). Keep your transactions as short and sweet as possible. Do your expensive calculations before you open the transaction!

The N+1 Problem: A Deadlock Factory

This one is so common it deserves its own rant. You fetch a list of parent objects (SELECT * FROM invoices), and then in a loop, you issue a new SQL statement for each child (SELECT ... WHERE invoice_id = ? FOR UPDATE). This is a recipe for disaster because the order of locking is entirely determined by the order of the initial result set, which might be different across transactions. The solution is to use a single, efficient query to lock everything you need up front, using IN or a join.

-- Instead of a loop in your app, do this in one shot inside your transaction:
SELECT * FROM invoice_items WHERE invoice_id IN (SELECT id FROM invoices WHERE status = 'PENDING') FOR UPDATE;

This locks all the relevant rows immediately, in a single statement, avoiding the chaotic locking order that the N+1 pattern creates. It’s also, you know, about a hundred times faster.