Database Isolation Levels: How Concurrent Transactions Avoid Chaos
by Froga
A look at what can go wrong when several transactions touch a database at the same time, and the isolation levels SQL databases use to control it. For anyone who already knows what a database table is and wants to understand what happens under concurrent load.
A transaction is a group of database operations that either all happen or none do. When two transactions run at the same time, three problems can appear. A dirty read happens when one transaction reads a row that another transaction has changed but not yet committed, and that change is later rolled back. A non-repeatable read happens when a transaction reads the same row twice and gets two different values, because another transaction committed a change to that row in between. A phantom read happens when a transaction re-runs the same search and a row appears or disappears, because another transaction inserted or deleted a matching row in between.
A transaction reads a customer's balance, keeps working, and a moment later reads that same balance again. In between, a different transaction committed a change to it, so the second read comes back with a different number. Which problem has just occurred?
multiple choice
Ready to test yourself?
Sign up free to answer, score points, and build your streak.