The previous chapter showed how BEGIN, COMMIT, and ROLLBACK group SQL statements into a single unit of work. But what exactly does the database guarantee about that unit?

ACID is the answer: four properties that define what makes a transaction reliable. They are Atomicity, Consistency, Isolation, and Durability.

Insert these rows to follow along:

Atomicity means all-or-nothing. Every statement in a transaction either succeeds together, or the entire set of changes is discarded. The database never leaves behind a half-finished transaction.

Consider a StreamFlow operation where Frank (user_id 6) cancels his premium subscription and requests a refund. This involves three changes:

If the third UPDATE fails (say, a trigger rejects the refund because the payment is too old), atomicity guarantees that the first two changes are also undone. Frank keeps his premium access and no partial downgrade occurs.

Without atomicity, the first two UPDATEs would already be committed. Frank would lose premium access but never receive the refund, and the data across three tables would be inconsistent.

How the Database Delivers Atomicity

When a transaction modifies a row, the database does not simply overwrite the original data. It first saves the original value in an undo log (also called a rollback segment). If the transaction needs to roll back, whether due to an explicit ROLLBACK, a constraint violation, or a crash, the database replays the undo log entries in reverse order, restoring each modified row to its previous state.

This is why ROLLBACK is not just "stop executing." It actively reverses changes by reading the undo log and writing the old values back. The following diagram shows the two paths:

On the success path, the undo log entries are eventually discarded (they are no longer needed). On the failure path, they are the mechanism that makes the all-or-nothing guarantee possible.

Consistency means a transaction moves the database from one valid state to another valid state. "Valid" here means every constraint declared in the schema is satisfied: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints.

A quick clarification: ACID consistency is not the same thing as consistency in the CAP theorem. ACID consistency is about data validity through constraints. CAP consistency is about whether all nodes in a distributed system return the same data. They share a name but address different problems.

Constraint Enforcement in Practice

Suppose someone tries to record a payment for a user that does not exist:

The FOREIGN KEY on payments.user_id references users.user_id. No user with ID 999 exists, so the INSERT is rejected. In PostgreSQL, once a statement errors inside a transaction, the transaction is in an aborted state and must be rolled back. The subscription update is undone along with the failed insert.

Similarly, if you add a CHECK constraint:

Any transaction that tries to insert or update a payment with a zero or negative amount will be rejected, regardless of how many other valid statements are in the transaction.

Beyond SQL Constraints

Database constraints enforce structural rules: referential integrity, uniqueness, data types. But some business rules go beyond what SQL constraints can express. For example, "a user's total refunds should never exceed their total payments" is a rule that requires comparing aggregated values across rows. The database won't enforce this automatically. You need application logic, triggers, or stored procedures to maintain these kinds of invariants. Consistency in the ACID sense only covers what the schema declares.

Isolation means concurrent transactions do not interfere with each other. Each transaction behaves as though it has the database to itself, even when dozens of other transactions are running at the same time.

Here is why this matters. Imagine two sessions running simultaneously:

Without isolation, Session B might read Emma's tier as 'premium' before Session A has committed. If Session A later rolls back (maybe the payment processing failed), Session B made a decision based on data that never actually existed. Emma skipped ads she should have seen.

Isolation prevents this by controlling what uncommitted changes are visible to other transactions. The exact degree of protection depends on the isolation level configured for the database or the individual transaction.

With proper isolation, Session B sees 'free' because Session A's update has not been committed. The uncommitted change is invisible to other sessions.

Databases implement isolation through two main mechanisms: locks (preventing concurrent access to the same rows) and MVCC (Multi-Version Concurrency Control, where each transaction sees a snapshot of the data). Most modern databases, including PostgreSQL and MySQL's InnoDB engine, use MVCC as the primary mechanism.

The next chapter covers the four standard isolation levels and the specific anomalies each one prevents.

Durability means that once COMMIT returns successfully, the changes are permanent. They survive server crashes, power failures, and restarts. The database will not lose committed data because a process died or the machine rebooted.

Write-Ahead Logging (WAL)

The mechanism behind durability is the write-ahead log (WAL), sometimes called the redo log in MySQL. The core idea is simple: before the database modifies any actual data pages in memory or on disk, it first writes a record of the change to a sequential log file on disk.

When you issue COMMIT, the database flushes the WAL entries for your transaction to disk and then acknowledges the commit back to your application. At this point, even if the server crashes before the modified data pages are written to their final location, the WAL contains everything needed to reconstruct the changes.

On restart after a crash, the database reads the WAL and replays any committed transactions whose data pages were not yet written. This process is called crash recovery or WAL replay.

The WAL also plays a role in atomicity. During crash recovery, the database identifies transactions that were in progress but never committed. It uses the WAL (along with the undo log) to roll those incomplete transactions back, ensuring that uncommitted changes do not persist after a restart.

What Durability Does Not Cover

Durability protects against process crashes and server restarts. It does not protect against disk failure, data center outages, or accidental DELETE statements. For those, you need replication (copies of data on other machines) and backups. These are infrastructure concerns outside the scope of SQL transactions, but worth keeping in mind when someone claims a system is "durable."

The four ACID properties are not independent features. They work together during every transaction:

Full ACID compliance has a cost. Synchronous disk writes for durability, lock contention or snapshot overhead for isolation, and constraint checking for consistency all add latency. Some database systems intentionally relax one or more ACID properties to gain throughput, lower latency, or easier horizontal scaling.

A few things stand out in this table. MongoDB added multi-document transactions in version 4.0, but its schema-flexible design means consistency constraints (like foreign keys) are the application's responsibility, not the database's. Cassandra provides atomicity only at the row level. Writing to multiple rows atomically requires lightweight transactions, which use consensus protocols and are significantly slower. DynamoDB added a transactions API, but its default mode is single-item operations.

The key takeaway: "NoSQL" does not mean "no ACID." Many NoSQL systems have added transaction support over time. The important question is what guarantees the system provides by default, and what trade-offs kick in when you enable stronger guarantees.