Transactions and ACID in Practice: What Every Backend Developer Must Know

The money left Alice's account. Bob never received it. The server crashed in between. If you don't understand transactions, this is how your application loses data, silently, permanently, with no error log.

Transactions and ACID in Practice: What Every Backend Developer Must Know

Here is the scenario that every backend developer eventually hits in production. A bank transfer: debit $100 from Alice, credit $100 to Bob. Two SQL statements. Simple.

The server crashes between them.

Alice has lost $100. Bob never received it. The money has vanished into the gap between two database writes, and your application has no idea it happened.

This is exactly the problem transactions were invented to solve. And yet, after years of reviewing code and debugging production incidents, I can tell you: most developers use transactions far less than they should, misunderstand what isolation levels actually do, and write patterns that hold locks for seconds longer than necessary.

This article covers the full mental model, BEGIN, COMMIT, ROLLBACK, all four isolation levels, SELECT FOR UPDATE, SKIP LOCKED for job queues, and the most common anti-patterns that silently destroy performance at scale. It's based directly on the Transactions & ACID in Practice module from the PostgreSQL In-Depth course.

The Problem Transactions Solve

Consider the transfer code without transactions:

UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2;

If anything interrupts execution between those two lines, a server crash, an application exception, a network timeout, Alice's account has been debited but Bob's has not been credited. The database is now in an inconsistent state, and it has no knowledge that anything went wrong.

Transactions fix this by grouping operations into an atomic unit: either all of them succeed, or none of them do.

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

If the server crashes after the first UPDATE and before COMMIT, PostgreSQL automatically rolls back the entire transaction on restart. Alice keeps her $100. The database never reaches a half-updated state.

What ACID Actually Means

ACID is not a marketing acronym. Each letter describes a specific guarantee the database makes, and understanding each one changes how you write application code.

Atomicity means all operations in a transaction succeed or none do. There is no partial success. If your transaction debits Alice and the next statement fails, the debit is undone.

Consistency means a transaction takes the database from one valid state to another. Constraints, NOT NULL, FOREIGN KEY, CHECK, are enforced at commit time. If you have a CHECK (balance >= 0) constraint and a debit would push a balance negative, the entire transaction fails at COMMIT and rolls back automatically. The database enforces your business rules, not just your application code.

Isolation means concurrent transactions run as if they are the only transaction in the system. Another session's uncommitted changes are invisible to you. If Session A is updating a row and hasn't committed yet, Session B reads the pre-update value, not the in-progress value.

Durability means once committed, data survives crashes. PostgreSQL's Write-Ahead Log (WAL) ensures this, every committed transaction is written to durable storage before the COMMIT acknowledgement is returned to the client.

The Three Transaction Commands

BEGIN starts a transaction. Without it, every statement is its own transaction, it auto-commits immediately. This is fine for single statements, dangerous for multi-step operations.

COMMIT makes all changes permanent and releases locks.

ROLLBACK undoes all changes since BEGIN and releases locks. If your session disconnects before COMMIT, PostgreSQL automatically rolls back.

In application code (Node.js example):

javascript

The finally block is critical, always release the connection back to the pool, whether the transaction succeeded or failed.

One thing many developers don't realise: once an error occurs inside a transaction in PostgreSQL, the transaction is aborted. Every subsequent statement in that transaction will fail with "current transaction is aborted, commands ignored until end of transaction block", until you issue a ROLLBACK. This has caught many teams off guard in production.

The Four Isolation Levels

PostgreSQL supports four isolation levels. Understanding the difference is not academic, choosing the wrong one causes data integrity bugs that are extremely hard to debug.

READ COMMITTED (the default), each query in your transaction sees a fresh snapshot of committed data at the time that query runs. This means two identical SELECT queries in the same transaction can return different results if another transaction commits between them. This is called a non-repeatable read, and it is expected behaviour at this level. For most application work, READ COMMITTED is correct.

REPEATABLE READ, the entire transaction sees the same snapshot of data as of when the transaction started. If another session inserts 100 rows and commits while your transaction is open, your subsequent queries still see the original row count. Use this when you need consistency across multiple queries in a single transaction, for example, generating a financial report where all queries must reflect the same point in time.

SERIALIZABLE, the strongest level. Transactions execute as if they were serialised one after another. PostgreSQL detects situations where concurrent transactions could produce results different from any serial execution and fails one of them with a serialization error. Use this sparingly, it adds overhead and requires retry logic in your application.

READ UNCOMMITTED, in theory this allows dirty reads (reading uncommitted data from other transactions). In practice, PostgreSQL treats it identically to READ COMMITTED. Postgres never allows dirty reads, regardless of isolation level.

The rule for most applications: use READ COMMITTED for everything. Move to REPEATABLE READ only when you genuinely need a consistent multi-query snapshot. Use SERIALIZABLE only for complex financial operations where you've reasoned through why lower isolation levels produce incorrect results.

SELECT FOR UPDATE: Pessimistic Locking

Here is a classic race condition. Two workers both check an account balance, both decide it's sufficient, and both proceed to debit, leaving the balance negative despite a check constraint.

The naïve code:

SELECT balance FROM accounts WHERE id = 1; -- application checks: if balance >= 100, proceed UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Between the SELECT and the UPDATE, another transaction can change the balance. Your check was valid when you made it and invalid when the UPDATE runs.

The fix is SELECT FOR UPDATE, which locks the row at read time:

BEGIN; SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- Row is now locked. No other transaction can modify it until we commit. -- Application checks balance, decides to proceed UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;

Any other transaction trying to SELECT FOR UPDATE or UPDATE the same row will wait until you commit or rollback. The row is protected for the entire check-then-modify sequence.

FOR UPDATE SKIP LOCKED: The Job Queue Pattern

FOR UPDATE SKIP LOCKED is one of the most useful concurrency primitives PostgreSQL offers, and it is almost unknown outside of experienced backend teams.

The scenario: a table of background jobs, multiple workers running concurrently, each trying to claim and process the next available job. Using plain FOR UPDATE causes workers to queue up waiting for the same row lock, defeating the purpose of concurrency. Using no locking at all causes multiple workers to claim the same job.

SKIP LOCKED solves this exactly:

BEGIN; SELECT id, payload FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED;

UPDATE jobs SET status = 'processing', worker_id = $worker WHERE id = $id; COMMIT;

Each worker instantly skips rows that are already locked by another worker and claims the next available one. No blocking, no duplicate processing. This is the correct pattern for any queue-based workload in PostgreSQL.

The Most Expensive Mistake: Long Transactions

This is the pattern I see most often in production codebases, and it causes damage that compounds over time.

javascript

What's happening during those 2-5 seconds:

• Locks are held on every row the transaction has touched.
• Other transactions trying to modify those rows are blocked.
• PostgreSQL cannot vacuum dead tuples from any table involved in the transaction, across the entire database, not just the rows you locked.
• Connection pool slots are occupied for the full duration.

At low traffic, this is invisible. At 100 concurrent requests with 3-second payment API calls, you have 100 transactions holding locks simultaneously. Autovacuum falls behind. Tables bloat. Queries slow down. The cause is nearly impossible to identify without knowing what to look for.

The correct pattern:

javascript

Transaction open for milliseconds, not seconds. Lock held for milliseconds. Autovacuum unaffected.

SAVEPOINT: Partial Rollback

A SAVEPOINT lets you roll back part of a transaction without abandoning the whole thing. This is useful when you have a series of operations where one failing step should be retried or skipped, not the entire transaction.

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 150.00);

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 999, 1); -- Fails: product 999 doesn't exist

ROLLBACK TO SAVEPOINT before_items; -- The order INSERT is preserved. The failed order_item INSERT is undone.

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 1); -- Correct product this time

COMMIT; -- Order + 1 item committed. The failed attempt left no trace.

Where This Fits in the Full Course

The patterns above, along with isolation level trade-offs, deadlock prevention, and the WAL durability mechanics behind durability, are one module of the PostgreSQL In-Depth course at academy.jatinjainsaraf.com.

The course covers three phases: Foundation (absolute basics for beginners, zero assumed knowledge), Practitioner (the patterns working engineers use every week, indexes, schema design, JSONB, full-text search, performance tuning), and Architect (MVCC internals, WAL, autovacuum mechanics, replication, partitioning, and connection pooling failure modes).

Every module is built from years of running PostgreSQL at 20–30 TB scale on a live blockchain indexer. The examples in this article are from real production scenarios, not documentation.

The Questions Worth Asking About Your Own Codebase

Do any of your API endpoints hold a database transaction open while making an external HTTP call? Even one that takes 200ms?

Do your background job workers use FOR UPDATE SKIP LOCKED, or are they competing for the same row lock?

What happens in your application when a transaction fails mid-way? Does your error handling issue ROLLBACK and release the connection, or does it leave an aborted transaction sitting in the pool?

Do you know which isolation level your most critical queries are running at, and whether that level is actually appropriate for what those queries are doing?

These are not theoretical questions. Each one has a production failure mode attached to it. The good news is that once you have the mental model, the fixes are straightforward.

#PostgreSQL #Database #Backend #SQL #SoftwareEngineering #DatabaseEngineering #Transactions #ACID #NodeJS #BackendDevelopment