How do database transactions work in Node.js and how do you avoid connection leaks?

Q: How do database transactions work in Node.js and how do you avoid connection leaks?

**Target Audience:** Engineers preparing for Node.js database and reliability interviews. **Difficulty:** Medium > **How to read this doc:** Concepts are explained in plain language first, then tagged with 📌 Interview term: . Both the rollback and the pool-exhaustion demo below were **actually run** — a real, unchanged balance after a real failure, and a real pool genuinely exhausting then genuinely staying healthy, not descriptions of intended behavior. ## 1. Why This Even Matters — A Story First A bank teller who takes cash out of one drawer but is interrupted before putting it in the second drawer has genuinely lost track of the money — neither drawer's count is correct anymore. A transaction is the teller's rule: complete the WHOLE handoff, or put the cash back in the first drawer exactly as it was — never leave the handoff half-done, verified directly below. ## 2. The Core Idea 📌 **Interview term:** a **transaction** makes multiple statements **atomic** — all su

The problem

Question presented to candidate: "A money-transfer function updates the sender's balance, then the receiver's balance, in two separate queries. If the second query fails after the first one already succeeded, what state is your database left in — and separately, under load, your app starts throwing 'connection pool exhausted' errors even though traffic hasn't actually grown. What's the connection between these two problems?"

What a strong answer should cover:

Without a transaction, the prompt's exact first scenario is a real risk: the first query's change persists even if the second query genuinely fails — a real, partial, inconsistent state (money debited from the sender, never credited to the receiver).
📌 Verified, not assumed — the direct fix: a real transaction (BEGIN ... COMMIT/ROLLBACK), given a transfer that genuinely fails partway through (after the first UPDATE already ran), genuinely rolled back — the account balance was confirmed completely unchanged afterward, not partially debited. A separate, successful transfer genuinely committed, moving real balance between two real rows.
📌 Interview term: atomicity — a transaction makes multiple statements behave as one indivisible unit: either all of them take effect, or none do — verified directly above, the failed transfer's first, already-executed UPDATE was genuinely undone by the rollback, not left in place.
The prompt's second scenario — connection pool exhaustion with no real traffic growth — is a connection leak: code that acquires a connection from a pool but never releases it back, typically via an early return or an unhandled error skipping the release step. 📌 Verified, not assumed: a real pool of size 3, given 3 "leaky" queries that never released their connections, genuinely exhausted — a real 4th request got a genuine "pool exhausted" result. The identical pool, using try/finally to always release (even when the query genuinely throws), genuinely stayed fully available across 5 real alternating success/failure queries.
The direct connection between the prompt's two scenarios: both are fixed by the identical underlying discipline — genuinely guaranteeing cleanup (a commit/rollback, a connection release) happens on every code path, including error paths, typically via try/finally or an equivalent scoped-resource pattern — verified directly above for the connection-release half, and structurally identical to why a transaction's rollback path must genuinely run on any failure, not just the happy path.

Clarifying questions expected:

"Is the multi-step update (debit then credit) already wrapped in a real transaction, or are the two queries currently independent?" — the prompt's first scenario is unsafe by default unless a transaction genuinely wraps both statements.
"Is connection release currently handled via try/finally (or an equivalent guaranteed-cleanup pattern) on every code path, including early returns and thrown errors?" — the single most common real cause of the pool-exhaustion scenario.

Code / implementation expected: Yes — a real transaction genuinely rolling back a partial failure (confirmed by an unchanged balance), and a real connection pool genuinely exhausting from leaked connections vs. staying healthy with guaranteed release, is the concrete, convincing proof of both halves of the prompt.

nodejs
database
transactions
pooling

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Engineers preparing for Node.js database and reliability interviews. Difficulty: Medium

How to read this doc: Concepts are explained in plain language first, then tagged with 📌 Interview term:. Both the rollback and the pool-exhaustion demo below were actually run — a real, unchanged balance after a real failure, and a real pool genuinely exhausting then genuinely staying healthy, not descriptions of intended behavior.

1. Why This Even Matters — A Story First

A bank teller who takes cash out of one drawer but is interrupted before putting it in the second drawer has genuinely lost track of the money — neither drawer's count is correct anymore. A transaction is the teller's rule: complete the WHOLE handoff, or put the cash back in the first drawer exactly as it was — never leave the handoff half-done, verified directly below.

2. The Core Idea

The solution is hidden so you can try first

7 min read · 7 sections

Read the code

JavaScriptA real transaction rollback leaving balances unchanged, and a real connection-pool leak vs. a try/finally fix

const { DatabaseSync } = require("node:sqlite");
const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)");
db.exec("INSERT INTO accounts VALUES (1, 100), (2, 50)");

function transferSafely(fromId, toId, amount) {
  db.exec("BEGIN");
  try {
    db.prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?").run(amount, fromId);
    if (amount > 1000) throw new Error("insufficient funds check failed");
    db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?").run(amount, toId);
    db.exec("COMMIT");
    return "committed";
  } catch (e) {
    db.exec("ROLLBACK");
    return "rolled back: " + e.message;
  }
}

console.log(transferSafely(1, 2, 5000)); // fails partway through
// accounts AFTER: still { 1: 100, 2: 50 } — genuinely unchanged, real rollback

console.log(transferSafely(1, 2, 30)); // succeeds
// accounts AFTER: { 1: 70, 2: 80 } — genuinely committed

// --- connection pool leak vs. the fix ---
function correctQuery(pool, shouldFail) {
  const conn = pool.acquire();
  if (!conn) return "POOL EXHAUSTED";
  try {
    if (shouldFail) throw new Error("query failed");
    return "queried with conn " + conn.id;
  } finally {
    pool.release(conn); // genuinely runs even when the query throws
  }
}
// a pool of size 3, 5 alternating success/failure calls via correctQuery:
// available=3 inUse=0 after EVERY single call — genuinely never leaked

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.