Atomic Multi-Table Writes in Node.js with Knex Transactions and Row Locking

The problem is partial writes and lost updates under concurrency

A Node.js service that updates an account balance, inserts a ledger row and writes an audit entry must either commit all three or none. Without explicit transaction control and row locking, concurrent requests can read the same balance, compute new values independently, and overwrite each other. Knex.js provides a query builder and a transaction API that centralizes atomicity in application code, but the guarantees depend on correct callback usage, dialect-specific locking, and keeping untrusted input out of SQL strings.

Requirements for atomic service writes

Atomic commits across related tables are required so business invariants hold. Repeatable reads for the duration of a business operation prevent phantom reads that would change pricing or eligibility mid-workflow. Protection against lost updates requires pessimistic locking on the rows that are read then written. Auditability requires that changes are traceable without leaking untrusted input into SQL via string interpolation.

Smallest suitable design

The minimal design keeps business logic in the service layer and delegates persistence to Knex. All writes for one logical operation run inside a single knex.transaction callback that returns a promise chain. Commit happens only when the promise resolves, rollback on rejection.

Transaction shape and isolation

Use the promise-returning callback form so Knex can manage commit and rollback. Set isolation explicitly when the dialect supports it. The following pattern is intended for PostgreSQL with node-postgres. Replace client and connection values for your environment.

const db = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: { min: 2, max: 10 }
});

async function transfer(ctx, fromId, toId, amount) {
  return db.transaction(async trx => {
    await trx.raw('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
    const from = await trx('accounts')
      .where({ id: fromId })
      .forUpdate()
      .first();
    if (!from || from.balance < amount) throw new Error('insufficient funds');
    await trx('accounts').where({ id: fromId }).decrement('balance', amount);
    await trx('accounts').where({ id: toId }).increment('balance', amount);
    await trx('ledger').insert({ from_id: fromId, to_id: toId, amount });
    return { ok: true };
  });
}

Run this in the service process with permission to open a database connection and execute read/write queries. The callback must return the promise chain. Early returns or unhandled rejections can cause a commit despite errors, breaking atomicity.

Row locking with forUpdate

Critical reads use forUpdate inside the same transaction to acquire a pessimistic lock. The lock is held until commit or rollback, preventing another transaction from modifying the same rows. forUpdate and isolation level support vary by dialect. PostgreSQL supports forUpdate and REPEATABLE READ. MySQL supports locking reads with different syntax and isolation semantics. SQLite has limited transactional concurrency. Verify dialect behavior before relying on it.

Parameter binding boundary

All user-supplied values must be passed through the query builder so Knex can bind them. Avoid knex.raw with string interpolation for untrusted data. Even inside a transaction, raw interpolation reintroduces SQL injection risk. The builder ensures values appear as bound parameters, not concatenated SQL.

Trust and data boundaries

Treat the application process as trusted for query construction but untrusted for external input. The database is the authoritative trust boundary for consistency. Connection strings and secrets are supplied via environment, not code. Knex does not enforce schema or business rules; those remain application responsibilities. The service should validate input before it reaches the transaction and log only non-sensitive identifiers.

Operational checks

Monitor connection pool utilization and wait times. Long waits indicate pool exhaustion or transactions holding connections too long. Track transaction duration percentiles and rollback rate. Alert on deadlock errors, which the database will surface as transaction failures. Verify migration state and that migration locks are respected to avoid partial schema changes.

In non-production, enable Knex query logging and inspect that user inputs appear as bound parameters and that SELECT ... FOR UPDATE is emitted inside the transaction. Simulate concurrent updates against the same rows in integration tests to observe deadlock detection and automatic rollback behavior. Review migration history table and pool metrics after load tests to confirm transactions complete within target latency and pool exhaustion does not occur.

Failure modes

Deadlocks cause automatic rollback. The application must be prepared to retry idempotent operations with backoff. Connection loss mid-transaction leaves work incomplete; the database will roll back uncommitted changes, but in-flight application state may need reconciliation. Long-running transactions hold locks and degrade latency for other requests. Partial migration application can occur if migration locks are not respected, leading to schema drift.

When to change the design

Change the design if requirements move beyond a single database. Distributed transactions across services, cross-database writes, or writes to read replicas require explicit routing and saga or outbox patterns. Sharding requires partition-aware queries and may make row locking across partitions impractical. If audit requirements demand immutable append-only logs outside the transactional boundary, separate the audit write with a reliable outbox.

Limitations: Knex transaction semantics and row locking support vary by dialect and driver version. Behavior is version sensitive. Do not assume forUpdate or isolation levels are uniform across MySQL, PostgreSQL and SQLite. Mark any dialect-specific assumptions for review and verify with current documentation.