Introduction

When a backend application serves many users at once, the database becomes the central battleground for consistency. Two users booking the last seat, a payment confirmation racing with a refund, a report reading data while an update is in progress — every one of these scenarios touches PostgreSQL's transaction and concurrency machinery. Getting it wrong leads to silent data corruption, duplicated orders, or impossible balances.

This lesson focuses on what a backend developer must know to reason about concurrent behavior: how PostgreSQL wraps work in transactions, how isolation levels change visibility between concurrent transactions, how MVCC lets readers and writers not block each other, and where row-level locks actually engage. The goal is not database administration depth, but the practical mental model needed when a production bug report says "two requests did the same thing" or "the dashboard showed stale numbers".

ACID and Transaction Boundaries

A transaction in PostgreSQL is a unit of work that either commits fully or rolls back fully. The four guarantees — Atomicity, Consistency, Isolation, Durability — describe what the database promises if you wrap your statements in BEGIN and COMMIT.

In a backend, the typical shape looks like this:

BEGIN;

UPDATE accounts
   SET balance = balance - 100
 WHERE id = 42;

UPDATE accounts
   SET balance = balance + 100
 WHERE id = 99;

COMMIT;

If the second UPDATE fails, the first is rolled back too. There is never a state where the money has been debited but not credited. Atomicity here is not a nice-to-have — it is what keeps the books balanced.

A practical point for backend developers: PostgreSQL treats every single statement as its own implicit transaction unless you explicitly BEGIN. So if you issue two separate UPDATE statements without a transaction block, each one commits independently. This is the root cause of many "partially applied" bugs in application code that talks to the database through a connection pooler or an ORM with the default autocommit setting.

Isolation Levels in PostgreSQL

SQL defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. PostgreSQL implements three of them as actual distinct behaviors — READ UNCOMMITTED behaves the same as READ COMMITTED because of how MVCC works under the hood.

The level you pick decides what a transaction is allowed to see from concurrent transactions. READ COMMITTED is the default and is usually what you want: each statement sees data committed before that statement started. If another transaction commits an update between your two SELECTs, the second SELECT will see the new value — which can surprise people expecting a stable view.

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 42;
-- Run some logic...

SELECT balance FROM accounts WHERE id = 42;
-- Always returns the same value as the first SELECT,
-- regardless of concurrent commits.

COMMIT;

REPEATABLE READ locks the snapshot to the moment the transaction starts, not each statement. SERIALIZABLE goes one step further: it detects serializability violations and aborts one of the conflicting transactions with a serialization failure error that the application must be prepared to retry.

MVCC and Row-Level Locks

PostgreSQL uses Multi-Version Concurrency Control. When you UPDATE a row, the database does not overwrite the old version in place. It writes a new row version and marks the old one as dead. Readers see whichever version was committed at the time their snapshot was taken. This is why a long-running report query does not block incoming writes, and vice versa — they operate on different row versions.

But MVCC does not eliminate all blocking. When two transactions try to UPDATE the same row, PostgreSQL needs a winner, and the loser waits on a row-level lock until the first transaction commits or rolls back. SELECT FOR UPDATE is the explicit way to claim that lock before doing business logic that depends on the current value.

BEGIN;

SELECT stock FROM products WHERE id = 7 FOR UPDATE;
-- stock is now locked. Another transaction trying to
-- UPDATE or SELECT FOR UPDATE on this row will wait.

UPDATE products
   SET stock = stock - 1
 WHERE id = 7;

COMMIT;
-- Now the other transaction can proceed and see stock = stock - 1.

The pattern of SELECT FOR UPDATE followed by a conditional UPDATE is the standard fix for the classic race condition where two requests each read the value, both decide it is safe, and both decrement it. Without the explicit lock, both transactions read the same original stock count and you oversell.

Common Pitfalls and Practical Takeaways

Three failure modes show up constantly in backend code. First, long-running transactions hold snapshots and prevent tuple cleanup, so the table bloats and performance degrades over time — close transactions promptly, do not open one at the start of an HTTP request and leave it open across downstream calls. Second, deadlocks happen when two transactions acquire locks in opposite orders; keep the order consistent across the codebase, or rely on PostgreSQL to detect and abort one of them and retry. Third, ignoring serialization failures under SERIALIZABLE leads to data corruption — the database is telling you the operations were not actually safe to run in parallel, and your retry logic is part of the contract, not optional.

The practical mindset: treat isolation level and lock choice as part of your application design, not a database tuning detail. Default READ COMMITTED with explicit SELECT FOR UPDATE around critical sections solves most concurrency bugs cleanly. Reach for SERIALIZABLE when correctness matters more than throughput and you can handle the failures.

Summary

Transactions bundle multiple statements into an atomic unit. Isolation levels control what concurrent transactions can see of each other's uncommitted work. MVCC gives PostgreSQL non-blocking reads and writes by keeping multiple row versions, while row-level locks resolve conflicts when two transactions target the same row. For backend developers, the concrete tools are BEGIN/COMMIT discipline, the SELECT FOR UPDATE pattern for race-condition-prone updates, and consistent lock ordering to avoid deadlocks. Pick the isolation level deliberately — the default works for most cases, but REPEATABLE READ and SERIALIZABLE exist for situations where the default's behavior is not strict enough.