Premium Video

This video is available to premium subscribers only

Unlock Full Access

A transaction is a group of database operations that are treated as a single unit of work. ACID stands for Atomicity, Consistency, Isolation, and Durability, the four key properties of database transactions.

In this chapter, we will learn what database transactions are, what each letter in ACID means, how databases provide these guarantees, and the trade-offs that come with them.

Suppose we're building a banking application, and Alice wants to transfer $100 to Bob.

To complete this transfer, the application needs to make two changes:

Now imagine the server crashes after deducting the money from Alice's account, but before adding it to Bob's. Alice has lost $100, but Bob never received it. The money has simply disappeared.

There's another problem we need to consider. Suppose Alice has $100 in her account, and two transfer requests arrive at the same time. Both requests check her balance, see that she has enough money, and proceed with the transfer. As a result, Alice ends up sending $200, even though she only had $100.

To prevent problems like these, we need a way to:

That's exactly what database transactions are designed to do.

A transaction is a group of database operations that are treated as a single unit of work.

The application starts a transaction, performs one or more reads and writes, and then commits the transaction. Committing means making all the changes permanent.

If something goes wrong before the transaction is committed, the database can roll back those changes instead. A rollback undoes the changes made by the transaction.

In SQL, we typically start with a BEGIN statement, perform the required operations, and then either COMMIT or ROLLBACK. In our banking example, both balance updates would be part of the same transaction:

So what guarantees does a transaction provide? They're usually described with the acronym ACID:

Let's look at each one in detail.

Atomicity means a transaction is all or nothing. Either all the changes in a transaction are applied, or none of them are.

Let's go back to our banking example, where the server crashes in the middle of a transfer.

With atomicity, the database won't leave the transaction partially completed. If the transaction hasn't committed, the money deducted from Alice's account is rolled back. So after recovery, both accounts have the same balances they had before the transfer started. The application can then safely retry the transaction.

Without atomicity, the application would have to keep track of every partial change and write its own cleanup code for each failure case, which makes the system much harder to build and maintain.

Consistency means a transaction takes the database from one valid state to another. What counts as valid depends on the rules our data needs to follow.

Some of these rules are enforced directly by the database. For example, an account balance might have a constraint that prevents it from becoming negative, or an order might need a foreign key that ensures it belongs to an existing customer.

If a transaction violates one of these constraints, the database rejects the change and rolls the transaction back.

But not every rule can be enforced by the database. Some rules are the application's responsibility. For example, the total amount of money across all accounts should remain the same during a transfer. The database doesn't automatically know this rule, so the application must make sure its transactions preserve it.

In other words, consistency depends on both the database and the application.

There's one important distinction to make here. Consistency in ACID is different from consistency in the CAP theorem.

Isolation deals with transactions that run at the same time. A busy database might be handling thousands of transactions concurrently, so we need to make sure they don't interfere with each other.

Ideally, each transaction should behave as if it were running alone, without seeing the unfinished changes made by other transactions.

Let's go back to our banking example, where two transfer requests arrive at the same time. With the right isolation guarantees, the database prevents both transfers from spending the same money. The first transfer succeeds and reduces Alice's balance to zero. The second transfer then sees that there isn't enough money and fails.

The strongest isolation level is called Serializable. It guarantees that the final result is the same as if the transactions had executed one after another, in some order.

However, this level of isolation can be expensive in terms of performance. That's why databases also offer weaker isolation levels, which improve concurrency but allow certain problems to occur.

To understand these trade-offs, let's first look at the problems that can happen when transactions aren't properly isolated.

When transactions aren't properly isolated, several problems can occur.

Dirty Read

A dirty read happens when one transaction reads changes made by another transaction that hasn't committed yet.

For example, suppose transaction A updates Alice's balance to zero, and transaction B reads that balance. But then transaction A rolls back. Transaction B has now made a decision based on a value that was never actually committed.

Non-Repeatable Read

A non-repeatable read happens when a transaction reads the same row twice but gets different values, because another transaction updated and committed that row in between.

Phantom Read

Suppose a transaction runs the same query twice, such as all orders above $500:

The second time, a new order appears, because another transaction inserted a matching row. That new row is a phantom.

Lost Update

A lost update happens when two transactions read the same value, both make changes, and then write their results back. The second write ends up overwriting the first, causing one of the updates to be lost.

This is similar to what we saw earlier with Alice's two concurrent transfers.

The SQL standard defines four isolation levels, each offering different protection against the problems we just discussed.

However, stronger isolation comes with a performance cost. Transactions may need to wait longer or get aborted and retried more often, which can reduce throughput.

One important thing to remember is that databases don't always implement these levels exactly as the SQL standard defines them. For example, PostgreSQL's Repeatable Read also prevents phantom reads. So it's always worth checking how your database implements each isolation level before choosing one.

Databases mainly use two techniques to provide isolation.

Locking

When a transaction needs to update a row, it acquires a lock on that row. If another transaction tries to update the same row, it has to wait until the lock is released.

This approach is straightforward, but under heavy load, transactions can spend a lot of time waiting for locks.

Locking can also lead to deadlocks. For example, suppose transaction A holds a lock on row 1 and is waiting for row 2. At the same time, transaction B holds a lock on row 2 and is waiting for row 1. Neither transaction can move forward.

To resolve this, the database detects the deadlock and aborts one of the transactions, allowing the other to continue.

Multi-Version Concurrency Control (MVCC)

Instead of overwriting a row in place, the database maintains multiple versions of that row. This allows transactions to read from a consistent snapshot of the data without being blocked by concurrent updates.

As a result, readers generally don't block writers, and writers don't block readers.

Databases like PostgreSQL and MySQL's InnoDB use MVCC extensively, along with locks to handle conflicting writes.

The last property of ACID is durability. Durability means that once a transaction is committed, its changes are permanent, even if the database crashes or loses power immediately afterward.

Let's go back to our banking example. Suppose Alice's transfer is committed, and the application shows her a message saying the transfer was successful. But just a second later, the database server loses power. When the server restarts, the transfer must still be there.

To guarantee this, the database needs to save enough information to durable storage, such as a disk, before confirming the commit. Keeping the changes only in memory isn't enough, because that data would be lost if the server crashes.

Some databases allow you to relax durability guarantees to make commits faster. However, this comes with a risk. If the server crashes, some recently committed transactions may be lost, even though the application was already told they were successful.

So how do databases guarantee atomicity and durability without writing every modified data page to disk whenever a transaction commits?

Most databases use a technique called Write-Ahead Logging, or WAL.

Before making changes to the actual data files, the database first records those changes in a log. Writing to a log is generally faster, because it involves sequential writes rather than updating different parts of the database.

When a transaction commits, the database makes sure the necessary log records are saved to disk. The actual data pages can be written later in the background.

Now suppose the database crashes before those pages are updated. When it restarts, it uses the log to recover:

So with a single mechanism, Write-Ahead Logging helps databases provide two of the four ACID properties.

These guarantees come with a cost.

When many transactions try to update the same rows, such as the inventory count of a popular product, this contention can become a serious bottleneck.

There are a few ways to reduce this overhead.

For example, rather than reading an account balance and then writing back the updated value, we can use a single UPDATE statement that deducts $100 only if the balance is at least $100:

The database can execute this safely without requiring the application to manage a larger transaction. If the statement updates zero rows, there wasn't enough money.

ACID guarantees are easier to provide when all the data is stored in a single database. Things become more complicated when data is spread across multiple machines.

For example, suppose Alice's account is stored on one database shard and Bob's account is stored on another. Now a single transfer needs to update two separate databases. Each database can commit its own changes atomically, but how do we make sure both updates succeed or both are rolled back?

Two-Phase Commit

One approach is Two-Phase Commit. In this protocol, a coordinator first asks each participating database whether it's ready to commit. If everyone agrees, the coordinator tells them to commit. If any participant says no, it tells all of them to abort.

This provides atomicity across multiple databases, but it adds latency and can leave participants waiting if the coordinator fails.

Saga Pattern

In microservice architectures, another common approach is the Saga pattern. Instead of one large transaction, a saga breaks the operation into smaller, local transactions. If a later step fails, the system runs compensating actions to reverse the effects of earlier steps.

For the transfer, the saga might first debit Alice in one local transaction, then credit Bob in another. If crediting Bob fails, a compensating transaction refunds Alice.

This avoids holding locks across services, but it doesn't provide the same isolation guarantees. Other transactions may see intermediate changes before the entire operation is complete.

You may also come across the term BASE, which is often associated with NoSQL databases. It stands for Basically Available, Soft State, and Eventual Consistency.

The idea is that a system should stay available and scale, even when different replicas don't immediately agree on the latest data. Updates propagate over time, so the replicas eventually converge to the same state.

This approach works well for things like view counts, where showing a slightly outdated number isn't a big problem. But for bank balances or inventory, we need stronger guarantees.

However, the line between ACID and BASE isn't as clear as it might seem. Many NoSQL databases now support transactions, while distributed SQL databases like Google Spanner and CockroachDB provide ACID transactions across multiple machines.

So instead of focusing on SQL versus NoSQL, the more important question is what guarantees your data actually needs.

ACID transactions are especially important when incomplete or conflicting updates could cause serious problems.

Payments, bank transfers, and account balances are the most common examples, where even a small inconsistency can cause real harm.

The same applies to inventory management, order processing, and seat reservations. We need to make sure two customers can't purchase the last available item or book the same seat.

On the other hand, data like analytics events, application logs, and activity counters can often tolerate weaker guarantees without causing major problems.

In practice, a good approach is to keep critical data in a database with strong transactional support, and use weaker guarantees only where some inconsistency is acceptable. The key is to understand the trade-offs and choose the right level of consistency for each part of your system.

ACID transactions provide four important guarantees:

Together, these guarantees help us build reliable applications without having to handle every possible failure and concurrency issue ourselves.