ACID Properties

Objectives

By the end of this lesson, you should be able to:

• Explain what a transaction is
• Name and describe each of the four ACID properties
• Explain why a multi-step change needs a transaction at all

💡 Why this matters: Transferring money between two accounts means two separate updates, a withdrawal and a deposit. If the database crashed after the withdrawal but before the deposit, money would simply vanish. A transaction is PostgreSQL’s guarantee that this can’t happen.

⚠️ A note on verification: the concepts in this lesson are demonstrated with real, live PostgreSQL 18 behavior throughout the rest of this module.

What a Transaction Is

A transaction is a group of one or more SQL statements treated as a single unit, either every statement in the group succeeds together, or none of them take effect at all. The next three lessons cover the actual commands (BEGIN, COMMIT, ROLLBACK, SAVEPOINT), this lesson covers the guarantees a transaction provides, known as the ACID properties.

Atomicity

Atomicity means a transaction is all-or-nothing, if any part fails, the entire transaction fails, and every change made so far within it is undone, as if none of it had ever run. A withdrawal and a deposit either both happen or neither does, there’s no possibility of one succeeding and the other failing, leaving the data in a half-changed state.

Consistency

Consistency means a transaction can only take the database from one valid state to another, never violating a constraint along the way. If a transaction would leave a CHECK constraint (Module 5) violated, like an account balance going negative, PostgreSQL refuses that statement, exactly the enforcement already familiar from every constraint covered so far, transactions extend this same guarantee across multiple statements at once.

Isolation

Isolation means each transaction behaves as if it’s running alone, even when other transactions are happening at the same time. One transaction in progress doesn’t see another transaction’s uncommitted, half-finished changes, this prevents one user’s in-progress work from corrupting or confusing another’s. (The exact mechanics of isolation involve more nuance than fits here, this course’s scope is the core guarantee, not the finer isolation levels PostgreSQL supports.)

Durability

Durability means once a transaction is committed, its changes survive, even a power failure or crash immediately afterward won’t lose committed data. PostgreSQL achieves this by writing changes to disk in a way that guarantees they survive an unexpected shutdown, once a COMMIT succeeds, the data really is permanently saved.

Try It

1. Explain, in your own words, why “the withdrawal succeeded but the deposit didn’t” is exactly the failure atomicity is designed to prevent.
2. Explain, in your own words, how consistency (via a CHECK constraint) could prevent an account balance from ever going negative, even across a multi-statement transaction.
3. Describe a real scenario (outside of banking) where atomicity matters, multiple related changes that should only ever happen together.
4. Explain, in your own words, the difference between consistency (a property of the data) and isolation (a property of concurrent transactions).

Recap

• A transaction groups multiple statements into one all-or-nothing unit.
• Atomicity: all statements in a transaction succeed together, or none take effect.
• Consistency: a transaction can’t leave the database in a state that violates its constraints.
• Isolation: concurrent transactions don’t see each other’s uncommitted changes.
• Durability: once committed, changes survive even a crash immediately afterward.

Next lesson: BEGIN and COMMIT, actually starting a transaction and making its changes permanent.