Transactions, ACID and isolation levels — SQL & Relational Databases

A transaction is a sequence of database operations that are executed as a single unit — they all succeed together or they all fail together. The classic example is a bank transfer: deduct from account A and credit account B. If the deduction succeeds but the credit fails, the money disappears. Transactions prevent this by wrapping both operations in a BEGIN...COMMIT block — if any step fails, the entire sequence is rolled back to the state before the transaction started. This all-or-nothing property is Atomicity, the A in ACID.

ACID is an acronym for the four properties that guarantee reliable transaction behaviour: Atomicity (all-or-nothing execution), Consistency (transactions take the database from one valid state to another, respecting all constraints), Isolation (concurrent transactions do not see each other's intermediate states), and Durability (committed transactions survive system crashes). These four properties collectively define what it means for a database to handle concurrent writes correctly — without ACID guarantees, concurrent multi-user systems would produce incorrect data under load.

Isolation levels define how strictly transactions are isolated from each other's concurrent changes. Stricter isolation prevents more classes of anomalies but reduces concurrency (transactions wait longer for locks). Weaker isolation allows more concurrency but introduces anomalies like dirty reads and non-repeatable reads. Understanding isolation levels is critical for data engineers because they determine the correctness of concurrent pipeline reads, the data consistency observed during long-running analytical queries, and the performance trade-offs of OLTP vs OLAP workloads.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.