Transactions from Application Code

1. 1
   Acquire connection

   Takes one connection from the pool for the whole bracket.

   fails by Pool exhausted — the handler waits here before doing any work (Connection Pools).

2. 2
   BEGIN

   Starts the transaction; on MVCC engines, establishes the snapshot.

   fails by Forgotten entirely, so each statement autocommits and atomicity is imaginary.

3. 3
   Statements

   Reads and writes on that connection; writes take row locks and hold them.

   fails by A statement executed on a different connection — outside the bracket, invisible to the rollback.

4. 4
   Constraint checks

   Unique, foreign key and check constraints enforced at write or at commit if deferred.

   fails by On Postgres, one violation poisons the transaction; every later statement fails until rollback.

5. 5
   COMMIT

   Makes the WAL record durable; everything becomes visible at once.

   fails by Serialization failure, deferred constraint violation, or a dropped connection — the work did not happen.

6. 6
   Release connection

   Returns it to the pool.

   fails by Skipped on an error path: a leaked connection still inside an open transaction, holding locks (Connection Pool Exhaustion).

7. 7
   Post-commit effects

   Publish, enqueue, send, invalidate cache.

   fails by Anything here can fail after the commit is irreversible (The Dual Write Problem).

1async function withTransaction<T>(fn: (tx: Client) => Promise<T>): Promise<T> {
2  for (let attempt = 0; ; attempt++) {
3    const tx = await pool.connect()
4    try {
5      await tx.query('BEGIN')
6      const out = await fn(tx)          // every statement uses `tx`
7      await tx.query('COMMIT')
8      return out
9    } catch (err) {
10      await tx.query('ROLLBACK').catch(() => {})
11      if (isRetryable(err) && attempt < 3) continue   // 40001, 40P01
12      throw err
13    } finally {
14      tx.release()                     // runs on every path, including throw
15    }
16  }
17}
18 
19await withTransaction(async (tx) => {
20  const { rows } = await tx.query(
21    'INSERT INTO orders (tenant_id, total) VALUES ($1, $2) RETURNING id',
22    [tenantId, total],
23  )
24  await insertLineItems(tx, rows[0].id, items)      // handle passed down
25  await decrementStock(tx, items)
26})

Three properties to copy: the handle is a parameter so no callee can escape the bracket, finally releases on every path, and the retry re-runs the *whole* unit rather than the failed statement. isRetryable here means the SQLSTATEs the database uses for serialization failure and deadlock — not "the operation is safe to retry", which is a different property entirely (Retries).