Designing Safe Concurrent Writes with Sequelize Transactions and Isolation Levels

Requirements

The application must allow multiple request handlers to modify related rows (e.g., transfer funds between accounts) without losing updates, creating phantom rows, or leaving the database in an inconsistent state. The solution should:

1. Encapsulate each logical unit of work in a transaction.
2. Choose an isolation level that prevents the specific anomalies the business logic cannot tolerate.
3. Keep transaction objects confined to server‑side code.
4. Provide observable safeguards (logging, deadlock detection, retry) so operators can detect and recover from failures.

Smallest Suitable Design

The minimal design that satisfies the requirements is a wrapper around Sequelize’s explicit transaction API:

const runInTransaction = async (callback, isolation = undefined) => {
  const opts = isolation ? { isolationLevel: isolation } : {};
  return await sequelize.transaction(opts, async (t) => {
    // pass the transaction t to all model calls inside callback
    return await callback(t);
  });
};

By default Sequelize uses READ COMMITTED, which prevents dirty reads and is sufficient for most write‑only workloads. If the business rule requires that no new rows matching a search condition appear during the transaction (to avoid phantom reads), bump the isolation to REPEATABLE READ or SERIALIZABLE.

Trust / Data Boundaries

The transaction object t holds the connection and the ability to commit or rollback. Exposing t to the client (e.g., via a request parameter or a shared mutable object) would allow a malicious caller to:

• Commit prematurely, leaving other participants in an inconsistent state.
• Hold the connection open indefinitely, exhausting the pool.
• Force a rollback after observing intermediate state, enabling replay attacks.

Therefore, the transaction must be created, used, and resolved entirely within server‑side functions that are not serialized to the client. All model calls inside the callback must receive t as an argument; the callback itself should not return t.

Operational Checks

To run this design safely in production, add the following observability and resilience measures:

1. Duration logging – wrap the transaction promise with a timer and log if it exceeds a threshold (e.g., 2 seconds). This surfaces long‑running transactions that may indicate lock contention.
2. Deadlock detection – Sequelize throws a SequelizeTimeoutError or a DatabaseError with a deadlock‑specific code (depending on the dialect). Catch these errors and decide whether to retry.
3. Automatic retry – implement a simple retry loop that re‑executes the whole transaction on deadlock, up to a configurable limit (e.g., three attempts). Use exponential back‑off to reduce contention.
4. Connection‑pool health – monitor the pool’s idle/active counts; a steady rise in active connections without corresponding completions suggests a missing await or an unhandled rejection that left a transaction open.

Failure Modes

Even with the minimal design, certain conditions can cause problems:

• Unawaited transaction – if the promise returned by sequelize.transaction is not awaited or not returned from the handler, the connection stays checked out with an open transaction. Over time the pool exhausts and the application stalls.
• Excessive locking under SERIALIZABLE – this isolation level acquires range locks that can block concurrent writers, increasing the chance of deadlocks or timeouts. Throughput may drop dramatically under heavy write loads.
• Phantom reads under READ COMMITTED – if the application relies on a predicate like “select * where balance > 0 for update” and then inserts a new row matching that predicate, another concurrent transaction could see the new row after the first commits, leading to lost updates.
• Mis‑scoped transaction object – passing t to a helper that stores it in a closure or global variable can cause the transaction to be used outside its intended scope, breaking isolation guarantees.

When the Design Should Change

Re‑evaluate the smallest design if any of the following become true:

1. The business logic requires repeatable reads (e.g., a report that must see a consistent snapshot of rows read earlier in the same transaction). Move to REPEATABLE READ or SERIALIZABLE and adjust retry logic accordingly.
2. Profiling shows frequent deadlocks at the current isolation level. Consider reducing lock scope (e.g., updating rows in a deterministic order) or switching to application‑level optimistic locking with a version column.
3. The connection pool regularly hits its maximum because some request handlers forget to await the transaction wrapper. Introduce a lint rule or a TypeScript wrapper that enforces the return of the transaction promise.
4. Operational metrics reveal that transaction durations regularly exceed the logging threshold, indicating that the logical unit of work is too large. Split the work into smaller transactions or use sagas with compensating actions.

Practical Verification

To confirm that the chosen isolation level behaves as expected, you can run a Node.js script that creates two concurrent transactions updating the same account:

async function testIsolation(level) {
  const Account = sequelize.define('Account', { id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true }, balance: Sequelize.INTEGER });
  await sequelize.sync({ force: true });
  await Account.create({ id: 1, balance: 100 });

  const tx1 = sequelize.transaction({ isolationLevel: level }, async (t) => {
    const acc = await Account.findByPk(1, { transaction: t, lock: t.LOCK.UPDATE });
    // simulate work
    await new Promise(r => setTimeout(r, 100));
    await acc.update({ balance: acc.balance - 30 }, { transaction: t });
  });

  const tx2 = sequelize.transaction({ isolationLevel: level }, async (t) => {
    const acc = await Account.findByPk(1, { transaction: t, lock: t.LOCK.UPDATE });
    await acc.update({ balance: acc.balance + 20 }, { transaction: t });
  });

  try {
    await Promise.all([tx1, tx2]);
    const final = await Account.findByPk(1);
    console.log('Both committed, final balance:', final.balance);
  } catch (err) {
    if (err.name === 'SequelizeTimeoutError' || (err.parent && err.parent.code && err.parent.code.includes('deadlock'))) {
      console.log('One transaction aborted due to deadlock/timeout as expected');
    } else {
      throw err;
    }
  }
}

// Example run: testIsolation(Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE);

Observe the output: either both transactions commit (if the database engine permits concurrent updates under the chosen level) or one is aborted with a deadlock/timeout error, confirming that the isolation level is active. Check the logs for transaction duration and any retry attempts.

Limitations

The wrapper shown does not automatically handle nested transactions; Sequelize supports them via saving points, but exposing that complexity is unnecessary for most simple write‑only use cases. If your application needs true nested transactions, consider using Sequelize’s transaction with the autocommit: false option and manually managing save points, or adopt an out‑of‑process saga pattern.

Finally, remember that isolation levels are a database feature; the behavior described assumes the underlying engine (PostgreSQL, MySQL, SQLite, MSSQL) implements the levels as defined in the SQL standard. Consult your dialect’s documentation for any caveats (e.g., MySQL’s REPEATABLE READ does not prevent phantom reads in certain scenarios).