Transactions

Some operations only make sense all-or-nothing. Transferring money debits one account and credits another; placing an order writes the order row and decrements inventory. If half of that happens and the other half fails, your data is corrupt. A transaction groups multiple statements into a single atomic unit: either every change commits together, or none of them do. This page covers the ACID guarantees, the BEGIN/COMMIT/ROLLBACK mechanics, how each ORM exposes them, isolation levels, and the failure modes — deadlocks, long-held locks, and the connection rule that ties back to pooling.

ACID — what a transaction guarantees

Property	Guarantee
Atomicity	All statements commit, or none do — no partial writes
Consistency	Constraints (FKs, checks, uniqueness) hold before and after
Isolation	Concurrent transactions don't see each other's half-done work
Durability	Once committed, the change survives a crash (written to disk/WAL)

A transaction turns many statements into one indivisible operation

The classic example is a bank transfer: two `UPDATE`s that must both happen or neither. Wrap them in a transaction and a crash, error, or constraint violation between the two rolls *both* back — the account balances are never left inconsistent. ACID is the database's promise that, inside a transaction, you reason about your writes as a single step even though many things run concurrently underneath.

The mechanics: BEGIN, COMMIT, ROLLBACK

SQL

BEGIN;                                              -- start the transaction
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                                              -- make both permanent

-- If anything fails between BEGIN and COMMIT:
ROLLBACK;                                            -- undo everything since BEGIN

COMMIT makes it permanent; ROLLBACK discards everything since BEGIN

Between `BEGIN` and `COMMIT`, your changes are provisional — visible to your own transaction but not yet durable or visible to others (depending on isolation). `COMMIT` finalizes them atomically; `ROLLBACK` throws them all away. The golden rule in application code: open the transaction, do the work inside a `try`, `COMMIT` on success, and `ROLLBACK` in the `catch` so a thrown error never leaves a transaction half-applied.

Transactions in the raw Postgres driver (pg)

async function transfer(pool, fromId, toId, amount) {
  const client = await pool.connect()        // one dedicated connection
  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')            // undo on ANY failure
    throw e
  } finally {
    client.release()                          // ALWAYS return the connection
  }
}

A transaction must run on ONE checked-out connection — and you must release it

Every statement in a transaction has to travel down the **same** connection; the database tracks transaction state per-connection. That's why you call `pool.connect()` to check out a single `client` rather than using `pool.query()` (which may grab a different connection each call and would scatter your statements across separate sessions). And because you've manually borrowed it, you **must** `client.release()` in `finally` — forget it and that connection leaks, draining the [pool](/nodejs/connection-pooling) until the app hangs.

The same pattern in mysql2

const conn = await pool.getConnection()
try {
  await conn.beginTransaction()
  await conn.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1])
  await conn.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2])
  await conn.commit()
} catch (e) {
  await conn.rollback()
  throw e
} finally {
  conn.release()
}

ORM transaction APIs

Prisma — sequential array, or interactive callback

// Array form — all succeed or all roll back:
await prisma.$transaction([
  prisma.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } }),
  prisma.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } }),
])

// Interactive form — logic between queries; throw to roll back:
await prisma.$transaction(async (tx) => {
  const from = await tx.account.findUnique({ where: { id: 1 } })
  if (from.balance < 100) throw new Error('Insufficient funds')   // → ROLLBACK
  await tx.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } })
  await tx.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } })
})

Sequelize (managed) & TypeORM

// Sequelize managed transaction — auto-commits on resolve, rolls back on throw:
await sequelize.transaction(async (t) => {
  await Account.decrement('balance', { by: 100, where: { id: 1 }, transaction: t })
  await Account.increment('balance', { by: 100, where: { id: 2 }, transaction: t })
})

// TypeORM — the manager is scoped to the transaction:
await AppDataSource.transaction(async (manager) => {
  await manager.decrement(Account, { id: 1 }, 'balance', 100)
  await manager.increment(Account, { id: 2 }, 'balance', 100)
})

Managed transactions: resolve commits, throw rolls back — let errors propagate

Every modern ORM offers a **managed** (callback) transaction: you pass an async function, and the ORM runs `BEGIN` before it, `COMMIT` if it resolves, and `ROLLBACK` if it throws — checking out and releasing the connection for you. The key discipline is to do *all* the transaction's queries through the provided handle (`tx`/`t`/`manager`), and to let errors **propagate** rather than swallowing them — a caught-and-ignored error means the work commits when it shouldn't.

Isolation levels

Level	Prevents	Trade-off
Read Uncommitted	Nothing (can read dirty data)	Fastest, least safe (rare in PG)
Read Committed	Dirty reads	Default in Postgres; allows non-repeatable reads
Repeatable Read	Dirty + non-repeatable reads	Default in MySQL/InnoDB
Serializable	All anomalies — acts as if serial	Safest; may abort on conflict, must retry

Higher isolation = fewer anomalies but more contention

Isolation controls what one transaction can see of another's *uncommitted or in-flight* work. The defaults (Read Committed in Postgres, Repeatable Read in MySQL) are fine for most apps. Raise it to **Serializable** for operations where concurrent transactions could corrupt invariants (e.g. two people booking the last seat) — but be ready: Serializable can fail a transaction with a serialization error, and your code must **catch that and retry**. Higher isolation trades throughput for correctness.

Deadlocks

Text

Transaction A: locks row 1, then wants row 2
Transaction B: locks row 2, then wants row 1
                 → each waits for the other forever → DEADLOCK

The database detects the cycle, kills one transaction (error 40P01 / 1213),
and the victim must RETRY. Avoid by locking rows in a consistent order.

Deadlocks are normal under concurrency — order your locks and retry the victim

Two transactions that grab the same rows in **opposite order** can each hold what the other needs. The database breaks the tie by aborting one with a deadlock error — it does not hang forever. Two defenses: (1) always acquire locks in a **consistent order** (e.g. always update the lower account id first) so cycles can't form, and (2) wrap transaction-running code in a small **retry loop** for transient deadlock/serialization errors. Treat the occasional deadlock as expected, not exceptional.

Row locking for read-modify-write

SQL

BEGIN;
  -- Lock the row so no other transaction can change it until we commit:
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  -- ...check balance in app code, then:
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

`SELECT ... FOR UPDATE` prevents lost updates in check-then-write logic

When you read a value, decide based on it, then write — two concurrent transactions can both read the old value and one's update is **lost**. `SELECT ... FOR UPDATE` locks the matched rows for the duration of the transaction, forcing the second transaction to wait until the first commits. For simple counters, prefer an atomic expression (`balance = balance - 100`) which avoids the read entirely; use `FOR UPDATE` when application logic must sit between the read and the write.

Rules for healthy transactions

Keep them short — a transaction holds locks until commit; long ones block others and bloat the DB.
Never do slow I/O inside — no HTTP calls, no waiting on user input while a transaction is open.
One connection, released in finally — or use a managed/callback transaction that handles it.
Let errors roll back — throw out of the block; don’t catch-and-ignore.
Retry transient failures — deadlocks and serialization errors are expected; loop a few times.
Don't wrap reads that don't need it — transactions add overhead; use them where atomicity matters.

A long-open transaction holds locks and starves everyone else

The single biggest transaction anti-pattern is keeping one open too long — especially across a network call or any `await` on something other than the database. While open, it holds its locks and pins a connection, so other transactions queue behind it and the connection pool drains. Gather your data and validate *before* `BEGIN`, do only the necessary writes inside, and `COMMIT` as fast as possible. Short, focused transactions are the difference between a snappy app and one that mysteriously stalls under load.

You've covered persistence end-to-end. Next, lock down who can access it: [Authentication Intro](/nodejs/auth-intro).