Transactions and ACID

A transaction is all or nothing: atomicity, consistency, isolation and durability, commit and rollback, and redundancy.

A11.6Databases and big dataA level55 min

A database change is often several statements that only make sense together. When BugBot carries two balls from the red zone to the blue zone, the database must add 2 to blue and take 2 from red. If the power fails between the two, two balls have appeared from nowhere. This lesson is about transactions, the way a DBMS makes sure that never happens, and the four properties, ACID, that a reliable transaction must have.

What a transaction is

A transaction is a single logical unit of work made of one or more operations on the database, which must either all happen or all not happen. In SQL:

BEGIN TRANSACTION
UPDATE zones SET balls = balls + 2 WHERE name = 'blue'
UPDATE zones SET balls = balls - 2 WHERE name = 'red'
COMMIT

• COMMIT makes the changes permanent and visible to other users.
• ROLLBACK undoes every change made since the transaction began, putting the database back exactly as it was.

Transaction processing is the handling of every change as a transaction: a DBMS that does it keeps a log of the changes each transaction makes, so it can undo an unfinished transaction or redo a committed one after a crash.

Half a change

First, what goes wrong without a transaction. This database commits every statement as soon as it runs (isolation_level=None), and the program fails between the two updates:

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)     # every statement commits at once
db.execute("CREATE TABLE zones (name TEXT PRIMARY KEY, balls INTEGER NOT NULL)")
db.executemany("INSERT INTO zones VALUES (?, ?)", [("red", 3), ("blue", 2)])

try:
    db.execute("UPDATE zones SET balls = balls + 2 WHERE name = 'blue'")
    raise RuntimeError("power cut")                          # the failure, between the two updates
    db.execute("UPDATE zones SET balls = balls - 2 WHERE name = 'red'")
except RuntimeError as e:
    print("failed:", e)

print(db.execute("SELECT * FROM zones").fetchall())
print("total:", db.execute("SELECT SUM(balls) FROM zones").fetchone()[0])

Run this in the simulator

There were 5 balls; now the database says 7. Now the same with a transaction. In Python's sqlite3, with db: wraps the statements in a transaction: it commits if the block finishes and rolls back if an exception escapes it.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE zones (name TEXT PRIMARY KEY, balls INTEGER NOT NULL)")
db.executemany("INSERT INTO zones VALUES (?, ?)", [("red", 3), ("blue", 2)])
db.commit()

try:
    with db:
        db.execute("UPDATE zones SET balls = balls + 2 WHERE name = 'blue'")
        raise RuntimeError("power cut")
        db.execute("UPDATE zones SET balls = balls - 2 WHERE name = 'red'")
except RuntimeError as e:
    print("failed:", e)

print(db.execute("SELECT * FROM zones").fetchall())
print("total:", db.execute("SELECT SUM(balls) FROM zones").fetchone()[0])

Run this in the simulator

The half-done update was rolled back, and the total is still 5.

ACID

A DBMS guarantees four properties for every transaction:

Atomicity is provided by rollback, using the log. Durability comes from writing the change and the log to non-volatile storage before reporting the commit as done. Isolation is provided by the concurrency controls of the next lesson, such as record locking.

Consistency: rules the database enforces

A CHECK constraint is a rule the DBMS tests on every change. A statement that would break it fails, and with a transaction around it, the whole transaction can be rolled back:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE zones (name TEXT PRIMARY KEY, balls INTEGER NOT NULL CHECK (balls >= 0))")
db.executemany("INSERT INTO zones VALUES (?, ?)", [("red", 3), ("blue", 2)])
db.commit()

try:
    db.execute("UPDATE zones SET balls = balls - 4 WHERE name = 'blue'")
except sqlite3.IntegrityError as e:
    print("refused:", e)
print(db.execute("SELECT * FROM zones").fetchall())

Run this in the simulator

A transaction around a physical action

A transaction can span the robot's own work. The database records the carry only if the robot really arrived; if it did not, the change is rolled back.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE zones (name TEXT PRIMARY KEY, balls INTEGER NOT NULL CHECK (balls >= 0))")
db.executemany("INSERT INTO zones VALUES (?, ?)", [("red", 3), ("blue", 2)])
db.commit()

try:
    with db:
        db.execute("UPDATE zones SET balls = balls - 1 WHERE name = 'red'")
        forward(50, distance=30)                    # carry the ball to the blue zone
        x, y = position()
        if y < 28:
            raise RuntimeError("did not reach blue")
        db.execute("UPDATE zones SET balls = balls + 1 WHERE name = 'blue'")
    print("committed")
except RuntimeError as e:
    print("rolled back:", e)

print(db.execute("SELECT * FROM zones ORDER BY name").fetchall())

Run this in the simulator

Had the robot been blocked short of the blue zone, the exception would have rolled back the first update, and the red zone would still have 3 balls.

Record locking and redundancy

Two more ideas complete transaction processing.

Record locking stops two transactions changing the same record at once. While one transaction is updating a record, the record is locked and any other transaction that wants it must wait until the first commits or rolls back. The next lesson looks at why this is needed and at the problem it can cause.

Redundancy here means keeping extra copies on purpose: mirrored disks, replicated database servers, and backups, so that a failed disk or server loses neither data nor service. A replica can take over if the main server fails, which protects durability. Do not confuse it with data redundancy inside a database (lesson A11.2), the unwanted repetition of the same fact that normalisation removes. In an answer, say which one you mean.

Task: all or nothing

The table zones holds each zone's name (text, the primary key) and its number of balls (a whole number), with the rule CHECK (balls >= 0). It starts as red 3, blue 2, green 0. moves is a list of (src, dst, n): two zone names and a whole number of balls.

1. Write move(src, dst, n) that, as one transaction, first adds n to dst with UPDATE zones SET balls = balls + ? and then takes n from src. If the database refuses the change with sqlite3.IntegrityError, the whole move must be undone: neither zone changes. Print moved <n> <src> -> <dst> if it worked, or refused <n> <src> -> <dst> if not. Do not test the numbers with an if of your own: let the CHECK rule refuse.
2. Call move for each item of moves, in order.
3. Print every zone in name order as <name> <balls>, then total <sum> using SUM.

Eight lines in all. The robot does not move.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE zones (name TEXT PRIMARY KEY, balls INTEGER NOT NULL CHECK (balls >= 0))")
db.executemany("INSERT INTO zones VALUES (?, ?)", [("red", 3), ("blue", 2), ("green", 0)])
db.commit()

moves = [("red", "blue", 2), ("blue", "green", 5), ("blue", "green", 3), ("red", "green", 2)]

Challenges

1. Take the with db: out of your move. Which line of output changes, and which ACID property has been lost?
2. A bank moves £50 from one account to another. Describe what each ACID property guarantees.
3. Why must a DBMS write to its log before it tells the user a transaction is committed?