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.

• • 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.

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.

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

Goal

Code

Check out a connection

const c = await pool.connect()

Start a transaction

c.query('BEGIN')

Save permanently

c.query('COMMIT')

Undo on error

c.query('ROLLBACK')

Set isolation

SET TRANSACTION ISOLATION LEVEL ...

Return the connection

c.release()

Frequently Asked Questions

🎉 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