Transactions and ACID

A transaction is a group of operations that the database treats as one unit of work. Transactions are described by the ACID properties. Atomicity: all of the operations take effect, or none do. Consistency: a committed transaction moves the database from one state that satisfies its constraints to another. Isolation: concurrent transactions do not see each other's partial work. Durability: once committed, the changes survive crashes, usually through a write-ahead log.

Python

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE account (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
db.execute("INSERT INTO account VALUES (1, 100), (2, 50)")


def transfer(source, target, amount):
    db.execute("BEGIN")
    try:
        db.execute("UPDATE account SET balance = balance + ? WHERE id = ?", (amount, target))
        db.execute("UPDATE account SET balance = balance - ? WHERE id = ?", (amount, source))
        db.execute("COMMIT")
        return True
    except sqlite3.IntegrityError:
        db.execute("ROLLBACK")
        return False


print(transfer(1, 2, 30))
print(transfer(1, 2, 500))
print(db.execute("SELECT id, balance FROM account ORDER BY id").fetchall())

Read the full lesson