Transactions and ACID

How databases make sure your data doesn't end up in a half-written, broken state.

Key Points

• Atomicity: all operations in a transaction succeed, or none of them do. No partial updates.
• Consistency: a transaction can only bring the database from one valid state to another. Constraints and rules are never broken mid-transaction.
• Isolation: concurrent transactions don't see each other's intermediate states. Each runs as if it's the only one going.
• Durability: once a transaction commits, it stays committed — even if the server crashes a second later.
• ROLLBACK undoes all changes from the current transaction. COMMIT makes them permanent.

What it is

A transaction is a group of database operations that are treated as a single unit — they all succeed together or all fail together. ACID is the set of four properties that define what a reliable transaction looks like: Atomicity, Consistency, Isolation, and Durability.

Why it exists

Without transactions, two things can go wrong. First, partial failures: you debit someone's account but crash before crediting the recipient. Second, concurrent access: two users read the same inventory count at the same time, both decrement it, and you end up overselling. Transactions prevent both.

Intuition

When to use

• Any time multiple writes need to happen together — if one fails, they all should fail.
• When concurrent access to the same data is possible and correctness matters.
• Anytime you're doing financial, inventory, or booking operations.

Common mistakes

• Keeping a transaction open too long. Long-running transactions lock rows and block other queries. Do the work fast, commit, move on.
• Swallowing exceptions and letting code continue after a failed operation inside a transaction — now the commit succeeds with partial data.

Notes

• Isolation has levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Higher isolation means more correctness but more locking and lower throughput. Most databases default to READ COMMITTED.
• NoSQL databases often sacrifice ACID (especially isolation) for scalability. This is a deliberate tradeoff, not an oversight.
• Phantom reads, dirty reads, and non-repeatable reads are specific anomalies that different isolation levels prevent — each is worth understanding separately.

In practice

If you're running data pipelines that write to a database, understanding transactions means you can make your pipeline restartable — if something fails halfway, you roll back and try again without leaving half-loaded garbage. Also relevant in data engineering when loading into OLTP systems.

Related concepts