Stop 06 · SQL DatabasesTopics · 6/14

Transactions

In short

Money left your account but never reached your friend? A transaction makes sure a group of changes either all happen or none of them do.

A way to group multiple database operations so they all succeed together or all fail together — critical for operations like transferring money between two accounts.

The blob holds a phone while coins flow between two piggy banks tied by a safety rope.

Think of it like this

A UPI transfer

When you send 500 rupees on UPI, two things must happen: your balance drops and your friend's rises. If the bank's server crashed halfway, you'd lose money. So both steps are treated as one unit: done fully, or undone fully.

Watch it happen

A transfer that fails, then works

API

PostgreSQL

BEGIN

1/6The API opens a transaction. Changes stay private until it commits.

The big ideas

What you need to know

One unit of work

Wrap related changes between BEGIN and COMMIT. They succeed together or fail together.

ROLLBACK undoes everything

If any step fails, ROLLBACK throws away every change made since BEGIN.

One connection

Every query in a transaction must go through the same database connection, not random ones from the pool.

Keep them short

An open transaction holds locks. Don't call slow APIs or wait for users while one is open.

See it in code

Walk through the code

1import { Pool } from "pg";2const pool = new Pool();3export async function transfer(from: number, to: number, amount: number) {4  const client = await pool.connect();5  try {6    await client.query("BEGIN");7    await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, from]);8    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);9    await client.query("COMMIT");10  } catch (err) {11    await client.query("ROLLBACK");12    throw err;13  } finally {14    client.release();15  }16}

1/4Grab one dedicated connection. A transaction lives on a single connection.

pool.query() picks any free connection, so never run BEGIN with it. Check out one client with pool.connect(), as here.

Save yourself the pain

Common mistakes

Oops

Running BEGIN with pool.query() and the updates with more pool.query() calls.

Do this instead

Each pool.query can use a different connection. Check out one client and run the whole transaction on it.

Oops

Calling a payment gateway while the transaction is still open.

Do this instead

Do slow external calls before or after. Long transactions hold locks and block other users.

Your mission

Break a transfer on purpose

~20 min

Quick check

Inside a transaction, the first UPDATE works and the second throws. You run ROLLBACK. What's in the database?

Go deeper

1Wrap a multi-step operation (e.g. debit one account, credit another) in a transaction.
2Deliberately trigger a failure mid-transaction and confirm the rollback works.
3Learn `BEGIN`, `COMMIT` and `ROLLBACK` at the SQL level.