Lesson 14 of 14 · 8 min · Database theory for interviews
ACID and isolation levels
What you will learn
- The four ACID properties in plain words
- The problems that appear when transactions overlap: dirty read, non-repeatable read, phantom read
- The four standard isolation levels
- What PostgreSQL and MySQL do by default
- How to answer isolation-level questions in an interview
In simple words
Many people use a database at the same moment. ACID is a set of four promises that keep it trustworthy: a transaction is all-or-nothing, it leaves the data valid, it is not confused by other people working at the same time, and once saved it stays saved. Isolation levels are a dial that decides how much one person's half-finished work is visible to others.
Words to know
- ACID: atomicity, consistency, isolation, durability, the four transaction promises.
- Isolation: how separate a transaction's view is from other transactions running at the same time.
- Dirty read: seeing another transaction's changes that are not yet committed.
- Phantom: a new row that appears when you repeat a query inside one transaction.
ACID
- Atomicity: all of the transaction's changes happen or none do. If it crashes halfway, the database rolls back.
- Consistency: a transaction takes the database from one valid state to another. Constraints (keys,
CHECK, foreign keys) are never left broken at commit. (Your application's own rules, such as "total stock never negative", you must enforce with constraints or code.) - Isolation: concurrent transactions do not see each other's unfinished work; the result should be as if they ran one after another, to the degree the isolation level promises.
- Durability: once committed, the data survives a crash or power cut. Databases achieve this with a write-ahead log that is flushed to disk before the commit is acknowledged.
What goes wrong without isolation
Imagine two sessions on the accounts table (Asha has 5000).
- Dirty read: session B reads Asha's balance after session A has changed it to 3500 but before A commits. A then rolls back. B acted on a value that never existed.
- Non-repeatable read: B reads Asha's balance (5000). A commits a change to 3500. B reads again in the same transaction and now sees 3500. The same query gave two answers.
- Phantom read: B runs
SELECT ... WHERE balance > 1000and gets 2 rows. A inserts a new account and commits. B runs the same query again and gets 3 rows. A new row, a "phantom", appeared. - Lost update: A and B both read 5000, A writes 4000, B writes 4500, and A's change is lost. Prevent it with atomic updates (
balance = balance - 1000), row locks (FOR UPDATE), or optimistic version columns.
The four isolation levels
The SQL standard defines levels by which problems they allow:
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | possible | possible | possible |
| Read Committed | no | possible | possible |
| Repeatable Read | no | no | possible (by the standard) |
| Serializable | no | no | no |
Higher isolation gives safer results and more waiting, aborts or retries. Real databases differ from the textbook table:
- PostgreSQL uses multiversion concurrency control (MVCC): readers do not block writers. Its default is Read Committed, where each statement sees data committed before it started. Read Uncommitted behaves as Read Committed (dirty reads never happen). Its Repeatable Read takes a snapshot at the start of the transaction and also prevents phantoms; its Serializable is built on that and may abort a transaction with a serialisation failure, which the application must retry.
- MySQL InnoDB also uses MVCC. Its default is Repeatable Read, with a snapshot for plain reads and "next-key" locks that block many phantoms for locking reads. Serializable turns plain reads into locking reads. Read Uncommitted really can show dirty data.
Set the level for a transaction:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- PostgreSQL
-- ... queries ...
COMMIT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- MySQL, applies to the next transaction
START TRANSACTION;
Choosing a level
- Read Committed suits most web applications: each statement sees fresh committed data and locks are short.
- Repeatable Read suits a report that must read consistent numbers across several queries.
- Serializable suits logic that must behave as if transactions ran one at a time (booking the last seat, money rules across rows), when you can retry failures.
- Often a targeted lock (
SELECT ... FOR UPDATE) or a unique constraint solves a specific race more cheaply than raising the level for everything.
Durability and the log
Databases write changes to a write-ahead log (WAL in PostgreSQL, redo log in InnoDB) and flush it to disk at commit. After a crash they replay the log. Settings that trade durability for speed (for example synchronous_commit = off in PostgreSQL, or innodb_flush_log_at_trx_commit values other than 1 in MySQL) can lose the last few commits in a crash, so know the trade-off before using them.
Common mistakes
- Assuming the default level is the same everywhere: PostgreSQL Read Committed, MySQL InnoDB Repeatable Read.
- Raising the isolation level without adding retry logic for serialisation failures and deadlocks.
- Believing consistency in ACID means your business rules are automatically correct.
- Read-modify-write races left unprotected.
- Reciting the textbook table as if every database matched it exactly.
Try it yourself
- Open two sessions on a PostgreSQL table. In Read Committed, read a row in session B, change and commit it in A, and read again in B.
- Repeat after
BEGIN ISOLATION LEVEL REPEATABLE READin B and compare. - Write the
UPDATEthat avoids a lost update on a stock counter. - List the isolation level setting commands for your database.
Check yourself
What does each letter of ACID stand for?
Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions do not interfere), Durability (committed data survives crashes).
What is a non-repeatable read?
Reading the same row twice in one transaction and getting different values because another transaction committed a change in between.
What is the default isolation level in PostgreSQL and in MySQL InnoDB?
PostgreSQL: Read Committed. MySQL InnoDB: Repeatable Read.
What must an application do when a Serializable transaction fails with a serialisation error?
Retry the whole transaction.
Interview questions
Explain the isolation levels and the problems each prevents.
Read Uncommitted allows dirty reads; Read Committed prevents dirty reads; Repeatable Read also prevents non-repeatable reads; Serializable also prevents phantoms and behaves as if transactions ran one at a time. Higher levels cost more waiting or retries, and real engines such as PostgreSQL implement them with snapshots.
What is MVCC?
Multiversion concurrency control keeps several versions of a row so readers see a consistent snapshot without blocking writers, and writers do not block readers.
How would you prevent two users booking the last seat?
Make the check and update atomic: UPDATE seats SET booked = true WHERE id = ? AND booked = false and check the affected row count, or lock the row with SELECT ... FOR UPDATE, or use a unique constraint; use Serializable only if you also handle retries.
Summary
- ACID: all-or-nothing, valid state to valid state, isolated from others, durable after commit.
- Overlapping transactions cause dirty reads, non-repeatable reads, phantoms and lost updates.
- Isolation levels trade safety against concurrency; PostgreSQL defaults to Read Committed, MySQL InnoDB to Repeatable Read.
- Use atomic updates, locks or constraints for specific races, and retry on serialisation failures and deadlocks.
Going deeper
- Transaction isolation: PostgreSQL's levels, in full.
- Concurrency control and MVCC: how readers and writers coexist.
- InnoDB transaction isolation levels: MySQL's behaviour.
- Reliability and the write-ahead log: how durability works.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.