Lorixen.
EN

Database deadlocks: why they happen and how to stop them

Short answer: A deadlock happens when two transactions each hold a lock the other needs, so neither can continue. The database detects it, cancels one of them with a "deadlock detected" error and lets the other finish. You fix deadlocks mainly by making transactions take locks in the same order, keeping transactions short, adding indexes so updates lock fewer rows, and retrying the cancelled transaction safely in your app.

What a deadlock actually is

Picture two staff members updating stock at the same time. Transaction A updates product 1, then product 2. Transaction B updates product 2, then product 1. A locks product 1, B locks product 2, and then each waits for the row the other holds. Neither can move.

PostgreSQL's documentation on explicit locking describes exactly this: two or more transactions each holding locks the other wants. It notes that PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, so the others can complete. Other relational databases behave similarly.

So a deadlock isn't a hang that lasts forever. It's an error your app receives, typically under load, often intermittently, which is why it can be hard to reproduce.

How it shows up in a business app

Common symptoms:

  • Occasional failures on busy actions, like checkout, stock adjustments or batch imports, with "deadlock detected" in the logs.
  • Errors that only appear when two people or two background jobs work on related data at once.
  • Failures clustering around peak times or scheduled jobs.

Typical causes in small business apps:

  • Updating several rows in different orders, such as a basket with items processed in the order the customer added them.
  • A background job and a user action touching the same records in opposite orders.
  • Missing indexes, so an update scans and locks more rows than intended.
  • Long transactions that hold locks while calling an external API or waiting for user input.

Fix 1: take locks in a consistent order

The PostgreSQL documentation's main advice is that the best defence is to make sure all applications using a database acquire locks on multiple objects in a consistent order. In the stock example, if both transactions updated product 1 before product 2, one would simply wait for the other, and no deadlock would occur.

In practice:

  • Sort IDs before updating several rows, for example by product ID, in every code path.
  • When a transaction touches several tables, touch them in a fixed order, such as orders, then order lines, then stock.
  • Apply the same order in background jobs and admin tools, not just the main app.

The documentation also recommends that the first lock a transaction takes on an object should be the most restrictive mode it will need, which avoids a transaction upgrading its lock part-way through and colliding with another.

Fix 2: keep transactions short

The longer a transaction holds locks, the more chance another one collides with it. Keep transactions to the database work only:

  • Don't call payment providers, send emails or fetch remote data inside a transaction.
  • Don't keep a transaction open while waiting for a person.
  • Do the reading and preparation first, then open the transaction, write and commit.

This also helps general performance, since other requests wait less.

Fix 3: index what you update by

If an update filters on a column without an index, the database may need to examine and briefly lock many rows. Two transactions touching different rows can then still collide. Adding the right index means each update locks only the rows it really changes. Check slow or deadlocking statements with your database's query plan tools.

Fix 4: retry the victim safely

Even with good design, occasional deadlocks can happen on busy systems. Because the database cancels the whole transaction, the safe response is to retry the whole transaction from the start, not just the failed statement:

  1. Catch the deadlock error code your database uses.
  2. Roll back.
  3. Wait a short, slightly random time.
  4. Run the full transaction again, a limited number of times.
  5. If it still fails, report a clear error and log it.

This only works if the transaction is safe to repeat. Make sure side effects like emails or payment calls happen after commit, not inside the retried block.

Finding the cause

PostgreSQL logs details of the transactions involved when it detects a deadlock, including the statements, which is usually enough to see the conflicting order. Its lock settings documentation explains that deadlock_timeout, one second by default, is how long a transaction waits on a lock before the server checks for a deadlock. That setting also controls when lock waits are logged if you switch on lock-wait logging, which helps you see contention before it becomes a deadlock.

When you read a deadlock report, look for:

  • The two statements involved and which rows they touched.
  • Whether the same code path appears on both sides, with different data orders.
  • Whether one side is a background job.

A worked example

Consider a hypothetical Ottawa outdoor-gear shop whose order process reserved stock for each basket item in the order items were added, while a nightly supplier import updated stock sorted by supplier code. During a busy sale, a handful of checkouts failed with deadlock errors. The logs showed checkout and the import locking the same products in opposite orders. The team sorted stock updates by product ID in both places, moved the confirmation email after commit, and added a three-attempt retry around the reservation transaction. The errors stopped appearing in the logs.

FAQ

Are deadlocks a sign of a database bug?

Almost never. They're a result of how the app's transactions interact. The database is protecting your data by cancelling one.

Should I just raise timeouts?

No. Deadlock detection resolves the conflict; longer timeouts mostly delay the error and hold locks longer.

Can an ORM cause deadlocks?

Yes, if it issues updates in an unpredictable order or wraps more work than you expect in one transaction. Check the SQL it generates.

Is retrying always safe?

Only if the whole transaction can run twice without side effects. Keep external calls outside it.

If your app shows intermittent deadlock errors under load, send us the logs and we'll help find the cause.

Sources

WhatsAppBook a call