Atomic money moves with Knex.js transactions and no partial writes

Moving money between two accounts looks trivial until the first update succeeds and the second fails. You end up with a debit and no credit, or a credit with no debit, and the database no longer reflects reality. That is the classic partial-write problem for any multi-step mutation.

The practical takeaway is to treat related writes as one unit of work. Knex.js provides a built-in transaction API that groups multiple queries into an atomic unit. If any step throws, Knex rolls back the whole unit; if all steps succeed, it commits.

The problem with two-step updates

Without a transaction, each query runs in its own autocommit. A network blip, a constraint violation, or an unhandled exception after the first query leaves the database in an inconsistent state. The fix is not more try/catch around individual queries, it is scoping them to a single transaction with a dedicated transaction object.

A transaction in SQL means all statements either happen together or not at all. Knex exposes that via knex.transaction, which gives you a trx handle. All queries built from trx participate in the same database transaction, and Knex manages BEGIN, COMMIT and ROLLBACK for you.

Using the Knex transaction API

The API is callback or promise based. With async/await the pattern is:

await knex.transaction(async (trx) => {
  await trx('accounts')
    .where('id', fromId)
    .decrement('balance', amount);

  await trx('accounts')
    .where('id', toId)
    .increment('balance', amount);
});

Inside the callback, use trx, not knex. Knex will commit automatically if the callback resolves and roll back if it rejects. Do not start a new transaction inside an existing one unless you explicitly opt into savepoints, otherwise Knex will error.

A useful verification pattern is to run the same logic against a fresh in-memory SQLite database and inspect balances after success, then introduce an intentional throw after the first update to confirm neither change persists. That demonstrates rollback without relying on production data.

Trade-offs and limits to keep in mind

Transactions add coordination cost. The database holds row locks for the duration of the transaction, which can increase latency under high concurrency and raise the chance of deadlocks when multiple transactions touch the same rows in different order.

Keep transactions short and focused. Read what you need, write what you need, and return. Avoid network calls, external service requests, or heavy computation inside the transaction callback.

Driver support matters. Knex relies on the underlying client for proper transaction and savepoint handling. Older versions of pg or mysql2 may lack reliable savepoint support, which affects nested transaction behavior.

Deadlock retries are a practical necessity for write-heavy workloads. If your database reports a deadlock, a common approach is to retry the whole transaction a small number of times with a short backoff, rather than trying to patch a single statement.

Permissions: the database user needs SELECT and UPDATE on the involved tables and permission to start transactions, which is standard for most application users.

Actionable closing

Wrap any multi-step data mutation that must stay consistent in a Knex transaction. Use the trx object for every query in the unit, keep the callback small, and test rollback by forcing an error after a partial update. Monitor lock wait times and deadlock rates in your database’s performance views to catch transactions that are growing too long.