Database Transactions & ACID

Reviewed & published by Brayan K

When a single operation touches several rows — like moving money between two accounts — you need it to be all-or-nothing. A transaction groups those statements so they either all succeed or all roll back, never leaving your data half-changed.

Part of the free Node.js course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll learn the ACID properties, the BEGIN / COMMIT / ROLLBACK flow, and the canonical node-postgres pattern with a pooled client — plus isolation levels and deadlocks.

What You'll Learn in This Lesson

• The four ACID properties and what each guarantees
• BEGIN, COMMIT, and ROLLBACK semantics
• Running a transaction on a single pg client
• The try / commit / catch-rollback / finally-release pattern
• Isolation levels from READ COMMITTED to SERIALIZABLE
• What deadlocks are and how to handle them

1️⃣ BEGIN, COMMIT, ROLLBACK

A transaction starts with BEGIN, runs any number of statements, and ends with either COMMIT (make it permanent) or ROLLBACK (undo it all). This all-or-nothing behavior is atomicity — the A in ACID. A crash mid-transfer can never leave money missing.

-- A transaction groups several statements into ONE all-or-nothing unit.
-- Classic example: move $100 from account A to account B. BOTH updates
-- must happen, or NEITHER should. A crash in the middle must not leave
-- money missing.

BEGIN;                                   -- start the transaction

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

COMMIT;                                  -- make both changes permanent

-- If anything had failed between BEGIN and COMMIT, you would instead run:
--   ROLLBACK;                           -- undo BOTH updates, as if nothing ran
-- That all-or-nothing behavior is ATOMICITY, the A in ACID.

Between BEGIN and COMMIT, the two updates are a single unit. Either both land or, with ROLLBACK, neither does — the database returns to exactly the state it had before BEGIN.

2️⃣ The node-postgres Pattern

In Node, a transaction must run on one connection. Because pool.query can pick a different connection per call, you check out a single client with pool.connect() and run BEGIN, the work, and COMMIT on it. The shape is always the same: try / commit, catch / rollback, finally / release.

// install: npm install pg
// In node-postgres you MUST run a transaction on a SINGLE client checked
// out of the pool. pool.query() may pick a different connection each call,
// so BEGIN, the work, and COMMIT could end up on different sessions.
import { Pool } from "pg";
const pool = new Pool();

async function transfer(fromId, toId, amount) {
  const client = await pool.connect();   // check ONE connection out of the pool
  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");        // success: make it permanent
  } catch (err) {
    await client.query("ROLLBACK");      // any failure: undo everything
    throw err;                           // let the caller know it failed
  } finally {
    client.release();                    // ALWAYS return the connection
  }
}

The finally block is non-negotiable: client.release() always runs, whether you committed or rolled back, so the connection returns to the pool. Forget it and you'll leak connections until the pool is exhausted and your app hangs.

• • pool.connect() — check out one connection for the transaction.
• • try → BEGIN, work, COMMIT — the success path.
• • catch → ROLLBACK, rethrow — undo on any error.
• • finally → client.release() — always return the connection.

3️⃣ Isolation Levels & Deadlocks

Isolation — the I in ACID — controls how much one in-flight transaction sees of others. PostgreSQL defaults to READ COMMITTED; stricter levels like REPEATABLE READ and SERIALIZABLE prevent more anomalies but reduce concurrency and may force you to retry a transaction.

// ISOLATION (the I in ACID) controls how much one in-flight transaction
// can see of others. Higher levels prevent more anomalies but reduce
// concurrency and may force retries.
//
//   READ COMMITTED  (PostgreSQL default) - sees only committed data,
//                    but two reads in the same txn can differ.
//   REPEATABLE READ - same rows read twice look the same all txn long.
//   SERIALIZABLE    - behaves as if transactions ran one at a time;
//                    the strictest, and may abort a txn to keep that
//                    guarantee (you then RETRY it).

async function reportUnderSerializable(client) {
  // Set the level right after BEGIN, before any data statements.
  await client.query("BEGIN");
  await client.query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");

  // ... your reads/writes run with the strongest guarantee ...

  await client.query("COMMIT");
  // On a serialization_failure error, ROLLBACK and run the whole txn again.
}

A deadlock happens when two transactions each hold a lock the other needs. The database detects the cycle and aborts one with a deadlock error; the right response is to catch it and retry the aborted transaction. Acquiring locks in a consistent order and keeping transactions short makes deadlocks rare.

Your turn. Fill in the three ___ blanks to complete the canonical safe pattern.

// 🎯 YOUR TURN — fill in the ___ blanks to complete the safe pattern.
async function placeOrder(client, order) {
  try {
    await client.query("___");           // 👉 1) start the transaction

    await client.query("INSERT INTO orders(...) VALUES(...)");
    await client.query("UPDATE inventory SET qty = qty - 1 WHERE ...");

    await client.query("COMMIT");        // success path
  } catch (err) {
    await client.query("___");           // 👉 2) undo on failure
    throw err;
  } finally {
    client.___();                        // 👉 3) return the connection
  }
}

// ✅ Blanks are: BEGIN, then ROLLBACK, then release. That try / commit /
//    catch-rollback / finally-release shape is the canonical pg pattern.

Mini-Challenge: Force a Rollback

Write a transfer that deliberately throws after the first UPDATE but before COMMIT — for example by violating a CHECK constraint that forbids a negative balance. Confirm that after the error, neither balance changed, because your catch block ran ROLLBACK. Then remove the throw and watch both balances update together on COMMIT. Seeing the all-or-nothing behavior with your own data is the moment atomicity clicks.

Common Errors (and the fix)

• Using pool.query for a transaction — Each call may hit a different connection, so BEGIN and COMMIT land on different sessions. Check out one client with pool.connect().
• Forgetting client.release() — Leaks the connection forever; the pool drains and the app stalls. Always release in finally.
• No ROLLBACK on error — Leaves a transaction open holding locks. Always ROLLBACK in the catch block before rethrowing.
• Ignoring deadlock errors — A deadlocked transaction is aborted by the database. Catch the error and retry it rather than failing the request.
• Cranking isolation to SERIALIZABLE everywhere — It blocks more anomalies but raises contention and serialization failures. Use the lowest level that is correct for the operation.

Pro Tips

• 💡 Keep transactions short: the longer a transaction holds locks, the more it blocks others and invites deadlocks. Do slow work (HTTP calls, heavy CPU) outside the transaction.
• 💡 Wrap the pattern in a helper that takes a callback, runs BEGIN/COMMIT/ROLLBACK and release for you, so every transaction in your codebase is correct by construction.
• 💡 Always use parameterized queries ($1, $2) inside transactions too — never string-concatenate values, which opens you to SQL injection.

📋 Quick Reference — Transactions in pg

🎉 Lesson Complete — and You've Finished the Node.js Course!

• ✅ ACID = Atomicity, Consistency, Isolation, Durability
• ✅ BEGIN / COMMIT / ROLLBACK make a change all-or-nothing
• ✅ Run a transaction on one pooled client, never pool.query
• ✅ The try / commit / catch-rollback / finally-release pattern is canonical
• ✅ Isolation levels trade anomalies for concurrency
• ✅ Handle deadlocks by retrying the aborted transaction
• 🎓 Congratulations! You've completed the Node.js course — from the event loop all the way to message queues, gRPC, distributed tracing, and ACID transactions. You now have the toolkit to build, scale, and operate real production Node.js services. Go build something great.

Practice quiz

Continue this course

• Previous: Distributed Tracing
• Quick reference: Node.js cheat sheet

Frequently asked questions

What do the four ACID properties actually mean?

ACID describes the guarantees a reliable transaction gives you. Atomicity means the transaction is all-or-nothing: either every statement commits or, if anything fails, the whole thing rolls back as if it never ran. Consistency means a transaction moves the database from one valid state to another, never leaving it violating constraints like a negative balance or a broken foreign key. Isolation means concurrent transactions do not step on each other; each behaves, to the degree the isolation level promises, as if it ran alone. Durability means once you receive a successful COMMIT, the data survives even a crash or power loss because it has been written to durable storage. Together they let you treat a multi-step change as a single safe operation.

Why can't I just use pool.query for a transaction in node-postgres?

A connection pool hands out whatever connection happens to be free for each call, so two consecutive pool.query calls may run on two different physical connections. A transaction, however, lives entirely inside one connection: BEGIN, all the work, and COMMIT or ROLLBACK must happen on the same session, because the transaction state belongs to that session. If you spread them across pool.query calls, your BEGIN might run on connection 1 and your COMMIT on connection 2, which simply will not work. The fix is to check out a single client with pool.connect(), run every statement of the transaction on that client, and release it back to the pool in a finally block.

What is the try / commit / catch-rollback / finally-release pattern?

It is the canonical shape for a transaction in node-postgres. You acquire a client from the pool, then in a try block you run BEGIN, do your work, and COMMIT. If any statement throws, control jumps to the catch block where you run ROLLBACK to undo every partial change and usually rethrow the error so the caller knows it failed. The finally block always runs, whether you committed or rolled back, and there you call client.release() to return the connection to the pool. Skipping the rollback can leave a transaction open and holding locks, and skipping the release leaks connections until the pool is exhausted, so both branches matter.

What is a deadlock and how do I deal with it?

A deadlock happens when two transactions each hold a lock the other one needs, so neither can move forward: transaction one holds row A and wants row B, while transaction two holds row B and wants row A. The database detects this cycle and breaks it by aborting one of the transactions with a deadlock error, letting the other proceed. Your application should catch that error and simply retry the aborted transaction, since the conflicting one will usually have finished by then. You can also reduce deadlocks by always acquiring locks in a consistent order, keeping transactions short, and touching rows in the same sequence everywhere, so two transactions are less likely to grab the same resources in opposite orders.