Transactions and ACID
Group updates so they all happen or none do, and roll back when a rule fails.
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())True False [(1, 70), (2, 80)]
Explanation
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.
The classic example is a bank transfer: debit one account and credit another. If the second step fails after the first, atomicity requires undoing the first. The program credits the target, then debits the source. When the debit would make the balance negative, the CHECK constraint raises an error, and ROLLBACK undoes the credit too, so no money appears out of nowhere.
Isolation comes in levels that trade strictness for concurrency. SQLite effectively serializes writers. PostgreSQL's default is read committed, and stricter levels such as repeatable read and serializable are available. An in-memory SQLite database like this one has no durability, since durability needs data stored on disk.
Explore the dry run
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())Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| Start an explicit transaction. Nothing is permanent until COMMIT. | — | — | |
| Both updates succeeded, so make them permanent: account 1 has 70 and account 2 has 80. | (1, 70), (2, 80) | — | |
| The second transfer's debit violated CHECK (balance >= 0). Rolling back also undoes the credit to account 2. | both accounts restored | — | |
| The failed transfer left no trace: atomicity. | both accounts restored | True False [(1, 70), (2, 80)] |
Cost and common mistakes
Rollback cost is proportional to the amount of work to undo. Each update here is O(log n) with a primary-key index.
- Without a transaction, a failure between two related updates leaves the data half-changed.
- Keeping a transaction open for a long time blocks other work and increases the chance of conflicts. Keep transactions short.
- A transaction does not make an application correct by itself: constraints and logic still have to define what a valid state is.
Try it yourself
What would the final balances be if transfer(2, 1, 20) ran after the example?
Reveal answer and reasoning
Account 2 loses 20 and account 1 gains 20, so the balances become [(1, 90), (2, 60)].