Node.js 48 🟢 Managing SQL Database Transactions in Node.js - KandZ

A transaction is a group of SQL statements that execute as the single unit. The bank transfer debits the one account and credits the other, and the two statements must succeed or fail together. The Node.js application that manages the money must use the transactions, or the partial failure leaves the money in the wrong place. The pg and the mysql2 drivers provide the BEGIN, the COMMIT, and the ROLLBACK, and the ORMs — the Knex, the Prisma, the Drizzle, the Sequelize — wrap them in the higher-level API. The transaction is the foundation of the data integrity, and the Node.js application must understand the scoping, the isolation, and the error handling.

The transaction is scoped to the single connection. The pool.query() checks out the connection, runs the query, and releases it, and the next pool.query() may use the different connection. The transaction must use the same connection for the BEGIN, the statements, and the COMMIT. The pool.connect() checks out the client explicitly, and the client.release() returns it. The using the pool.query() for the transaction is the classic mistake, and the two statements may run on the different connections and the different transactions.

The error handling is the critical. The try/catch/finally pattern ensures the ROLLBACK on the failure and the release in the finally. The client.release() must be in the finally, or the connection leaks. The client.release(true) destroys the connection instead of returning it to the pool, and the true is the argument when the connection is in the unknown state. The deadlock and the serialization failure are the retryable, and the application must catch the specific error codes and retry the transaction.

This chapter covers three areas. First, why the transactions exist — the atomicity problem and the connection-scoping. Second, the transaction patterns — the pg and the mysql2 drivers, the Knex, the Prisma, the Drizzle, and the error handling. Third, the advanced patterns — the savepoints, the isolation levels, the retries, and the deadlock handling. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the transaction flow.

Key point: A SQL transaction is a group of statements that execute as the single unit. The transaction is scoped to the single connection, so the pool.connect() and the client.release() are the requirement. The try/catch/finally ensures the ROLLBACK on the failure and the release in the finally. The ORMs — the Knex, the Prisma, the Drizzle, the Sequelize — provide the transaction API, and the application must handle the serialization failures and the deadlocks with the retry.

Why the transactions exist

The atomicity problem. A business operation is often the several statements. The transfer debits the one account and credits the other. The order inserts the order row, the order items, and the decrements the inventory. If the one statement fails after the others succeed, the database is the inconsistent. The transaction groups the statements, and the failure rolls back the whole group. The atomicity is the all-or-nothing, and the transaction is the mechanism.

The connection-scoping problem. The pg and the mysql2 pools manage the many connections, and the pool.query() uses the arbitrary one. The transaction must run on the single connection, because the BEGIN and the COMMIT are the connection state. The pool.query('BEGIN') followed by the pool.query('INSERT') may run on the different connections, and the INSERT is the auto-commit and the ROLLBACK does nothing. The pool.connect() is the correct, and the client.release() is the cleanup.

The concurrent problem. The two transactions read and write the same row, and the interleaving produces the lost update. The SELECT reads the balance, the two sessions add the different amounts, and the second write overwrites the first. The isolation level controls the interleaving, and the SELECT ... FOR UPDATE takes the row lock. The transaction is the mechanism, and the isolation level is the strictness.

The error-handling problem. The transaction that throws the error in the middle must roll back. The try/catch/finally is the pattern, and the client.release() is the finally. The missing rollback leaves the transaction open, and the connection is the idle in the transaction. The idle_in_transaction_session_timeout is the PostgreSQL setting that kills the idle transaction, and the application should not rely on it.

The retry problem. The serialization failure and the deadlock are the transient, and the retry is the correct response. The PostgreSQL returns the 40001 for the serialization failure and the 40P01 for the deadlock. The application must catch the codes and retry the whole transaction. The retry requires the idempotent transaction, and the transaction must be the re-runnable.

The trade-off. The transaction adds the complexity: the checkout, the try/catch/finally, the retry. The single-statement operation does not need the transaction, because the single statement is the atomic. The multi-statement operation needs the transaction, and the trade-off is between the integrity and the complexity.

a. The pg and the mysql2 transactions

The pg. The pool.connect() checks out the client, and the client.query() runs the statements.

const client = await pool.connect();

try {
  await client.query('BEGIN');

  const fromResult = await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2 RETURNING balance',
    [amount, fromId]
  );

  if (fromResult.rows[0].balance < 0) {
    throw new Error('Insufficient funds');
  }

  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [amount, toId]
  );

  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

The BEGIN starts the transaction, and the COMMIT writes the changes. The ROLLBACK in the catch undoes the changes, and the release in the finally returns the connection.

The mysql2. The pool.getConnection() checks out the connection, and the beginTransaction, the commit, and the rollback are the methods.

const conn = await pool.getConnection();

try {
  await conn.beginTransaction();

  const [fromRows] = await conn.execute(
    'SELECT balance FROM accounts WHERE id = ? FOR UPDATE',
    [fromId]
  );

  if (fromRows[0].balance < amount) {
    throw new Error('Insufficient funds');
  }

  await conn.execute(
    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
    [amount, fromId]
  );
  await conn.execute(
    'UPDATE accounts SET balance = balance + ? WHERE id = ?',
    [amount, toId]
  );

  await conn.commit();
} catch (e) {
  await conn.rollback();
  throw e;
} finally {
  conn.release();
}

The beginTransaction starts, and the commit and the rollback are the counterparts. The FOR UPDATE locks the row.

The pool.query mistake. The pool.query('BEGIN') does not work, because the next pool.query may use the different connection.

// ❌ The wrong
await pool.query('BEGIN');
await pool.query('UPDATE ...');
await pool.query('COMMIT');

// ✅ The correct
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE ...');
  await client.query('COMMIT');
} finally {
  client.release();
}

b. The ORM transactions

The Knex. The knex.transaction takes the callback, and the trx is the transaction object.

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

The callback resolves and the commit happens, and the callback throws and the rollback happens. The trx must be used instead of the knex inside the callback.

The Prisma. The $transaction has the two forms: the sequential and the interactive.

// The sequential
const [from, to] = await prisma.$transaction([
  prisma.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  }),
  prisma.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  }),
]);

// The interactive
await prisma.$transaction(async (tx) => {
  const from = await tx.account.findUniqueOrThrow({ where: { id: fromId } });
  if (from.balance < amount) throw new Error('Insufficient funds');

  await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  });
  await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  });
});

The sequential form takes the array of the queries, and the interactive form takes the callback. The tx is the transaction client, and the prisma is the outer client.

The Drizzle. The db.transaction takes the callback, and the tx is the transaction object.

await db.transaction(async (tx) => {
  const [from] = await tx
    .select()
    .from(accounts)
    .where(eq(accounts.id, fromId))
    .for('update');

  if (from.balance < amount) throw new Error('Insufficient funds');

  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} - ${amount}` })
    .where(eq(accounts.id, fromId));

  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} + ${amount}` })
    .where(eq(accounts.id, toId));
});

The tx is the transaction object, and the .for('update') locks the row.

The Sequelize. The sequelize.transaction takes the callback, and the transaction object is the argument.

await sequelize.transaction(async (t) => {
  await Account.decrement('balance', {
    by: amount,
    where: { id: fromId },
    transaction: t,
  });
  await Account.increment('balance', {
    by: amount,
    where: { id: toId },
    transaction: t,
  });
});

The transaction: t passes the transaction to the query.

c. The advanced patterns

The savepoint. The SAVEPOINT marks the point within the transaction, and the ROLLBACK TO SAVEPOINT undoes the changes since the point.

const client = await pool.connect();
try {
  await client.query('BEGIN');

  await client.query('INSERT INTO orders ...');
  await client.query('SAVEPOINT after_order');

  try {
    await client.query('INSERT INTO order_items ...');
  } catch (e) {
    await client.query('ROLLBACK TO SAVEPOINT after_order');
  }

  await client.query('COMMIT');
} finally {
  client.release();
}

The savepoint isolates the failure of the order_items insert, and the order is preserved.

The isolation level. The SET TRANSACTION ISOLATION LEVEL sets the level for the current transaction.

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
  // ...
  await client.query('COMMIT');
} finally {
  client.release();
}

The SERIALIZABLE is the strictest, and the serialization failure requires the retry.

The retry. The serialization failure and the deadlock are the retryable.

async function withRetry(fn, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await fn();
    } catch (e) {
      const isRetryable = e.code === '40001' || e.code === '40P01';
      if (!isRetryable || attempt === maxRetries) throw e;
      await new Promise((r) => setTimeout(r, 100 * attempt));
    }
  }
}

await withRetry(async () => {
  await pool.transaction(async (client) => {
    // the transaction
  });
});

The 40001 is the serialization failure, and the 40P01 is the deadlock. The exponential backoff is the pattern.

The FOR UPDATE. The SELECT ... FOR UPDATE locks the row, and the concurrent transaction waits.

const client = await pool.connect();
try {
  await client.query('BEGIN');

  const result = await client.query(
    'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
    [fromId]
  );

  if (result.rows[0].balance < amount) {
    throw new Error('Insufficient funds');
  }

  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [amount, fromId]
  );
  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [amount, toId]
  );

  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

The FOR UPDATE prevents the lost update, and the consistent lock order prevents the deadlock.

Complete Example Session

// ============================================
// PART 1: THE PG TRANSACTION
// ============================================

const client = await pool.connect();

try {
  await client.query('BEGIN');

  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [amount, fromId]
  );
  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [amount, toId]
  );

  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}


// ============================================
// PART 2: THE MYSQL2 TRANSACTION
// ============================================

const conn = await pool.getConnection();

try {
  await conn.beginTransaction();

  await conn.execute(
    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
    [amount, fromId]
  );
  await conn.execute(
    'UPDATE accounts SET balance = balance + ? WHERE id = ?',
    [amount, toId]
  );

  await conn.commit();
} catch (e) {
  await conn.rollback();
  throw e;
} finally {
  conn.release();
}


// ============================================
// PART 3: THE POOL.QUERY MISTAKE
// ============================================

// ❌ The wrong
await pool.query('BEGIN');
await pool.query('UPDATE ...');
await pool.query('COMMIT');

// ✅ The correct
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE ...');
  await client.query('COMMIT');
} finally {
  client.release();
}


// ============================================
// PART 4: THE KNEX TRANSACTION
// ============================================

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


// ============================================
// PART 5: THE PRISMA TRANSACTION
// ============================================

await prisma.$transaction(async (tx) => {
  const from = await tx.account.findUniqueOrThrow({ where: { id: fromId } });
  if (from.balance < amount) throw new Error('Insufficient funds');

  await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  });
  await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  });
});


// ============================================
// PART 6: THE DRIZZLE TRANSACTION
// ============================================

await db.transaction(async (tx) => {
  const [from] = await tx
    .select()
    .from(accounts)
    .where(eq(accounts.id, fromId))
    .for('update');

  if (from.balance < amount) throw new Error('Insufficient funds');

  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} - ${amount}` })
    .where(eq(accounts.id, fromId));

  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} + ${amount}` })
    .where(eq(accounts.id, toId));
});


// ============================================
// PART 7: THE SAVEPOINT
// ============================================

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO orders ...');
  await client.query('SAVEPOINT after_order');

  try {
    await client.query('INSERT INTO order_items ...');
  } catch (e) {
    await client.query('ROLLBACK TO SAVEPOINT after_order');
  }

  await client.query('COMMIT');
} finally {
  client.release();
}


// ============================================
// PART 8: THE ISOLATION LEVEL
// ============================================

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
  // ...
  await client.query('COMMIT');
} finally {
  client.release();
}


// ============================================
// PART 9: THE RETRY
// ============================================

async function withRetry(fn, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await fn();
    } catch (e) {
      const isRetryable = e.code === '40001' || e.code === '40P01';
      if (!isRetryable || attempt === maxRetries) throw e;
      await new Promise((r) => setTimeout(r, 100 * attempt));
    }
  }
}


// ============================================
// PART 10: THE COMPLETE TRANSFER
// ============================================

async function transfer(fromId, toId, amount) {
  return withRetry(async () => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');

      const fromResult = await client.query(
        'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
        [fromId]
      );

      if (fromResult.rows[0].balance < amount) {
        throw new Error('Insufficient funds');
      }

      await client.query(
        'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
        [amount, fromId]
      );
      await client.query(
        'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
        [amount, toId]
      );

      await client.query('COMMIT');
    } catch (e) {
      await client.query('ROLLBACK');
      throw e;
    } finally {
      client.release();
    }
  });
}

The ten parts show the pg transaction, the mysql2 transaction, the pool.query mistake, the Knex, the Prisma, the Drizzle, the savepoint, the isolation level, the retry, and the complete transfer.

Quick Reference

The pg Methods

The method	The purpose
`pool.connect()`	The checkout
`client.query('BEGIN')`	The start
`client.query('COMMIT')`	The write
`client.query('ROLLBACK')`	The undo
`client.release()`	The return

The mysql2 Methods

The method	The purpose
`pool.getConnection()`	The checkout
`conn.beginTransaction()`	The start
`conn.commit()`	The write
`conn.rollback()`	The undo
`conn.release()`	The return

The ORM Transactions

The ORM	The API
Knex	`knex.transaction(async (trx) => {})`
Prisma	`prisma.$transaction(async (tx) => {})`
Drizzle	`db.transaction(async (tx) => {})`
Sequelize	`sequelize.transaction(async (t) => {})`

The Error Codes

The code	The meaning
`40001`	The serialization failure
`40P01`	The deadlock
`23505`	The unique violation
`23503`	The foreign key violation

The Isolation Levels

The level	The anomalies
`READ COMMITTED`	The non-repeatable, the phantom
`REPEATABLE READ`	The phantom (the PostgreSQL prevents)
`SERIALIZABLE`	The none; the retry required

Best Practices

✅ Do This:

// Use the pool.connect for the transaction
const client = await pool.connect();                                      // ✅
// Use the try/catch/finally
try { ... } catch (e) { await client.query('ROLLBACK'); } finally { client.release(); } // ✅
// Use the FOR UPDATE for the read-modify-write
'SELECT ... FOR UPDATE'                                                   // ✅
// Use the retry for the serialization failure
if (e.code === '40001') retry();                                          // ✅
// Use the savepoint for the partial rollback
'SAVEPOINT after_order'                                                   // ✅
// Use the ORM transaction for the consistency
await knex.transaction(async (trx) => { ... });                           // ✅
// Use the consistent lock order
// The ascending id                                                       // ✅

❌ Don’t Do This:

// Don't use the pool.query for the transaction
await pool.query('BEGIN');  // the different connection                  // ❌
// Don't forget the release
const client = await pool.connect();  // never released                   // ❌
// Don't forget the ROLLBACK
try { ... } catch (e) { throw e; }  // the transaction stays open         // ❌
// Don't use the SERIALIZABLE without the retry
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  // the 40001              // ❌
// Don't lock the rows in the inconsistent order
// The deadlock                                                           // ❌
// Don't ignore the error code
catch (e) { /* the silent */ }                                            // ❌
// Don't use the transaction for the single statement
await pool.transaction(async () => { await pool.query('SELECT 1'); });    // ❌

Common Pitfalls

The pitfall	The why	The fix
The transaction does not work	The `pool.query`	Use the `pool.connect`
The connection leak	The missing release	Use the `finally`
The transaction stays open	The missing rollback	Use the `catch`
The serialization failure	The `SERIALIZABLE`	Add the retry
The deadlock	The inconsistent order	Lock in the ascending order
The lost update	The no `FOR UPDATE`	Add the lock
The partial commit	The wrong connection	Use the same client

Real-World Examples

1. The pg Transaction

const client = await pool.connect();
try { await client.query('BEGIN'); ... } finally { client.release(); }

2. The mysql2 Transaction

const conn = await pool.getConnection();
try { await conn.beginTransaction(); ... } finally { conn.release(); }

3. The Knex

await knex.transaction(async (trx) => { ... });

4. The Prisma

await prisma.$transaction(async (tx) => { ... });

5. The Drizzle

await db.transaction(async (tx) => { ... });

6. The Savepoint

await client.query('SAVEPOINT after_order');

7. The Isolation

await client.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

8. The Retry

if (e.code === '40001') { /* retry */ }

9. The FOR UPDATE

'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE'

10. The Complete

async function transfer(from, to, amount) { /* the transaction */ }

Visual

The Transaction Flow

┌──────────────────────────────────────────────────────────────┐
│  THE TRANSACTION FLOW                                        │
│                                                              │
│  pool.connect()                                              │
│    │                                                         │
│    │  The connection is checked out.                        │
│    ▼                                                         │
│  client.query('BEGIN')                                       │
│    │                                                         │
│    │  The transaction starts.                               │
│    ▼                                                         │
│  client.query('UPDATE ...')                                  │
│    │                                                         │
│    ├── the success → client.query('COMMIT')                  │
│    │     The changes are written.                           │
│    │                                                         │
│    └── the error → client.query('ROLLBACK')                  │
│          The changes are undone.                            │
│    │                                                         │
│    ▼                                                         │
│  client.release()                                            │
│    │                                                         │
│    │  The connection is returned to the pool.               │
│    ▼                                                         │
│  The done.                                                   │
│                                                              │
└──────────────────────────────────────────────────────────────┘

The pool.query Mistake

┌──────────────────────────────────────────────────────────────┐
│  THE pool.query MISTAKE                                      │
│                                                              │
│  ❌ THE WRONG:                                                │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  await pool.query('BEGIN');                          │    │
│  │    → the connection 1                                │    │
│  │                                                       │    │
│  │  await pool.query('UPDATE ...');                     │    │
│  │    → the connection 2 (the DIFFERENT)                │    │
│  │    → the auto-commit                                 │    │
│  │                                                       │    │
│  │  await pool.query('ROLLBACK');                       │    │
│  │    → the connection 3                                │    │
│  │    → the nothing to roll back                        │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  ✅ THE CORRECT:                                              │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  const client = await pool.connect();                │    │
│  │                                                       │    │
│  │  await client.query('BEGIN');                        │    │
│  │  await client.query('UPDATE ...');                   │    │
│  │  await client.query('COMMIT');                       │    │
│  │                                                       │    │
│  │  client.release();                                   │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
└──────────────────────────────────────────────────────────────┘

The Retry

┌──────────────────────────────────────────────────────────────┐
│  THE RETRY                                                   │
│                                                              │
│  The attempt 1:                                              │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  BEGIN → UPDATE → the 40001 error                    │    │
│  │  ROLLBACK                                             │    │
│  └──────────────────────────────────────────────────────┘    │
│    │                                                         │
│    │  the retryable? the 40001 → yes                       │
│    ▼                                                         │
│  The attempt 2:                                              │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  BEGIN → UPDATE → the 40001 error                    │    │
│  │  ROLLBACK                                             │    │
│  └──────────────────────────────────────────────────────┘    │
│    │                                                         │
│    │  the retryable? the 40001 → yes                       │
│    ▼                                                         │
│  The attempt 3:                                              │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  BEGIN → UPDATE → the COMMIT                         │    │
│  │  The success!                                         │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  The max retries: the 3. The backoff: the 100ms, the 200ms.  │
│                                                              │
└──────────────────────────────────────────────────────────────┘

The Isolation Levels

┌──────────────────────────────────────────────────────────────┐
│  THE ISOLATION LEVELS                                        │
│                                                              │
│  READ COMMITTED (the default):                               │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  The non-repeatable read is possible.                │    │
│  │  The phantom read is possible.                       │    │
│  │  The fastest, the least strict.                      │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  REPEATABLE READ:                                            │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  The non-repeatable read is prevented.               │    │
│  │  The PostgreSQL also prevents the phantom.           │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  SERIALIZABLE:                                               │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  All the anomalies are prevented.                    │    │
│  │  The serialization failure (the 40001) possible.     │    │
│  │  The retry is the requirement.                       │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
The transaction	The group of statements as the unit
The pg	`pool.connect()` + the `BEGIN`
The mysql2	`pool.getConnection()` + the `beginTransaction`
The Knex	`knex.transaction(async (trx) => {})`
The Prisma	`prisma.$transaction(async (tx) => {})`
The Drizzle	`db.transaction(async (tx) => {})`
The savepoint	The partial rollback
The FOR UPDATE	The row lock
The serialization failure	The `40001`
The deadlock	The `40P01`
The retry	The exponential backoff

Key takeaways:

A SQL transaction is a group of statements that execute as the single unit. The BEGIN starts it, the COMMIT writes the changes, and the ROLLBACK undoes them. The transaction is scoped to the single connection.
The pool.connect() is the requirement for the transaction. The pool.query() uses the arbitrary connection, and the BEGIN and the UPDATE may run on the different connections. The client.release() returns the connection to the pool.
The try/catch/finally is the pattern. The catch runs the ROLLBACK, and the finally runs the release. The missing release leaks the connection, and the missing ROLLBACK leaves the transaction open.
The ORMs wrap the transaction in the higher-level API. The Knex, the Prisma, the Drizzle, and the Sequelize provide the transaction function, and the trx or the tx is the transaction object.
The FOR UPDATE locks the row. The read-modify-write requires the lock, or the lost update occurs. The consistent lock order prevents the deadlock.
The serialization failure and the deadlock are the retryable. The 40001 is the serialization failure, and the 40P01 is the deadlock. The application must catch the codes and retry the whole transaction with the exponential backoff.
The savepoint allows the partial rollback. The SAVEPOINT marks the point, and the ROLLBACK TO SAVEPOINT undoes the changes since the point while keeping the earlier changes.
The isolation level controls the concurrency. The READ COMMITTED is the default, and the SERIALIZABLE is the strictest. The higher the level, the fewer the anomalies and the more the retries. The result is the consistent data, which is the foundation of the reliable application.

Remember: The SQL transaction is the group of statements that execute as the single unit. The pool.connect() checks out the connection, and the try/catch/finally ensures the ROLLBACK on the failure and the release in the finally. The ORMs provide the transaction API, and the application must handle the serialization failures and the deadlocks with the retry. The result is the atomic, the consistent, and the isolated data, which is the foundation of the reliable Node.js application.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!