Transactions: why a money transfer must save both changes or neither

You send £50 from Ana's account to Ben's. Behind the scenes that is two separate changes to a database: take £50 off Ana, then add £50 to Ben. What if the server loses power after the first change and before the second? Ana is £50 poorer and Ben got nothing. The money has simply vanished.

Databases solve this with a transaction: a group of changes that the database treats as one unit. Either every change in the group happens, or none of them do. There is no in-between state that anyone can be left holding.

Two changes, one idea

In SQL (the language most databases are asked questions in), a change to existing rows is an UPDATE. Our transfer is two of them:

UPDATE accounts SET balance = balance - 50 WHERE name = 'Ana';
UPDATE accounts SET balance = balance + 50 WHERE name = 'Ben';

On their own, each statement is saved the moment it finishes (this is often called autocommit). So after the first line, "Ana has £50 less" is already permanent. The database has no idea the second line was supposed to follow.

Wrapping them in a transaction

To group the statements, you tell the database where the group starts and how it ends:

• BEGIN starts the group. From here on, changes are kept in a draft that only you can see.
• COMMIT makes the whole draft permanent, all at once.
• ROLLBACK throws the draft away, as if none of it had happened. If the database or the program crashes before a COMMIT, the draft is thrown away too.

Try it. The simulator below runs the transfer one step at a time. Press Run next step a few times, then press Crash! to cut the power in the middle. Then switch the transaction on or off and compare what survives.

With the transaction on, a crash before COMMIT leaves the saved balances exactly as they were: £100 and £20. With it off, a crash between the two updates leaves £50 and £20, and £50 has disappeared.

Running it for real

This is not a toy idea; you can run the same thing with Python's built-in sqlite3 module (this output is from SQLite 3.45.1):

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
db.executemany("INSERT INTO accounts VALUES (?, ?)", [("Ana", 100), ("Ben", 20)])
db.commit()

try:
    db.execute("UPDATE accounts SET balance = balance - 50 WHERE name = 'Ana'")
    raise RuntimeError("crash!")          # pretend the program dies here
    db.execute("UPDATE accounts SET balance = balance + 50 WHERE name = 'Ben'")
    db.commit()
except RuntimeError as e:
    db.rollback()
    print("error:", e)

print(db.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())

error: crash!
[('Ana', 100), ('Ben', 20)]

The first update ran, but because COMMIT never did, ROLLBACK undid it. Nothing was lost.

Errors roll back too

A transaction also protects you from mistakes the database itself catches. Our table has a rule, CHECK (balance >= 0), that forbids a negative balance. In Python, with db: commits if the block finishes and rolls back if it raises an error:

try:
    with db:
        db.execute("UPDATE accounts SET balance = balance + 80 WHERE name = 'Ben'")
        db.execute("UPDATE accounts SET balance = balance - 80 WHERE name = 'Ana'")
except sqlite3.IntegrityError as e:
    print("error:", e)

After a successful £50 transfer Ana has £50, so taking £80 breaks the rule. The output:

error: CHECK constraint failed: balance >= 0
[('Ana', 50), ('Ben', 70)]

Ben's first update (+ 80) had already run when the second one failed, yet Ben is still at £70. The whole group was undone.

What databases promise

Transactions are the heart of a famous set of guarantees called ACID:

• Atomicity: all the changes happen, or none do. This is what you just played with.
• Consistency: a transaction can only move the database from one valid state to another, so rules like balance >= 0 are never left broken.
• Isolation: other people using the database at the same time don't see your half-finished draft.
• Durability: once COMMIT succeeds, the change survives a crash or power cut.

Most relational databases, including SQLite, PostgreSQL and MySQL (with its InnoDB engine), support transactions. Each differs in the details of isolation, which is a topic of its own.

What this means for your code

Whenever one real-world action needs more than one change to your data (an order plus its stock count, a signup plus a welcome record, a transfer between two accounts), put those changes in one transaction. Keep it short: do the thinking first, then BEGIN, make the changes, and COMMIT. And don't wait on slow things such as sending an email in the middle, because other work may be waiting on your draft to finish.