Transactions: ACID and isolation levels

Money, stock and seats all change when many users act at the same moment. So interviewers ask: "What happens if two requests update the same data at the same time?" They want to see if you know that a database can promise safety, and that the promise has limits.

A weak answer says "use a transaction" and thinks the problem is solved. A strong answer says what a transaction promises. It names one bug that can still happen with the default setting. It says how to prevent the bug and what that costs.

At SDE-1 level you should explain all-or-nothing changes. At SDE-2 level you should explain how strictly the database keeps users apart, and name two bugs, such as one change overwriting another. At SDE-3 level you should know that databases differ, and say when you would accept a retry (running the request again).

Think of a bank counter. You give the clerk a slip: "move Rs 500 from my account to my brother's account". The clerk must not stamp only the first half. Either both steps are done, or none. And while the clerk works, no other customer should see the money missing from both accounts.

A transaction is a group of changes that the database treats as one unit. You mark its start and its end:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;

UPDATE changes rows, and WHERE chooses which rows. Commit means "keep all the changes". Rollback means "cancel all the changes". If anything fails before the commit, the database does a rollback for you.

Postgres has no transaction inside a transaction. Instead, you can set a savepoint: a marker inside a transaction. ROLLBACK TO that marker cancels only the changes made after it. The earlier changes stay, and the transaction goes on.

ACID: four promises

1. Atomicity. All or nothing. If the server crashes between the two updates, neither change is kept.
2. Consistency. The data keeps the rules you gave the database, such as "balance must not be negative" or "every order has a customer". A change that breaks a rule is refused. This does not mean that every user sees the same data at the same moment. It only means the rules hold.
3. Isolation. Transactions that run at the same time (concurrent transactions) do not disturb each other. How strong this promise is depends on a setting called the isolation level, which you meet below.
4. Durability. After the commit is confirmed, the change survives a crash or a power cut. The database first writes the change to a log on disk, and only then says "done".

What can go wrong when transactions run together

Each bug below is a way in which one transaction disturbs another:

• Dirty read. You read a change that another transaction has not committed. It may still be rolled back, so you saw data that never really existed.
• Non-repeatable read. You read the same row twice in one transaction and get two values, because another transaction committed a change in between.
• Phantom read. You run the same query twice in one transaction and get different rows, because another transaction added or removed matching rows in between.
• Lost update. Two transactions read the same value. Each calculates a new value in application code and writes it back. The second write replaces the first, and the first change is lost.
• Write skew. Two transactions read the same rows. Each then writes a different row, based on what it read. Each change is fine alone, but together they break a rule.

Isolation levels

An isolation level decides which of these bugs are allowed. The SQL standard (the official rules for SQL) has four levels:

1. Read Uncommitted: may show uncommitted changes.
2. Read Committed: shows only committed changes.
3. Repeatable Read: the transaction keeps seeing the same data once it has read it.
4. Serializable: the result is the same as if the transactions had run one after another.

Real databases do not follow the standard exactly, so you must know your database. This is how Postgres behaves:

Three points about Postgres:

• Dirty reads never happen, at any level.
• Repeatable Read stops phantom reads too. This is stronger than the standard asks.
• At Repeatable Read, your transaction may try to change a row that another transaction changed after your snapshot. The database then stops it with an error ("could not serialize access due to concurrent update", code 40001). Your code must retry the whole transaction. This is how lost updates are stopped.

Other databases differ. For example, MySQL with the InnoDB engine uses Repeatable Read as its default, and its behaviour is not the same as in Postgres. Read your database manual before you say what a level prevents.

A wallet app. All numbers are made up to teach with.

1. Atomicity. Account A holds Rs 1,000 and account B holds Rs 200. Total = Rs 1,200. We move Rs 500 from A to B.

• Without a transaction, the server crashes after the debit. A = 1,000 - 500 = Rs 500. B = Rs 200. Total = Rs 700. Rs 500 has disappeared.
• With a transaction, the crash happens before the commit. The database rolls back. A = Rs 1,000, B = Rs 200, total Rs 1,200.

2. Lost update (the predict box). The wallet holds Rs 1,000. Payment 1 is Rs 300. Payment 2 is Rs 200. At Read Committed:

The final balance is Rs 800. It should be 1,000 - 300 - 200 = Rs 500. Rs 300 of payment 1 is lost. If the order in steps 3 and 4 is swapped, the final balance is Rs 700, and Rs 200 is lost. So the answer to the predict box is: 800 or 700, and it should be 500.

At Repeatable Read, step 4 fails with error 40001, because the row changed after payment 2 took its snapshot. The app retries payment 2. It now reads 700 and writes 700 - 200 = Rs 500. Correct.

3. Phantom read. A report counts payments above Rs 5,000 today. At Read Committed, it counts 10. Another transaction adds a payment of Rs 6,000 and commits. The report runs the same count again in the same transaction and gets 11. At Repeatable Read in Postgres, both counts give 10. A report that only reads has nothing that can conflict, so it does not fail.

4. Write skew. A family has two wallets. The rule is that A + B must never be below zero. A = Rs 600 and B = Rs 600, so the total is Rs 1,200. Two transactions run at the same time:

• Transaction 1 reads both (total 1,200). It sees that 1,000 is allowed, and takes Rs 1,000 from A. A = -400.
• Transaction 2 reads both (still total 1,200 in its snapshot). It takes Rs 1,000 from B. B = -400.
• They changed different rows, so Repeatable Read sees no conflict. Both commit. A + B = -400 + -400 = -800. The rule is broken.

At Serializable, the database finds the problem, and one transaction fails with error 40001. The retry reads a total of Rs 200 and refuses the Rs 1,000.

• It solves: half-finished changes (atomicity), broken rules, lost data after a crash, and, at the higher levels, the bugs of concurrent users.
• It costs: a higher isolation level means more failed transactions that you must retry, and often more waiting. Serializable costs the most.
• Default choice: Read Committed is fine when each request changes data in one simple step. Use Repeatable Read or Serializable when your code reads a value, decides, and then writes.
• I would change it when: I see a lost update or write skew in a read-then-write flow. Then I move up one level and add a retry. If retries become too frequent, I shorten the transaction.
• Keep transactions short. A long transaction keeps its snapshot and its changed rows open for a long time. This causes conflicts, makes others wait, and delays the database cleaning up old data. Never wait for a user inside a transaction.
• A transaction covers one database. An email you send is not part of it, so a rollback cannot take the email back.

"The C in ACID means every user sees the same latest data." It does not. Consistency means your rules hold: no negative balance, no order without a customer. Seeing the latest data is a different question.

"If I use a transaction, two users cannot overwrite each other." Not at the default level. In the example, both payments ran inside transactions at Read Committed, and Rs 300 was still lost. The level decides.

"Serializable means the database runs transactions one at a time." No. It runs them together, and guarantees that the result matches some order, one after another. To do this it stops some transactions with an error. Your code must retry them.

1. Give one line for each letter of ACID.
2. A wallet holds Rs 2,000. Three payments of Rs 100, Rs 250 and Rs 400 run at the same time at Read Committed. Each reads the balance, subtracts in application code and writes back. (a) What final balances are possible? (b) What should it be? (c) In the worst case, how many rupees is the final balance too high?
3. A nightly report runs three queries in one transaction at the default level. New payments arrive while it runs, and the three totals do not add up. Why, and what do you change?