Isolation Levels
SQL: lesson 14 of 15
How much of another transaction's mess you are allowed to see.
Lesson 14 of 15 · 6 min
Isolation Levels
Step 1 of 9
bed.free3
One row — a ward with three free beds — and two connections about to touch it at the same time.
The Idea
Transactions run at the same time, so the engine must decide what one may see of another's unfinished work. The levels are a ladder: read uncommitted, read committed, repeatable read, serializable. Each rung rules out one more anomaly and allows a little less concurrency.
Real-World Example
A ward whiteboard. A porter rubbing out a bed count mid-move: read it now and you may see a half-finished number, read it twice and get two answers, or wait for the pen to lift and be certain.
The Code
import sqlite3, os, tempfile
path = os.path.join(tempfile.mkdtemp(), "ward.db")
sqlite3.connect(path).executescript("""PRAGMA journal_mode=WAL;
CREATE TABLE bed (ward TEXT, free INT); INSERT INTO bed VALUES ('ivy',3);""")
writer = sqlite3.connect(path, isolation_level=None)
reader = sqlite3.connect(path, isolation_level=None)
writer.execute("BEGIN IMMEDIATE"); writer.execute("UPDATE bed SET free=0")
reader.execute("BEGIN") # snapshot taken here
print(reader.execute("SELECT free FROM bed").fetchone()) # (3,) uncommitted hidden
writer.execute("COMMIT")
print(reader.execute("SELECT free FROM bed").fetchone()) # (3,) repeatable
reader.execute("COMMIT")
print(reader.execute("SELECT free FROM bed").fetchone()) # (0,)Your turn
Put the steps in the right order.
- The writer commits, yet the reader's next query is unchanged
- The writer opens a transaction and updates the row
- The reader ends its transaction and finally sees the new value
- The reader opens a transaction, fixing the snapshot it will see
Mini quiz
1 / 3