Transactions & ACID
SQL: lesson 8 of 15
All of the writes land, or none of them do.
Lesson 8 of 15 · 6 min
Transactions & ACID
Step 1 of 11
A transaction is one unit of work: all of its writes land, or none of them do.
The Idea
A transaction wraps several statements into one unit of work: Atomic (all or nothing), Consistent (constraints always hold), Isolated (others do not see your half-finished state) and Durable (a commit survives a crash). If any step fails, the engine unwinds every earlier step with it.
Real-World Example
A museum lends an object to another collection. The registrar cannot sign it out of one register without signing it in at the other — an object that exists in neither book has, on paper, ceased to exist. Both lines are signed, or the crate never leaves.
The Code
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE loan (museum TEXT, objects INTEGER CHECK (objects >= 0));
INSERT INTO loan VALUES ('Hallam', 3), ('Verrey', 0);
""")
try:
with db: # commits at the end, rolls back if the block raises
db.execute("UPDATE loan SET objects = objects + 1 WHERE museum = 'Hallam'")
db.execute("UPDATE loan SET objects = objects - 1 WHERE museum = 'Verrey'")
except sqlite3.IntegrityError:
pass # Verrey would go to -1, so the CHECK fires
print(db.execute("SELECT * FROM loan").fetchall())
# [('Hallam', 3), ('Verrey', 0)] <- the first UPDATE was undone as wellYour turn
What does this print?
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE loan (museum TEXT, objects INTEGER CHECK (objects >= 0));
INSERT INTO loan VALUES ('Hallam', 3), ('Verrey', 0);
""")
try:
with db:
db.execute("UPDATE loan SET objects = 9 WHERE museum = 'Hallam'")
db.execute("UPDATE loan SET objects = -1 WHERE museum = 'Verrey'")
except sqlite3.IntegrityError:
pass
print(db.execute("SELECT * FROM loan").fetchall())Mini quiz
1 / 3