What Are ACID Transactions?

Linh sends Marco $100 in a banking app. To Linh it is one tap. To the database it is two writes: take $100 from Linh, then add $100 to Marco. If the server dies between those two writes, $100 disappears. ACID is the name for four promises a database makes so that this cannot happen: Atomicity, Consistency, Isolation and Durability. A group of operations that gets all four promises is called a transaction.

In short

A transaction groups several reads and writes into one unit of work.
Atomicity: all of it happens, or none of it does.
Consistency: every commit obeys the rules you declared, such as “a balance can’t go below 0”.
Isolation: transactions that run at the same time don’t trip over each other’s half-done work.
Durability: once the database says “committed”, a crash cannot undo it.

What is a transaction?

Here is the transfer in SQL. Linh starts with $200 and Marco with $50. BEGIN opens a transaction and COMMIT closes it. Everything in between counts as one unit.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Linh';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Marco';
COMMIT;

Until COMMIT, the two changes are only pending. Other users can’t see them yet, and the database can still throw them away. After COMMIT, they are final. The four ACID letters describe what the database guarantees along the way.

Atomicity: all or nothing

Run the transfer in Figure 1, first without a transaction, then with one. Both times, the server crashes right after Linh pays. Without a transaction, each UPDATE is saved the moment it runs. Nothing links the two halves, so a crash leaves the money half moved. With a transaction, the database knows the two updates belong together. If anything stops the transaction before COMMIT, such as a crash, an error, or the app sending ROLLBACK, the database undoes every pending change. This undo is called a rollback. That is atomicity: the transaction is one piece that can’t be split, whatever number of statements it has.

Consistency: the rules always hold

Linh has $200. What should happen if the app asks to send $500? The bank has a rule, written into the table itself:

CREATE TABLE accounts (
  name    TEXT PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);

The database checks every declared rule before it lets a transaction commit. If one rule fails, it rolls back the whole transaction, so Marco’s half of the transfer is undone too. Rules can be CHECK, NOT NULL, UNIQUE or foreign keys. Consistency means the database moves from one valid state to another valid state, and never stops in between. Consistency has a limit. The database only enforces rules it was told about. If the app sends $50 when Linh meant $5, every rule still holds, and the database saves the mistake. Of the four letters, C is the one that depends on you. A, I and D are the tools the database uses to keep your rules true.

Isolation: one at a time, even when they’re not

Linh’s joint account has $100. Linh and her brother each take out $80, at the same moment, from two different ATMs. Each ATM runs the same steps: read the balance, check there is enough, pay out, write the new balance. Each ATM did nothing wrong on its own. The bug came from the two running at the same time. T2 made its decision using a balance that T1 was about to change. Isolation means each transaction behaves as if it were alone. The strongest form, serializable isolation, promises that the result is the same as running the transactions one after another in some order. A database can get there in two main ways. It can make T2 wait, using a lock, as in Figure 3. Or it can let both run and, at commit time, cancel the one that conflicts. The app then retries it.

Isolation levels

Full serializable isolation costs speed, because transactions wait or retry more often. So SQL defines four isolation levels. Each weaker level allows some odd results, called anomalies, in exchange for more work running at once.

Dirty read: you read a change that another transaction has not committed yet, and it may still be rolled back.
Non-repeatable read: you read the same row twice in one transaction and get two different values, because someone committed in between.
Phantom read: you run the same query twice and new rows appear, because someone inserted them in between.

Figure 4 plays each anomaly as a race between a reader, T1, and a writer, T2. Pick a level and watch what T1 sees. The standard sets the minimum each level must prevent. Many databases prevent more: PostgreSQL’s Repeatable Read also blocks phantom reads, for example. Most databases do not use Serializable by default:

Database	Default level	Source
PostgreSQL	Read Committed	docs
Oracle	Read Committed	docs
SQL Server	Read Committed	docs
MySQL InnoDB	Repeatable Read	docs

Azure SQL Database also defaults to Read Committed, but reads from row versions (READ_COMMITTED_SNAPSHOT) instead of taking locks. So at the default level of most databases, the read-then-write in Figure 3 can still cause a lost update. The paper that sharpened these definitions lists lost update as possible at Read Committed (Berenson et al., “A Critique of ANSI SQL Isolation Levels”, 1995). There are three common fixes. They are alternatives: pick one. Each is a complete withdrawal on its own (PostgreSQL syntax). In all three, the app still checks that Linh has $80. That check is the bank’s rule, and no isolation level makes it for you. Isolation only makes sure the check sees the real balance, not a stale one. In Figure 3, both ATMs did check. The bug was that ATM B checked an old $100.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE name = 'Linh';
-- app checks balance >= 80
UPDATE accounts SET balance = balance - 80
WHERE name = 'Linh';
COMMIT;
-- If both ATMs run at once, one gets
-- a serialization error. The app retries
-- it from BEGIN, reads $20, and refuses.

BEGIN;
SELECT balance FROM accounts WHERE name = 'Linh'
FOR UPDATE;
-- ATM B waits here until ATM A commits
-- app checks balance >= 80
UPDATE accounts SET balance = balance - 80
WHERE name = 'Linh';
COMMIT;
-- ATM B then reads $20 and refuses.

UPDATE accounts SET balance = balance - 80
WHERE name = 'Linh' AND balance >= 80;
-- 1 row updated: pay out.
-- 0 rows updated: refuse.

Fix 1 lets both ATMs run freely and cancels one if they collide. The app must be ready to retry.
Fix 2 makes ATM B wait at the SELECT until ATM A commits.
Fix 3 puts the check inside the UPDATE, and works even at Read Committed. When two UPDATEs hit the same row, the second one waits for the first to commit, then re-checks its WHERE clause against the new value (PostgreSQL docs). ATM B finds $20, so balance >= 80 is false, and nothing is taken.

Durability: committed means saved

The app gets COMMIT OK. One millisecond later, the power goes out. Is the transfer still there after the restart? Rewriting whole data files on every commit would be slow. So most databases first add a short record of each change to a write-ahead log (WAL), and force that log onto the disk. Only then do they answer COMMIT OK. The data files are brought up to date later, in the background. After a crash, the database reads the log from a known safe point. It redoes every change from committed transactions and throws away changes from transactions that never committed. The same log is what makes the rollback in Figure 1 possible after a crash. Durability is only as strong as the disk. If the disk itself is destroyed, you need copies elsewhere: replicas or backups.

ACID on a data lake

ACID started in databases that own their storage. A data lake is different: it is a large set of files, often Parquet, in cloud object storage such as Amazon S3. Many jobs write those files at once, and a reader can catch a job halfway through. Table formats such as Delta Lake, Apache Iceberg and Apache Hudi add a transaction layer on top of the files. Delta Lake does it with an ordered transaction log: a folder named _delta_log that holds numbered commit files (00000000000000000000.json, …001.json, and so on). Each commit file lists the data files that one transaction added or removed.

Atomicity: a job writes its new data files first. They don’t count until the job adds the next commit file. If the job crashes before that, readers never see its files.
Isolation: a reader picks the latest commit number and reads exactly that version of the table, a snapshot. Writers use optimistic concurrency: if two writers race to create the same next commit file, only one wins. The other checks whether the winner’s changes conflict with its own. If not, it retries at the next number. If so, it fails.
Durability: commit files live in the same durable object storage as the data.
Consistency: Delta checks the table schema and any declared NOT NULL or CHECK constraints on every write.

By default, Delta Lake guarantees that writes are serializable, a level it calls WriteSerializable. Full Serializable, which also covers reads, is a table setting you can turn on. In open-source Delta Lake, each commit covers one table. Sources: Delta Lake’s transaction log and Databricks isolation levels.

Summary

One transfer, four promises: Three things to take away:

Put writes that belong together in one transaction. Without BEGIN … COMMIT, there is nothing to roll back.
Know your isolation level. Most databases default to Read Committed, which still allows lost updates.
Declare your rules. Consistency only protects rules the database knows about.

Check yourself

Glossary

Term	Meaning
Transaction	A group of operations the database treats as one unit of work.
Commit	Make a transaction’s changes final and visible to others.
Rollback	Undo every change a transaction made.
Constraint	A declared rule, such as `CHECK` or `UNIQUE`, the data must always obey.
Lost update	Two transactions read the same value; the second write silently replaces the first.
Dirty read	Reading another transaction’s change before it commits.
Isolation level	How much one running transaction may see of others; Serializable is the strongest.
Serializable	The result equals some one-at-a-time order of the transactions.
Write-ahead log (WAL)	A file on disk where changes are recorded before the data files are updated.
Optimistic concurrency	Let transactions run freely and check for conflicts only at commit time.

For AI agents

Adapted and expanded from Databricks’ “What Are ACID Transactions?”. The isolation levels, write-ahead log and Delta Lake details go beyond that article. Figures are simplified teaching models; the balances are illustrative.

All posts

Performance

Database

What Are ACID Transactions?

What is a transaction?

Atomicity: all or nothing

Consistency: the rules always hold

Isolation: one at a time, even when they’re not

Isolation levels

Durability: committed means saved

ACID on a data lake

Summary

Check yourself

Glossary

For AI agents

​What is a transaction?

​Atomicity: all or nothing

​Consistency: the rules always hold

​Isolation: one at a time, even when they’re not

​Isolation levels

​Durability: committed means saved

​ACID on a data lake

​Summary

​Check yourself

​Glossary

​For AI agents

What is a transaction?

Atomicity: all or nothing

Consistency: the rules always hold

Isolation: one at a time, even when they’re not

Isolation levels

Durability: committed means saved

ACID on a data lake

Summary

Check yourself

Glossary

For AI agents