Transactions & ACID

SQL: lesson 8 of 10

All of the writes land, or none of them do.

Lesson 8 of 10 · 6 min

Step 1 of 11

stateno transaction

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 well

Your 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

Transactions & ACID