Why a Transfer Can’t Just Deduct Money – A Simple Guide to ACID Transactions

Transaction definition

A transaction groups multiple operations into a single unit of work. In a money transfer, two steps are required: deduct 100 from Zhang San’s balance and add 100 to Li Si’s balance. Both steps must succeed together or fail together; otherwise the account data becomes inconsistent.

ACID properties

Atomicity : all‑or‑nothing execution; the transfer cannot deduct money without adding it.

Consistency : data must satisfy business rules before and after the transaction, e.g., the total amount of money cannot magically appear or disappear.

Isolation : concurrent transactions should not interfere with each other; a payment and a balance query must not see an intermediate, inconsistent state.

Durability : once a transaction commits, its changes survive system crashes; a restarted system must not lose the committed order.

Concurrency anomalies

Dirty read

Transaction A changes Zhang San’s balance from 100 to 50 but has not committed. Transaction B reads the balance as 50. If Transaction A later rolls back, the 50 never existed, making B’s read dirty.

Non‑repeatable read

Transaction A reads Zhang San’s balance as 100. Transaction B updates the balance to 80 and commits. Transaction A reads the balance again and sees 80. The same row changed between reads.

Phantom read

Transaction A queries orders with amount > 100 and gets 10 rows. Transaction B inserts a new order of amount 200 and commits. Transaction A repeats the query and now gets 11 rows—the extra row is the “phantom”. The distinction: non‑repeatable read changes a row’s content; phantom read changes the result‑set size.

Isolation levels

Isolation levels control how much concurrent transactions may affect each other. Weaker isolation yields better performance but higher risk; stronger isolation ensures data safety but reduces concurrency.

Read Uncommitted – allows dirty reads.

Read Committed – prevents dirty reads by allowing only committed data to be read.

Repeatable Read – prevents non‑repeatable reads, guaranteeing stable results for repeated reads of the same row.

Serializable – the strongest level, essentially serializing transactions, offering the highest safety at the cost of performance.

Default isolation levels: MySQL InnoDB uses Repeatable Read; Oracle uses Read Committed.

Self‑test

What is a transaction? Answer: A group of database operations that either all succeed or all fail.

What do the four letters of ACID stand for? Answer: Atomicity, Consistency, Isolation, Durability.

What is a dirty read? Answer: Reading data that another transaction has not yet committed.

What is a non‑repeatable read? Answer: Within the same transaction, two reads of the same row yield different results.

What is a phantom read? Answer: Within the same transaction, two range queries return a different number of rows.

How to distinguish non‑repeatable read from phantom read? Answer: Row content changes indicate a non‑repeatable read; result‑set row count changes indicate a phantom read.

List the four isolation levels from weakest to strongest. Answer: Read Uncommitted, Read Committed, Repeatable Read, Serializable.

What is MySQL InnoDB’s default isolation level? Answer: Repeatable Read.

Key takeaway

Transactions guarantee all‑or‑nothing execution; ACID ensures transaction reliability; isolation levels control the degree of interaction between concurrent transactions.