PostgreSQL ACID Transactions

Picture this: you’re migrating a production database, and halfway through the process, the server loses power. When it comes back online, you find that only half of your rows made it — some tables updated, others didn’t, and now your application is serving inconsistent data to angry users. Without a way to group operations into a single, all-or-nothing unit, your database is a book with torn-out pages. This is the pain that PostgreSQL’s transaction system and the ACID properties exist to solve. By the end of this lesson, you’ll not only understand what ACID means but also how to wield BEGIN, COMMIT, and ROLLBACK to protect your data like a pro.

The problem this lesson solves

When you run multiple SQL statements that depend on each other, you face a daunting question: what happens if one fails? Consider a classic bank transfer: you debit one account and credit another. If the debit succeeds but the credit fails, money vanishes into thin air. Without transactions, your database can end up in a state that’s logically impossible, yet physically stored.

This isn’t just a theoretical concern. In real-world applications, concurrent users are constantly reading and writing the same tables. A report running at 2:00 AM might see a half-applied batch update if there’s no isolation. Data corruption, lost updates, and phantom reads are not bugs in your SQL — they’re symptoms of missing transaction boundaries.

Understanding transactions and ACID properties gives you the power to define these boundaries. You can group statements, ensure they all succeed or none do, and control how other sessions see your changes. This lesson is your first line of defense against data loss and inconsistency.

Core concept / mental model

Think of a transaction as a single atomic operation — like putting a letter in an envelope. You write the letter, fold it, seal it, and drop it in the mailbox. Until you do, nothing is sent. In PostgreSQL, a transaction is a sequence of SQL statements that the database treats as one unit. The moment you issue BEGIN, you’re opening the envelope. COMMIT seals and sends it. ROLLBACK tears it up.

The ACID acronym breaks this down into four promises:

• Atomicity — the transaction is all-or-nothing. If any statement fails, the entire transaction is rolled back, leaving no partial effects.
• Consistency — the transaction brings the database from one valid state to another, preserving all constraints, triggers, and rules.
• Isolation — concurrent transactions are invisible to each other until they commit. Your transaction sees a consistent snapshot, as if it were the only one running.
• Durability — once committed, changes survive system crashes. If the power dies after COMMIT, the data is still there when the server restarts.

Pro tip: Atomicity and durability are the twin pillars of data safety. Atomicity prevents partial writes; durability prevents lost writes. Together, they ensure your data survives both application errors and hardware failures.

A helpful diagram-in-words: imagine a transaction as a train on a track. The train can either reach the end of the line (commit) or derail and be scrapped (rollback). There’s no in-between stop where the train can exist halfway off the rails.

How it works step by step

1. Start a transaction with BEGIN; — this tells PostgreSQL to start a new transaction block. Until you call COMMIT or ROLLBACK, your changes are private to your session.
2. Execute your statements — you run your INSERT, UPDATE, DELETE, or any other DML. These changes are visible to your session but not to other connections.
3. Check for errors — if any statement throws an error (e.g., a constraint violation), PostgreSQL marks the transaction as aborted. You can either ROLLBACK entirely or use savepoints to partially roll back.
4. Commit or rollback — COMMIT makes all changes permanent and visible to other sessions. ROLLBACK discards all changes, leaving the database as if the transaction never started.

Here’s the critical nuance: PostgreSQL’s default isolation level is Read Committed. In this mode, each statement sees a fresh snapshot of committed data. That means other sessions’ committed changes appear immediately after your COMMIT, not during your transaction. For stricter isolation, you can use REPEATABLE READ or SERIALIZABLE, but the default is often sufficient.

The actual mechanics involve PostgreSQL’s multiversion concurrency control (MVCC). Instead of locking rows, PostgreSQL keeps multiple versions of a row. Your transaction sees only the versions that were committed before your snapshot started. This gives you high concurrency without blocking reads, but it also means you must be careful with long-running transactions — they can bloat the table with old versions.

Hands-on walkthrough

Let’s put theory into practice. Fire up psql and create a simple ledgers table to simulate a bank transfer.

-- Create a table for accounts
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    balance NUMERIC NOT NULL CHECK (balance >= 0)
);

-- Insert some initial data
INSERT INTO accounts (id, name, balance) VALUES
    (1, 'Alice', 1000.00),
    (2, 'Bob', 1000.00);

Now, perform a transaction that transfers $200 from Alice to Bob.

BEGIN;

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

COMMIT;

-- Verify the result
SELECT * FROM accounts;

 id | name  | balance
----+-------+---------
  1 | Alice |  800.00
  2 | Bob   | 1200.00
(2 rows)

Both updates succeeded, and the data is consistent. Now let’s simulate a failure. Try to transfer $5000 from Alice, which should violate the CHECK constraint.

BEGIN;

UPDATE accounts SET balance = balance - 5000 WHERE id = 1;  -- This fails!
ROLLBACK;

-- Check the table
SELECT * FROM accounts;

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Alice, -4000).

 id | name  | balance
----+-------+---------
  1 | Alice |  800.00
  2 | Bob   | 1200.00
(2 rows)

The transaction was rolled back, and Alice’s balance remained at 800. Nothing was lost.

Now let’s see isolation in action. Open two psql sessions. In session 1, start a transaction and update Alice’s balance without committing. In session 2, try to read Alice’s balance.

Session 1:

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Do not commit yet

Session 2:

SELECT balance FROM accounts WHERE id = 1;

 balance
---------
    800.00
(1 row)

Session 2 sees the old value, because the change is uncommitted. Now commit in session 1 and re-run the query in session 2.

Session 1:

COMMIT;

Session 2:

SELECT balance FROM accounts WHERE id = 1;

 balance
---------
    900.00
(1 row)

After the commit, the change becomes visible. This is the essence of isolation — uncommitted changes are invisible to other sessions.

Compare options / when to choose what

PostgreSQL offers several isolation levels, each with its own trade-offs. The ANSI SQL standard defines four, but PostgreSQL implements three (Read Uncommitted is mapped to Read Committed). Here’s a comparison:

When to choose what: - Start with Read Committed for most applications — it’s fast and prevents dirty reads. - Switch to Repeatable Read if you’re running a multi-statement report and need the same data snapshot each query. - Use Serializable only when you have complex business logic that could suffer from phantom rows (e.g., reservation systems). Be aware it has a performance cost.

For most developers, understanding the default isolation level is enough. You can set it per transaction with SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; after BEGIN.

Troubleshooting & edge cases

Here are common pitfalls and how to handle them:

• ERROR: current transaction is aborted, commands ignored until end of transaction block — This happens when you hit a runtime error inside a transaction (e.g., constraint violation). PostgreSQL poisons the entire transaction; you must ROLLBACK before issuing new statements. Use savepoints to recover from specific errors without aborting everything.
• Deadlocks — When two transactions wait on each other’s locks, PostgreSQL detects it and aborts one with ERROR: deadlock detected. Retry the failed transaction under application control.
• Long-running transactions — Keeping a transaction open for minutes or hours causes table bloat and stale snapshots. Keep transactions short and commit promptly.
• Forgetting to commit — If you don’t issue COMMIT, your transaction stays open. Always ensure your application explicitly commits or rolls back, ideally with context managers.

Here’s a savepoint example to recover from a partial failure:

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;  -- This might fail
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;  -- This also fails
ROLLBACK TO my_savepoint;
COMMIT;

If both updates fail, you can roll back to the savepoint and recover gracefully, though in practice you wouldn’t intentionally fail both.

What you learned & what's next

You’ve now grasped the core idea behind transactions and ACID properties. You can explain how atomicity ensures all-or-nothing execution, how consistency preserves data validity, how isolation shields concurrent sessions, and how durability survives crashes. You completed a hands-on exercise with BEGIN, COMMIT, ROLLBACK, and you saw isolation in action. You also learned how to choose isolation levels and handle common errors.

This is foundational to every other PostgreSQL feature. Next up in the track, you’ll dive into locking and concurrency control, where you’ll learn how to manage locks explicitly to prevent conflicts in high-traffic applications. With transactions under your belt, you’ll be ready to build multi-user systems that stay consistent under load.