Database Transactions & ACID

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.

Learn Database Transactions & ACID in our free Node.js course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick…

Part of the free Node.js course at LearnCodingFast — hands-on lessons with examples you run in your browser, 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

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.

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.

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.

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.

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.

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.

📋 Quick Reference — Transactions in pg

Practice quiz

What does the A in ACID stand for?

  • Atomicity
  • Availability
  • Authorization
  • Asynchrony

Answer: Atomicity. Atomicity means a transaction is all-or-nothing: every statement commits together or none do.

What is a deadlock?

  • A transaction that never started
  • A query that runs too slowly
  • Two transactions each holding a lock the other needs, so neither can proceed
  • A dropped database connection

Answer: Two transactions each holding a lock the other needs, so neither can proceed. A deadlock is a cycle of lock waits; the database aborts one transaction to break it.

Which SQL statement begins a transaction?

  • OPEN
  • BEGIN
  • TRANSACT
  • START

Answer: BEGIN. BEGIN (or BEGIN TRANSACTION) starts a transaction block in PostgreSQL.

Which statement permanently saves all the work in a transaction?

  • FLUSH
  • PERSIST
  • ROLLBACK
  • COMMIT

Answer: COMMIT. COMMIT makes every change in the transaction permanent and visible to others.

What does ROLLBACK do?

  • Undoes every change made since BEGIN
  • Doubles the transaction
  • Saves changes faster
  • Locks the table forever

Answer: Undoes every change made since BEGIN. ROLLBACK discards all changes made since BEGIN, returning the data to its prior state.

With node-postgres, why must a transaction use a single client from the pool, not pool.query?

  • pool.query is slower
  • Each pool.query may run on a different connection, so BEGIN, work, and COMMIT could land on different sessions
  • Transactions are not supported by pg
  • A client cannot run BEGIN

Answer: Each pool.query may run on a different connection, so BEGIN, work, and COMMIT could land on different sessions. A transaction must stay on one connection; pool.query can pick a different one per call.

In the pg transaction pattern, what does the finally block always do?

  • Runs COMMIT again
  • Opens a new transaction
  • Deletes the table
  • Calls client.release() to return the connection to the pool

Answer: Calls client.release() to return the connection to the pool. finally releases the client back to the pool whether the transaction committed or rolled back.

In the standard pattern, when do you call ROLLBACK?

  • Only at server startup
  • After every successful COMMIT
  • In the catch block, when an error occurs mid-transaction
  • Before BEGIN

Answer: In the catch block, when an error occurs mid-transaction. On error you ROLLBACK in the catch block to undo partial work, then rethrow or handle it.

What does the C (Consistency) in ACID mean?

  • Queries always run in order
  • A transaction moves the database from one valid state to another, respecting all constraints
  • Data is cached consistently
  • All servers return the same value

Answer: A transaction moves the database from one valid state to another, respecting all constraints. Consistency means committed transactions never violate constraints, keeping the data valid.

Raising the isolation level toward SERIALIZABLE primarily affects what?

  • It prevents more anomalies like phantom reads but increases contention and possible retries
  • It removes the need for BEGIN
  • Nothing measurable
  • It disables COMMIT

Answer: It prevents more anomalies like phantom reads but increases contention and possible retries. Higher isolation blocks more anomalies but costs concurrency and may force serialization retries.