AdvancedLesson 22 of 306 min read

Transactions: ACID, Isolation and Locks

See atomicity save a crashing transfer, watch one connection hide its uncommitted change from another, and meet isolation levels and locking.

In this lesson you will

  • State the four ACID promises
  • See a rollback protect data
  • Explain isolation and dirty reads
  • Follow habits for safe transactions

About 28 minutes

Money that vanished half way

A transfer between two accounts is two steps: take money from one, add it to the other. If the power fails between them, the money has left one account and not arrived at the other. Nobody wants a database that can do that. The tool that prevents it is the transaction, which you met in lesson 9. This lesson goes deeper, with the four promises that make transactions trustworthy, and with what happens when two people use the database at once.

ACID in plain words

  • Atomicity. A transaction is all or nothing. If any step fails, every step is undone.
  • Consistency. A transaction takes the database from one valid state to another, and the rules you defined, such as constraints, are never left broken.
  • Isolation. Transactions that run at the same time do not see each other’s half-finished work, to the degree the isolation level promises.
  • Durability. Once a transaction is committed, it survives a crash.

Most mainstream relational databases offer these guarantees. Consistency is partly your job: the database enforces the rules that you wrote, so the quality of your constraints matters.

Run it

The program uses a small bank-style table with two accounts holding 100 and 50 units, made up. It tries a good transfer, a crashing one inside a transaction and the same crash without a transaction. Then it opens two connections to the same database file to see isolation and locking at work. SQLite behaves in its own way here, and the other main databases do things differently, so treat the second half as an illustration of the ideas.

import sqlite3, os

def fresh():
    if os.path.exists("bank.db"):
        os.remove("bank.db")
    db = sqlite3.connect("bank.db", timeout=0.2)
    db.executescript("CREATE TABLE acct (name TEXT PRIMARY KEY, units INTEGER NOT NULL CHECK (units >= 0));"
                     "INSERT INTO acct VALUES ('A', 100), ('B', 50);")
    return db

def total(db):
    return db.execute("SELECT SUM(units) FROM acct").fetchone()[0]

def transfer(db, amount, crash_between):
    try:
        db.execute("UPDATE acct SET units = units - ? WHERE name = 'A'", (amount,))
        if crash_between:
            raise RuntimeError("power cut")
        db.execute("UPDATE acct SET units = units + ? WHERE name = 'B'", (amount,))
        db.commit()
        return "committed"
    except Exception as why:
        db.rollback()
        return "rolled back after " + str(why)

db = fresh()
print("start total:", total(db))
print("good transfer:", transfer(db, 30, False), "| total:", total(db))
print("crashing transfer:", transfer(db, 30, True), "| total:", total(db))
print("balances now:", dict(db.execute("SELECT name, units FROM acct")))

# without a transaction each statement is saved at once
auto = sqlite3.connect("bank.db", isolation_level=None, timeout=0.2)
auto.execute("UPDATE acct SET units = units - 30 WHERE name = 'A'")
print("autocommit, crash before the second step | total:", total(db), "(30 units vanished)")

# two connections: isolation and locking
db = fresh()
other = sqlite3.connect("bank.db", timeout=0.2)
db.execute("UPDATE acct SET units = 999 WHERE name = 'A'")        # not committed yet
print("the other connection sees A =", other.execute("SELECT units FROM acct WHERE name = 'A'").fetchone()[0], "(before the commit)")
try:
    other.execute("UPDATE acct SET units = 1 WHERE name = 'B'")
    print("second writer: allowed")
except sqlite3.OperationalError as why:
    print("second writer refused:", why)
db.commit()
print("after the commit the other connection sees A =", other.execute("SELECT units FROM acct WHERE name = 'A'").fetchone()[0])
start total: 150
good transfer: committed | total: 150
crashing transfer: rolled back after power cut | total: 150
balances now: {'A': 70, 'B': 80}
autocommit, crash before the second step | total: 120 (30 units vanished)
the other connection sees A = 100 (before the commit)
second writer refused: database is locked
after the commit the other connection sees A = 999

The total starts at 150. The good transfer was committed, and the total stayed 150. The crashing transfer was rolled back after power cut: the debit was undone, and the total is still 150. The balances afterwards are {‘A’: 70, ‘B’: 80}, which is the result of only the good transfer. Now the dangerous case. With no transaction, each statement is saved at once. The debit was saved, the crash came before the credit, and the total is 120 instead of 150. Thirty units vanished, exactly the disaster atomicity prevents.

Isolation and locks

In the second half, one connection changes account A to 999 but has not committed yet. A second connection looks and sees 100, the old committed value. It never sees the half-finished change, which is isolation at work, and the reading of uncommitted data by another transaction is called a dirty read. When the second connection tries to write, it is refused with the message ‘database is locked’. SQLite allows only one writer at a time, so the second writer must wait, and here it gives up after a short timeout. After the first connection commits, the second sees 999. Other databases lock at finer levels, such as single rows, so two writers working on different rows can proceed together.

Isolation levels

The SQL standard describes four isolation levels. Read uncommitted allows dirty reads. Read committed prevents them. Repeatable read also prevents a row changing between two reads in your transaction. Serializable behaves as if the transactions ran one after the other. The stricter the level, the safer the results and the more waiting or retrying you may meet. Databases choose different defaults and implement the levels differently, so check what yours does, and do not assume that a level name means the same thing everywhere.

Habits for safe transactions

  • Keep transactions short. A long-running one holds locks and blocks others.
  • Do all the steps that must succeed together inside one transaction.
  • Be ready for a failure: catch the error, roll back and retry or report.
  • Touch tables in the same order everywhere, which lowers the chance of two transactions waiting for each other, a deadlock.
  • Do not wait for a person inside a transaction. Never leave one open while someone decides.
Common mistake: no transaction around related steps

Running related changes as separate statements with no transaction. Each succeeds alone, and a failure in the middle leaves the data half changed.

A practice copy only

The accounts are made up, and the demonstration used SQLite on a temporary file. Locking, isolation defaults and error messages differ between databases, so check your own system before relying on a particular behaviour.

Write down a task in your own work that has two or more steps that must succeed together. List the steps and decide where the transaction begins and ends.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does atomicity guarantee?

  1. Every row is unique
  2. A transaction is all or nothing
  3. Data is encrypted
  4. Queries are fast

The crashing transfer was undone completely.

What happened without a transaction?

  1. Nothing changed
  2. The credit ran twice
  3. The debit was saved and 30 units vanished
  4. The table was deleted

The total fell from 150 to 120.

What is a dirty read?

  1. Reading another transaction’s uncommitted change
  2. Reading a deleted table
  3. Reading twice
  4. Reading a NULL

The other connection saw only the committed value, so it avoided one.

What does durability promise?

  1. Changes can be undone later
  2. Reads are never blocked
  3. A committed change survives a crash
  4. Tables never grow

Once committed, the data stays.

Why keep transactions short?

  1. Long ones hold locks and block others
  2. Short ones use less disk
  3. The database requires it
  4. It removes ACID

Locks last until the transaction ends.

Summary

  • ACID: atomic, consistent, isolated and durable.
  • A transaction makes related steps all or nothing.
  • Isolation hides uncommitted work from others.
  • Keep transactions short and ready to fail.

Next lesson

Lesson 23 shows how to clean messy data with SQL.

Questions

Do all databases give ACID?

Most mainstream relational databases do, with differences in detail. Some systems relax the rules for speed or scale, so check what yours promises.

Which isolation level should I use?

Start with your database's default and learn what it prevents. Raise it only when a real problem needs it.

Are the balances real?

No. The accounts and units are made up for the demonstration.

Progress is saved in this browser.