DEV Community

Cover image for BEGIN on a Postgres Pool Scatters Your Transaction Across Connections. One ESLint Rule Stops It.
Ofri Peretz
Ofri Peretz

Posted on • Edited on • Originally published at ofriperetz.dev

BEGIN on a Postgres Pool Scatters Your Transaction Across Connections. One ESLint Rule Stops It.

#eslint #postgres #node #database

This passes every test and works perfectly in development. Under 100 concurrent
users in production, it silently corrupts account balances:

// ❌ a "transaction" on the pool
async function transferFunds(from, to, amount) {
  await pool.query("BEGIN");
  await pool.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [
    amount,
    from,
  ]);
  await pool.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [
    amount,
    to,
  ]);
  await pool.query("COMMIT");
}
Enter fullscreen mode Exit fullscreen mode

Why it corrupts data

A Pool is a set of connections. Each pool.query() checks out whatever
client is free at that moment — so the four statements above can run on four
different connections:

pool.query('BEGIN')      → Client A   (a transaction opens on A)
pool.query('UPDATE …')   → Client B   (runs outside A's transaction!)
pool.query('UPDATE …')   → Client C
pool.query('COMMIT')     → Client A   (commits an empty transaction)
Enter fullscreen mode Exit fullscreen mode

The BEGIN and COMMIT land on a client that never saw the UPDATEs. The
updates run as autocommitted statements on other clients — no atomicity, no
rollback, no isolation. Two concurrent transfers interleave and the balance is
wrong. This is a textbook race condition (CWE-362) — and it's invisible until
concurrency is high enough to scatter the statements.

The fix: one client for the whole transaction 

// ✅ BEGIN, every query, and COMMIT on the SAME client
async function transferFunds(from, to, amount) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query(
      "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
      [amount, from],
    );
    await client.query(
      "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
      [amount, to],
    );
    await client.query("COMMIT");
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release(); // always return the client to the pool
  }
}
Enter fullscreen mode Exit fullscreen mode

A checked-out client is a single connection held for the duration — BEGIN,
every UPDATE, and COMMIT execute on it, so the transaction is atomic. (And
release it in finally, or you trade a race condition for a connection
leak.)

The rule: no-transaction-on-pool

You don't want to rely on every engineer remembering pool-vs-client semantics.
The rule flags a transaction-control statement issued on a pool:

npm install --save-dev eslint-plugin-pg
Enter fullscreen mode Exit fullscreen mode 
// eslint.config.mjs — `configs` is a NAMED export (default export is the plugin)
import { configs } from "eslint-plugin-pg";

export default [configs.recommended];
Enter fullscreen mode Exit fullscreen mode 
src/transfer.js
  3:9  error  ⚠️ Transactions should not be started on the Pool directly. | HIGH
             Fix: Use "await pool.connect()" to get a client, then start the transaction on the client.
Enter fullscreen mode Exit fullscreen mode

(The ESLint CLI also appends the rule's doc URL to the Fix: line; trimmed
here.) It catches BEGIN, COMMIT, and ROLLBACK on a pool.query() — and
stays silent on a plain pool.query('SELECT …') (a single query needs no
transaction) and on client.query('BEGIN') (the correct form). (The rule's own
docs tag the narrower CWE-662, Improper Synchronization; the underlying bug class
is the race condition, CWE-362.) It keys on a string-literal first argument to a
pool-named object's .query(), so a transaction built from a template literal
or held in a differently-named variable still warrants a human look.

Make it reusable

Wrap the borrow→begin→commit→release dance once, and every transaction is correct
by construction:

async function withTransaction(callback) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await callback(client);
    await client.query("COMMIT");
    return result;
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();
  }
}

await withTransaction((client) =>
  Promise.all([
    client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [
      amount,
      from,
    ]),
    client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [
      amount,
      to,
    ]),
  ]),
);
Enter fullscreen mode Exit fullscreen mode

When to use what

Scenario Use
Single query pool.query()
Multiple independent queries pool.query() (no atomicity needed)
Transaction (BEGIN/COMMIT) pool.connect() → client.query()
Long-running session pool.connect() → client.query()

Compatibility

Surface Support
Package managers npm, yarn, pnpm, bun
Node >= 18.0.0
ESLint `^8.0.0 \
{% raw %}pg driver peer `^6 \
Module system Plugin ships CommonJS; your config can be {% raw %}eslint.config.js or .mjs
Oxlint Loads under Oxlint's JS-plugin runner via the interlace-pg port, parity-gated in CI

Where this fits

no-transaction-on-pool is the atomicity member of eslint-plugin-pg. The rest
of the data-layer threat model:

Links

⭐ Star on GitHub if you've ever wrapped pool.query("BEGIN") and called it a transaction.

I'm Ofri Peretz, a security engineering leader and the author of the
Interlace ESLint ecosystem — domain-specific static analysis for security,
reliability, and performance on the Node.js stack. eslint-plugin-pg is its
node-postgres layer.

ofriperetz.dev · LinkedIn · GitHub

Top comments (0)