Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 37 ยท Transactions

Deadlocks and Retries

Why deadlocks and serialization failures happen, how to avoid them with consistent lock order, and a safe retry helper with backoff for TypeORM and Sequelize.

When two transactions each hold a lock the other one needs, neither can continue. Databases detect this situation, pick a victim, roll the victim back and return an error. That error is not a bug in your SQL; it is the database telling you "try again". Under load, even correct code will see them now and then, so production code needs a plan.

The same goes for serialization failures at REPEATABLE READ or SERIALIZABLE, and for lock timeouts. In this lesson you will recognize those errors, reduce how often they happen and wrap transactions in a retry helper. The helper is plain JavaScript and runnable here; the ORM snippets are read-only.

How a deadlock forms

sequenceDiagram
  participant A as Transaction A
  participant B as Transaction B
  A->>A: lock account 1
  B->>B: lock account 2
  A->>B: wants account 2 (waits)
  B->>A: wants account 1 (waits)
  Note over A,B: Neither can proceed, database aborts one

Opposite lock order is the classic cause: A goes 1 then 2, B goes 2 then 1.

Errors to recognize

Database Deadlock Serialization failure
PostgreSQL SQLSTATE 40P01 SQLSTATE 40001
MySQL / MariaDB error ER_LOCK_DEADLOCK (1213) same code family; also lock wait timeout 1205
SQL Server error 1205 error 3960 (snapshot)
SQLite SQLITE_BUSY (database is locked) n/a

TypeORM wraps the driver error in QueryFailedError; the original code is on err.driverError.code (or err.code). Sequelize wraps it in DatabaseError subclasses with the original error under err.original (err.parent). In both cases read the database code, not the message text.

Reducing deadlocks

  1. Lock in a consistent order. Always touch accounts in ascending id, tables in the same sequence.
  2. Keep transactions short. No network calls, no waiting for users.
  3. Touch fewer rows. Add indexes so statements lock only what they need; a missing index can lock a wide range.
  4. Do the risky statement first if one write is likely to fail.
  5. Retry whatever remains.

Here is point 1 in code. Sorting the ids before locking removes the opposite-order cycle entirely.

Node.js Consistent lock order

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

A real "locked" error

SQLite has one writer at a time, so the simplest way to see a retryable error is two connections that both want to write.

Node.js SQLITE_BUSY with two writers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

A retry helper

The helper reruns the whole transaction function when the error is retryable, waits a little longer each time and adds random jitter so competing workers do not retry in lock step.

Node.js withRetry with exponential backoff

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Using it with an ORM means wrapping the entire transaction, not a single query:

TypeScript
const result = await withRetry(() =>
  dataSource.transaction('SERIALIZABLE', async (manager) => {
    // read, decide, write
  }),
);

Rules for safe retries:

  • Retry the whole transaction function, never one statement inside it.
  • The function must be safe to run again. It must not send emails, charge cards or call APIs inside the transaction (use after-commit hooks).
  • Cap the attempts. Endless retries turn one problem into an outage.
  • Never retry on constraint violations or validation errors; they will fail every time.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the classic cause of a deadlock between two transfers?

    A waits for what B holds while B waits for what A holds.
  2. 2. Where should you read the database error code in TypeORM?

    QueryFailedError wraps the driver error; match on its code, not on message text.
  3. 3. Which belongs inside a retried transaction function?

    The function may run more than once, so side effects must go after the commit.
  4. 4. Why add random jitter to the backoff?

    Synchronized retries collide again; randomness spreads them out.

Summary

  • Deadlocks and serialization failures are normal under load; the fix is to retry.
  • Lock rows in a consistent order, keep transactions short and index the columns you filter on.
  • Detect retryable errors by database code, wrap the whole transaction and cap attempts.
  • Put non-repeatable side effects after the commit.

Next: Test Databases and Fixtures