Transaction Race Conditions: Why BEGIN on Pool Breaks Everything

Source: Dev.to

Problem

// ❌ Dangerous: Transaction on 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');
}

A PostgreSQL pool is a set of client connections. Each pool.query() can use a different client.

• Request 1: pool.query('BEGIN') → Client A
• Request 1: pool.query('UPDATE…') → Client B (different!)
• Request 2: pool.query('BEGIN') → Client A (reused!)

Your transaction is now spread across multiple clients, leading to data inconsistency.

Safe Approach

// ✅ Safe: Get dedicated client, use it for entire transaction
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();
  }
}

Using the same client for BEGIN, all queries, and COMMIT guarantees transaction integrity.

What Fails on the Pool Directly

// ❌ pool.query('BEGIN')      → Error
// ❌ pool.query('COMMIT')     → Error
// ❌ pool.query('ROLLBACK')   → Error
// ❌ pool.query('SAVEPOINT') → Error

What Works

// ✅ client.query('BEGIN')    → OK
// ✅ pool.query('SELECT…')    → OK (no transaction)

Linting Rule: no-transaction-on-pool

Install the ESLint plugin:

npm install --save-dev eslint-plugin-pg

Configure it:

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

The rule catches every misuse:

src/transfer.ts
  3:9  error  🔒 CWE-362 | Transaction command on pool - use pool.connect() for transactions
               Fix: const client = await pool.connect(); client.query('BEGIN');

Reusable Transaction Wrapper

// ✅ Reusable transaction wrapper
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();
  }
}

// Usage
await withTransaction(async (client) => {
  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],
  );
});

Usage Scenarios

Installation & Resources

npm install --save-dev eslint-plugin-pg

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

• npm package: eslint-plugin-pg
• Rule documentation: no-transaction-on-pool
• GitHub: https://github.com/your-repo/eslint-plugin-pg

Don’t let race conditions corrupt your data. ⭐️ Star the repository on GitHub for updates.