Transactions & ACID

Most real database work is not one statement but a group of statements that only make sense together. Moving money between two accounts means subtracting from one row and adding to another. Placing an order means creating the order, reserving stock and recording a payment. If the program crashes after the first step, or two users act at the same moment, the data can end up half-finished or simply wrong. A transaction is the database's answer: it bundles several statements into a single unit of work that either happens completely or not at all.

This chapter builds that idea from the ground up. You will start with BEGIN, COMMIT and ROLLBACK, see how autocommit changes what a single statement means, and use savepoints to undo only part of your work. Then you will meet the four ACID guarantees through a bank transfer, peek at how logs and MVCC make them possible, and learn what goes wrong when many transactions run at once: concurrency anomalies, isolation levels, locks and deadlocks.

By the end you will be able to wrap multi-step changes in a transaction and recover cleanly from errors. You will also be able to name the anomaly a given bug produces, pick a sensible isolation level, explain why a deadlock happened and how to avoid it, and spot the common mistakes, such as forgetting to commit or holding a transaction open too long, before they reach production.

A transaction is a group of SQL statements that the database treats as one unit of work. Either every statement in the group takes effect, or none of them does. There is no state in between that anyone can end up stuck in.

The classic reason to want this is moving money. A transfer of 200 from account A to account B is really two changes: subtract 200 from A, then add 200 to B. As far as you are concerned it is one action, but the database sees two separate statements. If something goes wrong between them, the data is left half-changed.

The program below sets up two accounts holding 600 in total. It runs the debit, then simulates a crash before the credit can run. The connection is in autocommit mode (isolation_level=None), so every statement is saved the moment it finishes.

import sqlite3

db = sqlite3.connect(':memory:', isolation_level=None)
db.execute('CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)')
db.execute("INSERT INTO accounts VALUES ('A', 500), ('B', 100)")

def total():
    return db.execute('SELECT SUM(balance) FROM accounts').fetchone()[0]

def crash():
    raise RuntimeError('server crashed')

try:
    db.execute("UPDATE accounts SET balance = balance - 200 WHERE name = 'A'")
    crash()
    db.execute("UPDATE accounts SET balance = balance + 200 WHERE name = 'B'")
except RuntimeError as err:
    print('Error:', err)

print(db.execute('SELECT name, balance FROM accounts ORDER BY name').fetchall())
print('Total:', total())

Two statements, no transaction: the crash lands between them

The bank started with 600 and now holds 400. The 200 that left A never arrived at B. It is not in either account, and nothing in the data says where it went. This is the problem transactions solve.

Every transaction follows the same short life. You begin it, run your statements, and then end it in one of two ways. COMMIT keeps all the changes. ROLLBACK throws all of them away and returns the data to how it looked at the start.

A good way to picture this is a private draft. Between BEGIN and COMMIT you are editing a draft that only you can see. Other users keep reading the last published version. When you COMMIT, the draft is published in one step. If you ROLLBACK, the draft is thrown away and nobody ever saw it.

Here is the transfer again, wrapped in a transaction. It reuses db and total() from the earlier example. The first run crashes in the middle and rolls back. The second run finishes and commits.

def show():
    return db.execute('SELECT name, balance FROM accounts ORDER BY name').fetchall()

def transfer(amount, fail):
    db.execute('BEGIN')
    try:
        db.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'A'", (amount,))
        if fail:
            raise RuntimeError('server crashed')
        db.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'B'", (amount,))
        db.execute('COMMIT')
    except RuntimeError as err:
        db.execute('ROLLBACK')
        print('Rolled back:', err)

transfer(100, fail=True)
print(show())
transfer(100, fail=False)
print(show())
print('Total:', total())

Same two statements, now inside BEGIN ... COMMIT / ROLLBACK

After the failed transfer the balances are exactly what they were before it started. After the successful one, both changes are in place and the total has not changed. The earlier loss of 200 is still there, because a rollback only undoes the transaction it belongs to.

Crashes are only half the story. Even when nothing fails, a database serves many users at once. Without transactions, another user could read your data in the middle of a multi-step change and see the debit without the matching credit. For a moment, money would seem to have disappeared, and a report or balance check run at that moment would be wrong.

Transactions also solve this. Changes made inside a transaction stay private until COMMIT, so everyone else sees either the old state or the new state, never something in between. The next example uses two separate connections to the same database file, a writer and a reader. The reader looks at account A before and after the writer commits.

import os, sqlite3, tempfile

with tempfile.TemporaryDirectory() as folder:
    path = os.path.join(folder, 'bank.db')
    writer = sqlite3.connect(path, isolation_level=None)
    writer.execute('PRAGMA journal_mode=WAL')
    writer.execute('CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)')
    writer.execute("INSERT INTO accounts VALUES ('A', 500)")
    reader = sqlite3.connect(path)
    sql = "SELECT balance FROM accounts WHERE name = 'A'"

    writer.execute('BEGIN')
    writer.execute("UPDATE accounts SET balance = 300 WHERE name = 'A'")
    print('Writer sees:', writer.execute(sql).fetchone()[0])
    print('Reader sees before COMMIT:', reader.execute(sql).fetchone()[0])
    writer.execute('COMMIT')
    print('Reader sees after COMMIT:', reader.execute(sql).fetchone()[0])
    writer.close()
    reader.close()

The draft is visible only to its author until COMMIT

The writer sees its own draft straight away. The reader keeps seeing the published value of 500 until the COMMIT, and then sees 300. How the database keeps drafts apart, and how strictly it does so, depends on the isolation level. A later section covers that.

Not every statement needs a transaction. A single statement is already atomic on its own: the database finishes it completely or undoes it completely. The next example runs one UPDATE over two rows. A CHECK rule forbids negative balances, so the second row breaks the rule and the whole statement is rejected. The first row's change is rolled back with it.

import sqlite3

w = sqlite3.connect(':memory:', isolation_level=None)
w.execute('CREATE TABLE wallets (name TEXT, balance INTEGER CHECK (balance >= 0))')
w.execute("INSERT INTO wallets VALUES ('A', 500), ('B', 100)")

try:
    w.execute('UPDATE wallets SET balance = balance - 200')
except sqlite3.IntegrityError:
    print('Statement failed')

print(w.execute('SELECT name, balance FROM wallets ORDER BY name').fetchall())

One statement, two rows, nothing half-applied

A transaction is needed when two or more statements must succeed together. If the second statement could fail or be interrupted after the first one has already been saved, you have the bank-transfer problem again.

Several words come up again and again in this chapter, so here they are in one place.

A transaction is a group of statements that the database treats as one unit. You open it with BEGIN;, and you close it in exactly one of two ways: COMMIT; to keep the work, or ROLLBACK; to throw it away. Everything between the opening and the closing is provisional.

The standard way to open a transaction is START TRANSACTION;, and both MySQL and Postgres accept it. Both also accept the shorter BEGIN;. They mean the same thing here, so pick one and use it consistently.

After COMMIT or ROLLBACK the transaction is over. The next statement you run starts a fresh piece of work, and nothing from the earlier transaction can be undone or redone.

The classic example is moving 100 from account 1 to account 2. It takes two updates, one to take money out and one to put it in. If only one of them happened, money would vanish or appear from nowhere, so both must be wrapped in a single transaction.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Both updates become permanent together, at COMMIT.

Before you type COMMIT, it is good practice to look at what you are about to make permanent. Run a SELECT on both accounts inside the same transaction. If the numbers are wrong, you can still ROLLBACK and nothing has been harmed.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SELECT id, balance FROM accounts WHERE id IN (1, 2);

Still inside the transaction: check before you commit.

Here the balances started at 500 and 600, so 400 and 700 is exactly what a correct transfer should produce. The total is still 1100. Now COMMIT; is safe. Had something looked off, ROLLBACK; would put both accounts back to 500 and 600.

While a transaction is open, your own session sees its own uncommitted changes. That is why the SELECT in the previous example showed 400 and 700. Other sessions, at the default isolation levels, do not see those changes. They keep seeing the last committed values until you COMMIT.

This is what makes a transaction safe to build up gradually. Nobody ever observes the half-finished state where account 1 has paid but account 2 has not been credited yet.

If you close the connection without issuing COMMIT, the database rolls the transaction back. A terminal that crashes, a script that dies or a client that disconnects mid-way will all leave the data as it was before BEGIN. Committing is something you must do on purpose.

Two behaviours differ between the databases, and both can surprise you. The first is what happens after an error. In Postgres, if any statement fails inside a transaction, the transaction is marked as aborted. From then on the only command it accepts is ROLLBACK; until you end it. Every other statement is refused.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT * FROM acounts;
-- ERROR: relation "acounts" does not exist
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;

A typo aborts the whole transaction in Postgres. Even the valid second UPDATE is refused.

Notice that the first UPDATE is undone too, because ROLLBACK discards everything since BEGIN. Even if you type COMMIT; on an aborted transaction, Postgres treats it as a rollback and nothing is saved.

The second difference is DDL, the statements that change structure, such as CREATE TABLE and ALTER TABLE. MySQL causes an implicit commit when it meets DDL: everything you did so far is committed on the spot, and you can no longer roll it back. Postgres supports transactional DDL, so a table you create inside a transaction disappears if you roll back.

So far a transaction has been something you open yourself with BEGIN. But what happens when you never type BEGIN at all? The database still wraps your work in a transaction. In autocommit mode, every single statement is treated as its own tiny transaction: it starts when the statement starts and commits the moment the statement finishes.

This is the normal state of affairs. Postgres, MySQL and SQL Server all start a plain interactive session (psql, the mysql client, SSMS or sqlcmd) with autocommit on. That is why a lone INSERT you type at the prompt is immediately visible to everyone else, with no extra step.

Autocommit is convenient for single, self-contained changes. It becomes a problem the moment a job needs two or more statements to succeed or fail together, such as the bank transfer from earlier, because each statement would already be permanent before the next one runs.

There are two ways to get out of one-statement-one-commit. The first is temporary and is the one you will use most: typing BEGIN pauses autocommit for that one transaction. Everything after it stays pending until you end it with COMMIT or ROLLBACK, and then the session goes back to autocommit.

UPDATE accounts SET balance = balance - 50 WHERE id = 1;  -- committed at once

BEGIN;                       -- autocommit paused
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;                      -- both become permanent together

UPDATE accounts SET balance = 0 WHERE id = 3;  -- autocommit is back

The first and last statements commit by themselves; the middle two commit as a pair.

The second way is a session setting. In MySQL, SET autocommit = 0; turns autocommit off for the whole connection. From then on a transaction is always open: the first statement starts one, and it stays open after each statement until you run COMMIT or ROLLBACK, at which point the next statement silently starts a fresh one.

SET autocommit = 0;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- nothing is saved yet, even though no BEGIN was typed
COMMIT;                      -- now it is saved
SET autocommit = 1;          -- back to the default

MySQL session-level switch.

Application drivers flip the default. Python drivers such as psycopg2 (Postgres) and sqlite3 open a transaction implicitly the first time you run a data-changing statement, and they keep it open until you call conn.commit(). You never write BEGIN, but you must write the commit yourself.

The example below uses two connections to the same file. The writer changes a price but has not committed, so the reader still sees the old value. Only after commit() does the new value appear.

Autocommit has a sharp edge. Suppose you meant to update one row but forgot the WHERE clause. With autocommit on, that statement commits the instant it finishes, across every row in the table, and there is no transaction left to roll back.

The safe habit is to wrap risky changes in an explicit transaction and look at the damage before you decide. Open with BEGIN, run the statement, check how many rows it reported touching, and only then choose COMMIT or ROLLBACK.

Here the same habit is applied in Python. The forgotten WHERE touches three rows when only one was expected, so the code rolls back and the table is untouched.

So far a transaction has been all or nothing: ROLLBACK throws away everything since BEGIN. Sometimes that is too blunt. Suppose one step in the middle of a long transaction fails, but the work before it is still good. A savepoint lets you undo only the recent part and keep going.

SAVEPOINT name; puts a named marker at the current position inside an open transaction. It does not save or commit anything. It only remembers where you were, like a bookmark in the transaction's history.

Three statements make up the whole feature. ROLLBACK TO SAVEPOINT name; undoes everything done after the marker, but the transaction stays open and you can carry on. RELEASE SAVEPOINT name; throws away the marker itself and keeps all the changes. Nothing you did is undone.

BEGIN;
  -- ...some work...
  SAVEPOINT s1;
  -- ...risky work...
  ROLLBACK TO SAVEPOINT s1;   -- undo only the risky work
  -- ...more work, still inside the same transaction...
COMMIT;

The general shape. SAVEPOINT can be written with or without the word SAVEPOINT after TO; ROLLBACK TO s1 means the same thing.

Here is the classic use. A shop saves an order, then tries to add an optional gift record. If the gift cannot be added, we still want the order. Without a savepoint we would have to abort the whole transaction and lose the order too.

Below is the same sequence run for real with Python's built-in SQLite. The connection is set to manual mode (isolation_level=None) so that our own BEGIN and COMMIT are the only ones in play.

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT)")
db.execute("CREATE TABLE gifts (id INTEGER PRIMARY KEY, order_id INTEGER, note TEXT)")

def count(table):
    return db.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]

db.execute("BEGIN")
db.execute("INSERT INTO orders (item) VALUES ('book')")
db.execute("SAVEPOINT s1")
db.execute("INSERT INTO gifts (order_id, note) VALUES (1, 'gift wrap')")
print("gifts before rollback:", count("gifts"))
db.execute("ROLLBACK TO s1")
print("gifts after rollback:", count("gifts"))
print("still in transaction:", db.in_transaction)
db.execute("COMMIT")
print("committed orders:", count("orders"), "gifts:", count("gifts"))

The gift is visible until the rollback, then gone. The order survives.

Savepoints nest. You can set s1, do some work, set s2, do more work, and so on. They form a stack. If you roll back to an earlier savepoint, every savepoint created after it is dropped along with the work, because the history they pointed into no longer exists.

In the next program we set s1 and s2, then roll back to s1. The row c added after s2 and the row b added after s1 both vanish. Trying to use s2 afterwards fails because the marker is gone. Then we release s1 and commit.

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE t (v TEXT)")

db.execute("BEGIN")
db.execute("INSERT INTO t VALUES ('a')")
db.execute("SAVEPOINT s1")
db.execute("INSERT INTO t VALUES ('b')")
db.execute("SAVEPOINT s2")
db.execute("INSERT INTO t VALUES ('c')")

db.execute("ROLLBACK TO s1")
print("after rollback to s1:", [r[0] for r in db.execute("SELECT v FROM t")])
try:
    db.execute("ROLLBACK TO s2")
except sqlite3.OperationalError as e:
    print("s2 is gone:", e)

db.execute("RELEASE s1")
db.execute("COMMIT")
print("committed:", [r[0] for r in db.execute("SELECT v FROM t")])

Rolling back to s1 drops s2 as well as the work after s1.

Notice that RELEASE s1 did not undo or save anything. It only removed the bookmark. The row a was always part of the outer transaction, and it became permanent only at COMMIT.

In PostgreSQL, any error inside a transaction puts it into an aborted state. Every later statement fails with "current transaction is aborted, commands ignored until end of transaction block", and only ROLLBACK is accepted. A savepoint is the way out: ROLLBACK TO SAVEPOINT undoes the failed statement and returns the transaction to a healthy state, so the work before the savepoint survives.

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 100);
SAVEPOINT before_risky;
INSERT INTO accounts (id, balance) VALUES (1, 50);  -- duplicate key: error, transaction aborted
ROLLBACK TO SAVEPOINT before_risky;                -- transaction is usable again
INSERT INTO accounts (id, balance) VALUES (2, 50);
COMMIT;                                            -- keeps accounts 1 and 2

PostgreSQL: without the savepoint, the only choice after the error would be to roll back everything.

This is why savepoints appear in three everyday situations:

• Batch imports where bad rows are skipped: put a savepoint before each row, and roll back to it if the row fails, so one bad row does not discard thousands of good ones.
• Retrying one step: if a single statement fails for a recoverable reason, roll back to its savepoint and try again without redoing the earlier work.
• ORM nested transactions: many frameworks implement a transaction block inside another transaction block as a savepoint, so the inner block can fail and roll back alone.

Here is the import pattern. Each row gets its own savepoint. A duplicate email is rolled back and skipped, and the rest are loaded in one transaction.

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE users (email TEXT PRIMARY KEY)")

rows = ["a@x.com", "b@x.com", "a@x.com", "c@x.com"]
skipped = []

db.execute("BEGIN")
for email in rows:
    db.execute("SAVEPOINT row")
    try:
        db.execute("INSERT INTO users VALUES (?)", (email,))
    except sqlite3.IntegrityError:
        db.execute("ROLLBACK TO row")
        skipped.append(email)
    db.execute("RELEASE row")
db.execute("COMMIT")

print("loaded:", [r[0] for r in db.execute("SELECT email FROM users ORDER BY rowid")])
print("skipped:", skipped)

One bad row costs only itself.

The idea is the same everywhere, but the spelling is not. Most databases (PostgreSQL, MySQL, SQLite, Oracle) follow the standard SAVEPOINT form. SQL Server is the outlier: it uses SAVE TRANSACTION to set a marker and ROLLBACK TRANSACTION name to go back to it, and it has no RELEASE.

BEGIN TRANSACTION;
  INSERT INTO orders (item) VALUES ('book');
  SAVE TRANSACTION s1;
  INSERT INTO optional_gift (order_id, note) VALUES (1, 'gift wrap');
  ROLLBACK TRANSACTION s1;   -- undoes the gift only
COMMIT TRANSACTION;

SQL Server version of the order example. A bare ROLLBACK TRANSACTION with no name would undo everything.

ACID is the name for four guarantees a database makes about a transaction: Atomicity, Consistency, Isolation and Durability. The easiest way to see all four is a bank transfer. Account A holds 500 and account B holds 200. We move 100 from A to B. In SQL that is two statements: one that subtracts 100 from A, and one that adds 100 to B.

Neither statement is meaningful alone. If only the debit happens, 100 has vanished. If only the credit happens, 100 has appeared from nowhere. A transaction wraps both statements so the database treats them as one unit of work, and ACID describes what that unit is promised.

Atomicity means the transfer is indivisible. Observers outside the transaction can only ever see the state before it (A=500, B=200) or the state after it (A=400, B=300). The program below uses Python's built-in sqlite3. The with db: block commits if it finishes normally and rolls back if an error escapes. The second transfer tries to move 900, which would push A below zero, so the database rejects the debit and the whole transaction is undone.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (id TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
db.executemany("INSERT INTO accounts VALUES (?, ?)", [("A", 500), ("B", 200)])
db.commit()

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

def transfer(src, dst, amount):
    try:
        with db:
            db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))
            db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
        print("committed")
    except sqlite3.IntegrityError:
        print("rolled back")

transfer("A", "B", 100)
transfer("A", "B", 900)
print(dict(db.execute("SELECT id, balance FROM accounts")), total())

One transfer commits, one is rejected and leaves no trace

Notice that the total is still 700 after both attempts. The rejected transfer left nothing behind, not even a half-applied change.

Consistency says a transaction moves the database from one valid state to another valid state. For a bank, validity means rules such as balance >= 0 and the total money in the system does not change during a transfer. The database does not invent these rules. You declare them, and the engine refuses to commit any transaction that would break one.

Different rules are enforced by different tools, and some are not enforceable by the engine at all. The table shows the split.

CREATE TABLE accounts (
  id      TEXT PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transfers (
  id      TEXT UNIQUE NOT NULL,
  src     TEXT NOT NULL REFERENCES accounts(id),
  dst     TEXT NOT NULL REFERENCES accounts(id),
  amount  INTEGER NOT NULL CHECK (amount > 0)
);

Constraints turn business rules into things the engine checks for you

Real systems run many transactions at once. Isolation means a concurrent transfer or a report does not see the money as missing in the middle of someone else's transfer. Between the debit and the credit, the writer's own view shows a total of 600. Other transactions are supposed to keep seeing 700 until the commit lands. The next example uses two connections to the same database file, one writing and one running a report.

import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "bank.db")
writer = sqlite3.connect(path)
reader = sqlite3.connect(path)
writer.execute("CREATE TABLE accounts (id TEXT PRIMARY KEY, balance INTEGER)")
writer.executemany("INSERT INTO accounts VALUES (?, ?)", [("A", 500), ("B", 200)])
writer.commit()

report = "SELECT SUM(balance) FROM accounts"

writer.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 'A'")
print("writer sees:", writer.execute(report).fetchone()[0])
print("report mid-transfer:", reader.execute(report).fetchone()[0])
writer.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 'B'")
writer.commit()
print("report after commit:", reader.execute(report).fetchone()[0])

The report never sees the 100 that is in flight

Perfect isolation would make every transaction behave as if it ran alone, one after another. That is safe but expensive, because transactions have to wait for each other. So databases offer isolation levels. A weaker level lets more transactions run side by side but allows some odd readings. A stronger level removes those readings and costs concurrency. Later sections name the specific anomalies and compare the levels in detail.

Durability is the promise that once COMMIT has returned successfully, the transfer survives a power loss, an operating system crash or a process kill. The customer was told the money moved, so it must stay moved.

Memory is lost on a crash, and writing every changed data page to its final place on disk would be slow. Databases solve this with a write-ahead log: a sequential file that records what each transaction changed. The key rule is that the log is flushed to disk before the engine reports success. The data files can be brought up to date later, because the log is enough to rebuild them.

The order matters. If the engine told the client "committed" first and flushed afterwards, a crash in between would lose a transfer the customer had already been told about. Many engines let you loosen the flush for speed, for example SQLite's PRAGMA synchronous or PostgreSQL's synchronous_commit. Doing so trades durability for throughput, so the last few commits may vanish after a crash.

Now combine the letters. Suppose the server crashes after the debit of A but before the credit of B. The debit may even be in the log. Because no commit record was written for this transaction, the engine treats it as unfinished on restart and undoes it. That is atomicity doing its job after a crash, and it is what protects the total. The program below simulates the crash with an exception. It reuses db, total and the accounts from the first example.

def crashing_transfer(src, dst, amount):
    try:
        with db:
            db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))
            seen = db.execute("SELECT balance FROM accounts WHERE id = ?", (src,)).fetchone()[0]
            print("after debit, inside the transaction:", seen)
            raise RuntimeError("crash before the credit")
            db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
    except RuntimeError as e:
        print("restart:", e)

crashing_transfer("A", "B", 100)
print(dict(db.execute("SELECT id, balance FROM accounts")), total())

The debit is undone because the transaction never committed

It is tempting to read ACID as a label that makes any database safe. It is not. ACID is what you get when a capable engine meets code that uses transactions properly. The engine supplies the log, the locking or versioning and the constraint checks. You supply the transaction boundaries, the declared rules and the choice of isolation level.

The bank transfer in the last section promised that money is never half-moved and that a committed transfer survives a power cut. A database keeps those promises with two mechanisms: a log that records changes, and row versions that let many transactions look at the same data at once. This page covers the log. The next page covers versions.

The table's data lives in fixed-size pages scattered across the disk. Updating a page in place means random writes, which are slow, and a crash in the middle of one can leave a page torn. So the database never relies on page writes alone. Before it changes a data page, it appends a small description of the change to the write-ahead log (WAL). The rule is simple: the log record reaches disk before the data page it describes.

The log is only ever appended to, so writing it is one fast sequential stream. The changed data page can stay in memory as a dirty page and be written out later, whenever it is convenient.

Writing to a file normally only hands bytes to the operating system, which may keep them in memory for seconds. A call to fsync forces them onto the physical disk. When you run COMMIT, the database appends the commit record and calls fsync on the log before it answers. If you got a success message, the commit record is on disk, and that is what durability means.

That fsync is the expensive part of a commit. Postgres lets you skip the wait with synchronous_commit. With it off, COMMIT returns as soon as the record is in memory and the log is flushed a moment later in the background. Commits get much faster. The cost is that a crash can lose the last fraction of a second of transactions that were already reported as committed. The database stays consistent, because those transactions simply vanish whole, but they are gone.

-- only for this session or transaction; the default is on
SET synchronous_commit = off;

BEGIN;
INSERT INTO page_views (url) VALUES ('/home');
COMMIT;  -- returns before the log reaches the disk

Acceptable for page-view counters, not for bank transfers.

After a crash, the data pages on disk are a mix. Some changes from committed transactions never reached them, and some changes from unfinished transactions might have. On restart the database reads the log and fixes this in two moves. Redo replays logged changes so that everything committed is present. Undo reverses changes from transactions that have no commit record. After both, the data matches what committed transactions promised and nothing more.

The toy program below shows the idea with the transfer from earlier. Transaction 1 moved 30 from alice to bob and committed. Transaction 2 was moving 50 out of alice when the power failed. The crash left a page on disk with transaction 2's uncommitted value. Each log entry holds the transaction id, the account, the old value and the new value.

disk = {'alice': 20, 'bob': 50}  # pages as they were on disk at the crash
log = [
    ('BEGIN', 1),
    ('SET', 1, 'alice', 100, 70),
    ('SET', 1, 'bob', 50, 80),
    ('COMMIT', 1),
    ('BEGIN', 2),
    ('SET', 2, 'alice', 70, 20),
]

for rec in log:                      # redo: repeat history in log order
    if rec[0] == 'SET':
        disk[rec[2]] = rec[4]
print('after redo:', disk)

committed = {r[1] for r in log if r[0] == 'COMMIT'}
unfinished = {r[1] for r in log if r[0] == 'BEGIN'} - committed
for rec in reversed(log):            # undo: newest change first
    if rec[0] == 'SET' and rec[1] in unfinished:
        disk[rec[2]] = rec[3]
print('after undo:', disk)

A toy version of recovery. Real engines also track log positions per page.

Bob's 80 was missing from the disk and redo brought it back. Alice's 20 was on disk without a commit and undo took it out. Transaction 1 is fully applied and transaction 2 left no trace.

The log gives atomicity in one of two ways. Some engines keep undo records and run them backwards on ROLLBACK or after a crash, as the toy program did. Other engines, Postgres among them, never overwrite a row in place. An update writes a new row version that is marked with the id of the transaction that wrote it, and a rollback or crash just leaves that transaction unrecorded as committed. Its versions stay in the table but are hidden from everyone, so there is nothing to undo. Either way, a half-done transfer never becomes visible.

The log answers what happens when things crash. A second question is what happens when many transactions run at once. If every read had to wait for every write on the same row, a busy database would crawl. MVCC (multi-version concurrency control) avoids this by keeping more than one version of a row. A writer creates a new version and a reader keeps looking at the old one, so readers do not block writers and writers do not block readers.

Every transaction gets an increasing transaction ID. Each row version records which transaction created it (often called xmin) and which transaction deleted or replaced it (xmax). A transaction reads through a snapshot: a note of which transaction IDs had committed when it started, or when the statement started, depending on the isolation level. A version is visible only if its creator is in the snapshot and its deleter is not.

The toy below keeps three versions of alice's balance. Transaction 5 inserted 100. Transaction 9 replaced it with 70 and committed. Transaction 11 is still running and has started replacing 70 with 20. Three readers with different snapshots each see one consistent answer. The committed set stands in for the commit log.

versions = [
    {'balance': 100, 'xmin': 5, 'xmax': 9},
    {'balance': 70, 'xmin': 9, 'xmax': 11},
    {'balance': 20, 'xmin': 11, 'xmax': None},
]
committed = {5, 9}  # transaction 11 has not committed

def visible(v, snap):
    created = v['xmin'] <= snap and v['xmin'] in committed
    gone = v['xmax'] is not None and v['xmax'] <= snap and v['xmax'] in committed
    return created and not gone

def read(snap):
    return [v['balance'] for v in versions if visible(v, snap)]

for snap in (7, 9, 12):
    print('snapshot', snap, 'sees', read(snap))

Snapshot N means: transactions up to N that have committed count.

The reader at snapshot 12 never sees the 20 because transaction 11 has not committed, and it was never blocked by transaction 11 either. If transaction 11 rolls back, its version stays hidden. If it commits, later snapshots will see 20.

Old versions have to be stored somewhere, and the two big open-source engines chose differently. Postgres keeps old versions in the table itself, next to the live ones. This makes rollback instant, but dead versions pile up as bloat until VACUUM removes the ones no snapshot can see anymore. Autovacuum does this in the background. MySQL's InnoDB updates the row in place and keeps the previous values in undo logs. A reader that needs an old version rebuilds it by walking the undo chain backwards.

-- Postgres: reclaim space from dead row versions
VACUUM (VERBOSE) accounts;

Autovacuum normally runs this for you.

If the log were never trimmed, recovery after a crash would replay every change since the database was created. A checkpoint prevents that. At a checkpoint the database writes all dirty pages in memory out to the data files, records in the log that everything before this point is safely on disk, and then older log segments can be reused or removed. Recovery then only starts from the last checkpoint.

Checkpoints trade write work now for a quicker restart later. Frequent checkpoints keep the log short and recovery fast, but they cause more disk writes, and the first change to a page after a checkpoint is logged in full in Postgres, which adds log volume. Rare checkpoints are cheaper at runtime but a crash means a long replay. Postgres triggers one every checkpoint_timeout (five minutes by default) or when the log grows past max_wal_size, whichever comes first.

A transaction on its own is simple. Trouble starts when several transactions run at the same time and touch the same data. The database interleaves their steps, and if nothing stops it, one transaction can see things that never made sense from its own point of view. These surprises are called concurrency anomalies. This section names each one and walks through a timeline that produces it.

The first three anomalies are all about reads. A transaction reads something, and what it sees is either not real yet, or changes underneath it.

A dirty read happens when a transaction reads a change that another transaction has made but not yet committed. If that other transaction later rolls back, the reader has seen data that officially never existed.

Here T1 writes a balance of 500 and then changes its mind. In between, T2 reads the 500 and acts on it. Read the table top to bottom as time passing.

A non-repeatable read means you read the same row twice inside one transaction and get different values, because another transaction committed an update to that row between your two reads. Unlike a dirty read, the other change here is committed and real. The problem is that your own transaction no longer sees a stable picture.

Nothing was undone and nobody did anything wrong, yet T1 asked the same question twice and got two answers. A report that adds up figures across several reads of the same row can end up with totals that do not match.

A phantom read is the range-query version of the same problem. You run a query that selects a set of rows by a condition, run it again in the same transaction, and the set itself has changed: new rows appear, or old ones vanish, because another transaction committed an insert or delete that matches your condition.

No row that T1 read earlier was modified. A brand-new row simply walked into the range, like a phantom appearing from nowhere.

The two look alike, so it helps to separate them by what the other transaction touched. A non-repeatable read is about rows that already existed and were read. A phantom is about the result set of a condition, which can grow or shrink even when every row you saw earlier is untouched.

The anomalies so far were about reads. The next two come from read-then-write patterns, where a transaction reads something, thinks about it, and writes based on what it saw. Both can happen even when no dirty read is involved.

A lost update occurs when two transactions read the same value, each compute a new value from it, and both write. The second write overwrites the first, so one change is silently lost. Imagine a balance of 100 where two deposits of 10 and 20 arrive together.

Both transactions behaved correctly on the data they saw, yet the result is wrong. The usual cure is to let the database do the arithmetic in a single statement, such as UPDATE account SET balance = balance + 10, or to lock the row when you read it.

Write skew is the trickiest of the group. Two transactions read an overlapping set of rows, check a rule, and then each write to a different row. Neither overwrites the other, so there is no lost update, but together they break a rule that each one checked separately.

The classic story: a hospital requires at least one doctor to be on call. Alice and Bob are both on call. Each checks that the other is on call, sees it is safe to leave, and goes off call.

Here is the whole family side by side. The next section shows which isolation levels prevent each one, so keep this table in mind as the vocabulary for that comparison.

The previous section listed the anomalies that concurrent transactions can cause. Isolation levels are the dial the SQL standard gives you to decide which of those anomalies your transaction is willing to put up with. Each level is a promise about what your transaction can see while other transactions are running at the same time.

The standard defines four levels. Moving up the list, each level forbids one more kind of anomaly than the one before it. The cost of that extra safety is paid in waiting, aborted transactions and retries, which later pages cover.

The standard describes each level by which anomalies it allows. Read the table below as the minimum a level must prevent. Y means the anomaly can still happen, N means the level prevents it.

READ UNCOMMITTED is the weakest level. It allows dirty reads, meaning you can see changes another transaction has not committed yet. If that transaction rolls back, you acted on data that never officially existed. Many engines refuse to offer this much weakness: PostgreSQL treats READ UNCOMMITTED exactly like READ COMMITTED, so asking for it never gives you dirty reads there.

READ COMMITTED removes dirty reads. You only ever see data that was committed. The catch is that every statement gets a fresh look at the database, so two identical SELECTs inside one transaction can return different answers if someone commits in between. This is the default in PostgreSQL and SQL Server.

-- Session A (READ COMMITTED)        -- Session B
BEGIN;
SELECT balance FROM accounts
  WHERE id = 1;          -- 500
                                      UPDATE accounts SET balance = 400
                                        WHERE id = 1;
                                      COMMIT;
SELECT balance FROM accounts
  WHERE id = 1;          -- 400, a fresh view
COMMIT;

The same query gives two answers inside one transaction

REPEATABLE READ fixes that drift. The transaction takes a snapshot when its first statement runs, and every later read comes from that same snapshot. Rows you have read will not change under you, so non-repeatable reads are gone. This is the default in MySQL InnoDB.

SERIALIZABLE is the strongest level. The result must be the same as if the transactions had run strictly one after another in some order, even though they actually overlap. It blocks every anomaly in the table, including phantoms, where a repeated range query suddenly returns extra rows.

The table on the first page is the standard's minimum, not a description of every database. Engines are free to prevent more than the standard requires, and most do. Treat the names as a rough guide and check your engine's documentation before relying on a specific behaviour.

Setting a level is a single statement. In PostgreSQL it must come right after BEGIN, before the transaction runs its first query, because the snapshot is fixed once a query has run. You can also pass the level directly to BEGIN.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT SUM(balance) FROM accounts;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

-- Same thing in one line
BEGIN ISOLATION LEVEL SERIALIZABLE;

Choose the level before the first query

Stronger isolation is not free. Higher isolation means fewer anomalies, but more blocking, more aborted transactions and more retries. The database has to either make transactions wait for each other or cancel one of them when they conflict. Throughput drops as you climb the ladder.

In PostgreSQL, SERIALIZABLE does not make transactions wait. It lets them run and then watches for patterns that could not have happened in any one-at-a-time order. When it finds one, it aborts a transaction with a serialization failure, SQLSTATE 40001. Nothing is wrong with your SQL. The application must simply run the whole transaction again.

A retry loop wraps the entire transaction, not a single statement, because the aborted attempt left nothing behind. This runnable sketch fakes two failures before the third attempt succeeds.

So which level should you pick? Start by naming the invariant you must protect, such as a balance that must never go negative or a seat that must never be sold twice. Then use the lowest level that keeps that invariant safe. Most reads are fine on the default. Reach for REPEATABLE READ when a report needs a consistent view, and SERIALIZABLE only for logic that is hard to protect any other way.

Isolation levels describe what a transaction is allowed to see. Locks are one of the tools a database uses to enforce that. A lock is a note on a piece of data saying who is using it and how. Before a transaction touches a row, the database checks that note. If another transaction holds a conflicting lock, the newcomer has to wait.

Locks come in two basic modes. A shared lock (also called a read lock) says 'I am reading this, please don't change it'. Any number of transactions can hold a shared lock on the same row at once. An exclusive lock (a write lock) says 'I am changing this, keep out'. Only one transaction can hold it, and it blocks everyone else, readers and writers alike.

The practical rule is the one the table shows: readers don't block readers, but a writer blocks everybody. Some engines, such as Postgres and InnoDB with MVCC, let a plain SELECT read an older snapshot without taking a shared lock at all. Writers still take exclusive locks, which is why the next point matters.

When a transaction runs UPDATE, DELETE or INSERT, it takes exclusive locks on the rows it touched. It does not give them back when the statement finishes. It keeps them until it ends with COMMIT or ROLLBACK. Any other transaction that wants to change those same rows simply waits in line.

SQLite is a handy way to watch this, because it is in the standard library. It locks the whole database file for a writer rather than single rows, but the waiting behaviour is the same. Connection a opens a write transaction and has not committed. Connection b tries to write and gives up after 0.2 seconds.

import sqlite3, tempfile, os

path = os.path.join(tempfile.mkdtemp(), 'bank.db')
a = sqlite3.connect(path, timeout=0.2, isolation_level=None)
b = sqlite3.connect(path, timeout=0.2, isolation_level=None)

a.execute('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)')
a.execute('INSERT INTO accounts VALUES (1, 100), (2, 50)')

a.execute('BEGIN IMMEDIATE')
a.execute('UPDATE accounts SET balance = balance - 10 WHERE id = 1')

try:
    b.execute('UPDATE accounts SET balance = balance + 5 WHERE id = 2')
except sqlite3.OperationalError as e:
    print('B waited and gave up:', e)

a.execute('COMMIT')
b.execute('UPDATE accounts SET balance = balance + 5 WHERE id = 2')
print(b.execute('SELECT id, balance FROM accounts ORDER BY id').fetchall())

B is blocked until A commits

Once a committed, its lock disappeared and the same statement from b went straight through. A server database like Postgres or MySQL would not even have blocked b here, because the two updates touch different rows. That difference is the next topic.

Updates lock rows automatically, but a read does not. That creates a classic trap called check-then-update. You read a balance, decide in your application that there is enough money, then write the new balance. Between your read and your write, another transaction can change that balance, and your decision is based on stale data.

SELECT ... FOR UPDATE closes the gap. It reads the rows and takes exclusive locks on them in the same step, so nobody else can change them until you commit or roll back. Anyone else who runs FOR UPDATE on the same row waits, then sees your committed result.

BEGIN;

-- lock the row while we look at it
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- the application sees 100 and decides 30 is affordable
UPDATE accounts SET balance = balance - 30 WHERE id = 1;

COMMIT;  -- the lock is released here

No other transaction can change account 1 between the check and the update

Locks can be taken at different sizes. A row lock protects only the rows you touched, so two transactions updating different rows never get in each other's way. A table lock protects the whole table: simple and cheap for the database to track, but every other writer has to wait. Engines use table locks for operations that really do affect everything, such as ALTER TABLE or LOCK TABLE.

Waiting for a lock is normal and usually short. A deadlock is when the waiting can never end. Transaction T1 holds the lock on row A and wants row B. Transaction T2 holds the lock on row B and wants row A. Each is waiting for the other to commit, and neither can commit until it gets what it is waiting for.

This happens easily with transfers. One person sends money from A to B while someone else sends from B to A at the same moment, and each transaction locks its source row first.

You don't have to detect deadlocks yourself. The database watches who is waiting for whom, and when it finds a cycle it picks one transaction as the victim. The victim is aborted with an error, its changes are rolled back and its locks are released. That frees the other transaction, which carries on and commits normally.

In Postgres the victim sees deadlock detected (SQLSTATE 40P01). In MySQL InnoDB it is error 1213, Deadlock found when trying to get lock; try restarting transaction. The right response in application code is to catch that error and retry the whole transaction from BEGIN.

Detection is a safety net, not a plan. The best fix is to make cycles impossible. If every transaction takes its locks in the same order, nobody can hold B while waiting for A, because whoever wants both asks for A first. For transfers, a simple rule works: always lock the lower account id first, no matter which direction the money moves.

BEGIN;

-- lock both accounts, lowest id first, in one statement
SELECT id FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;

COMMIT;

A transfer from 2 to 1 runs the same SELECT, so it queues instead of deadlocking

• Access rows in a consistent order, such as ascending primary key, in every code path.
• Keep transactions short: do the slow work (API calls, user input, big computations) before BEGIN or after COMMIT.
• Commit quickly: locks live until the transaction ends, so every second you stay open is a second others may wait.
• Lock only what you need, and don't leave a transaction idle holding locks.

There are two ways to deal with the risk that two people change the same row. Pessimistic locking assumes conflicts will happen, so it locks first: SELECT ... FOR UPDATE, then work, then commit. Optimistic locking assumes conflicts are rare, so it takes no lock while reading. Instead the row carries a version column. The write only succeeds if the version is still the one you read, and if someone got there first you re-read and retry.

The optimistic pattern is easy to see in code. Both users read the account at version 1. Alice's write goes first and bumps the version to 2, so Bob's write, which still says version = 1, matches zero rows. Bob notices, re-reads the fresh balance and tries again.

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER, version INTEGER)')
conn.execute('INSERT INTO accounts VALUES (1, 100, 1)')

def read():
    return conn.execute('SELECT balance, version FROM accounts WHERE id = 1').fetchone()

def withdraw(amount, balance, version):
    cur = conn.execute(
        'UPDATE accounts SET balance = ?, version = version + 1 '
        'WHERE id = 1 AND version = ?', (balance - amount, version))
    return cur.rowcount == 1

alice = read()
bob = read()
print('alice saved:', withdraw(30, *alice))
print('bob first try:', withdraw(50, *bob))
bob = read()
print('bob retry:', withdraw(50, *bob))
print('balance, version:', read())

The WHERE version = ? check is the whole trick

A transaction that waits for a lock will, by default, wait for a long time. If the holder is stuck or forgot to commit, your request just hangs. A lock wait timeout puts a limit on it: after that many seconds the waiting statement fails with an error and you can decide what to do. Postgres calls the setting lock_timeout, and MySQL InnoDB calls it innodb_lock_wait_timeout.

-- Postgres: give up after 2 seconds of waiting for a lock
SET lock_timeout = '2s';

-- MySQL (InnoDB): give up after 5 seconds, for this session
SET SESSION innodb_lock_wait_timeout = 5;

A timed-out statement errors; roll back and retry or report a failure

When things hang, you can ask the database what it is holding. In Postgres, the pg_locks view lists every lock and whether it has been granted. A row with granted = false is a transaction in the waiting line. In MySQL, SHOW ENGINE INNODB STATUS prints a report that includes the current transactions, their locks and a LATEST DETECTED DEADLOCK section with both queries involved.

-- Postgres: who is waiting, and who is blocking them?
SELECT pid, locktype, mode, granted, pg_blocking_pids(pid) AS blocked_by
FROM pg_locks
WHERE NOT granted;

-- MySQL: transactions, locks and the last deadlock
SHOW ENGINE INNODB STATUS;

Start here when a query is stuck

Most transaction bugs are not exotic. They come from a few habits that look harmless on a developer laptop and then hurt under real traffic. The first two are about time: how long a transaction stays open, and what it does while it is open.

After BEGIN, every row you change stays locked until you end the transaction. If code returns early, hits an unhandled path or the session simply goes quiet, the transaction stays open. Other sessions that touch those rows wait behind it, and in PostgreSQL an open transaction also stops old row versions from being cleaned up. The tell-tale sign is a session that is idle in transaction.

-- PostgreSQL: find sessions that began a transaction and went quiet
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

A very old xact_start is a transaction somebody forgot to finish.

A transaction should be as short as the work it protects. The worst pattern is to open a transaction, change some rows and then wait for something slow: a user clicking Confirm, a payment API call, an email being sent. The locks are held for that entire wait, and nobody can predict how long it will be.

If the slow step must happen between two database steps, split the work into two short transactions and record the intermediate state in a column such as status = 'pending', rather than keeping one transaction open across the wait.

Many learners assume that a transaction is automatically isolated from every other one. In practice the default level is almost always READ COMMITTED, which still allows non-repeatable reads and lost updates. MySQL InnoDB is the usual exception, defaulting to REPEATABLE READ. Only SERIALIZABLE gives the illusion of running one transaction at a time, and you must ask for it.

-- PostgreSQL
SHOW transaction_isolation;
-- MySQL 8
SELECT @@transaction_isolation;

Check what you really have instead of guessing.

A very common shape is to SELECT a balance in application code, decide in Python or Java whether the withdrawal is allowed, and then UPDATE with the new number. Between the read and the write, another session can change the same row. Both sessions then write a value computed from the old balance, and one withdrawal vanishes. This is a lost update. The example below replays that interleaving: both sessions read first, then both write.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE account (id INTEGER PRIMARY KEY, balance INTEGER)')
db.execute('INSERT INTO account VALUES (1, 150)')

def read_balance():
    return db.execute('SELECT balance FROM account WHERE id = 1').fetchone()[0]

seen_by_a = read_balance()   # session A reads 150
seen_by_b = read_balance()   # session B reads 150 too

db.execute('UPDATE account SET balance = ? WHERE id = 1', (seen_by_a - 100,))
db.execute('UPDATE account SET balance = ? WHERE id = 1', (seen_by_b - 100,))

print('withdrawn: 200')
print('final balance:', read_balance())

Two withdrawals of 100 from 150 should be impossible.

The account lost 200 of money it never had, yet the balance only dropped by 100. Session B overwrote session A's result because it wrote a number it had calculated earlier.

The cure is to move the check and the arithmetic into a single statement: UPDATE account SET balance = balance - 100 WHERE id = 1 AND balance >= 100. The database evaluates the condition against the current row while holding its lock, so two sessions cannot both pass the check. The number of affected rows tells you whether the withdrawal happened.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE account (id INTEGER PRIMARY KEY, balance INTEGER)')
db.execute('INSERT INTO account VALUES (1, 150)')

def withdraw(amount):
    cur = db.execute(
        'UPDATE account SET balance = balance - ? WHERE id = 1 AND balance >= ?',
        (amount, amount),
    )
    return cur.rowcount

print('first withdrawal:', withdraw(100), 'row changed')
print('second withdrawal:', withdraw(100), 'rows changed')
print('balance:', db.execute('SELECT balance FROM account').fetchone()[0])

rowcount 0 means the guard refused the withdrawal.

Deadlocks and serialization failures are not bugs in the database. They are the database's way of saying that two transactions cannot both proceed, so it aborts one of them. In PostgreSQL these show up as SQLSTATE 40P01 and 40001, in MySQL as errors 1213 and 1205. The correct reaction is to roll back and run the whole transaction again, usually a few times with a short pause.

import time

class SerializationFailure(Exception):
    pass

attempts = 0

def transfer():
    global attempts
    attempts += 1
    if attempts < 3:
        raise SerializationFailure('could not serialize access')
    return 'committed'

def run_with_retry(fn, tries=5):
    for n in range(1, tries + 1):
        try:
            return fn()
        except SerializationFailure:
            print(f'attempt {n} failed, retrying')
            time.sleep(0.01 * n)
    raise RuntimeError('gave up')

print(run_with_retry(transfer))

Only retry errors that are safe to retry, and always cap the number of tries.

A frequent bug is a try/except that logs or ignores the error and then falls through to commit(). If an earlier statement in the transaction already succeeded, it gets committed on its own. In the transfer below, the credit to Bob works, the debit from Alice breaks a CHECK constraint, and the swallowed error leaves money created from nothing.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE acct (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))')
db.executemany('INSERT INTO acct VALUES (?, ?)', [('alice', 50), ('bob', 0)])
db.commit()

def balances():
    return dict(db.execute('SELECT name, balance FROM acct ORDER BY name'))

def move(amount):
    db.execute("UPDATE acct SET balance = balance + ? WHERE name = 'bob'", (amount,))
    db.execute("UPDATE acct SET balance = balance - ? WHERE name = 'alice'", (amount,))

try:
    move(80)
except sqlite3.IntegrityError:
    print('debit failed, ignoring')
db.commit()
print('swallowed:', balances())

db.execute("UPDATE acct SET balance = 0 WHERE name = 'bob'")
db.commit()
try:
    move(80)
    db.commit()
except sqlite3.IntegrityError:
    db.rollback()
    print('transfer refused')
print('rolled back:', balances())

The second version ends the transaction in the error path.

In MySQL, statements such as CREATE, ALTER, DROP and TRUNCATE cause an implicit commit. Everything you did before them in the transaction is made permanent at that moment, and a later ROLLBACK has nothing left to undo. PostgreSQL and SQL Server allow DDL inside a transaction and roll it back normally.

-- MySQL
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (7, 40);
ALTER TABLE orders ADD COLUMN note TEXT;   -- implicit COMMIT happens here
ROLLBACK;                                  -- too late: the INSERT is permanent

Run schema changes on their own, never in the middle of a data change.

It is tempting to wrap a whole request handler in one transaction: place the order, update the loyalty points, write an audit row, refresh a report table. But a transaction is a promise that a group of statements succeed or fail together. Statements with no such relationship only make it longer, widen the set of rows that are locked, and raise the chance of deadlocks. One slow or failing step then drags the others down with it.

The test is simple: if the second statement could fail and the first should still stand, they do not belong together. Keep the order and its stock deduction in one transaction because they must agree, and let the audit entry or the loyalty bonus run in its own.

A transaction groups several statements so they succeed or fail as one unit. You open it with BEGIN, and then you end it in exactly one of two ways: COMMIT makes every change permanent, and ROLLBACK throws every change away.

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both updates

The basic shape. SQL Server also accepts BEGIN TRANSACTION, and MySQL accepts START TRANSACTION.

A savepoint is a named marker inside an open transaction. SAVEPOINT s sets it, ROLLBACK TO s undoes everything done after the marker while keeping the transaction open, and RELEASE s forgets the marker once you no longer need it. Nothing is permanent until the final COMMIT.

The program below runs the whole cycle on an in-memory SQLite database. It moves 30 from account 1 to account 2, makes a mistaken update in between, and undoes only that mistake with a savepoint. A final transaction is rolled back completely.

ACID is the set of four promises a transaction makes. A bank transfer shows each one clearly.

Isolation is the promise that is relaxed most often, and relaxing it lets anomalies through. Each one is a different way that two overlapping transactions can produce a result no serial order would.

Higher isolation levels block more of these anomalies, at the cost of more waiting or more retries. Each database picks a different default, so the same code can behave differently when you move it.

When two transactions want the same row, the database makes one wait using a lock. You can ask for a lock yourself with SELECT ... FOR UPDATE, which locks the rows it returns until you commit or roll back. This is the standard fix for a lost update, because the second transaction has to wait and then sees the first one's result.

A deadlock is a circular wait. Transaction A holds row 1 and wants row 2, while transaction B holds row 2 and wants row 1, so neither can ever continue. The database detects the cycle and picks a victim: it aborts one transaction automatically so the other can finish.

Three rules keep this under control. Keep transactions short, so locks are held briefly. Take locks in the same order everywhere, for example always the lower account id first, so no cycle can form. And retry when the database aborts you, because a victim or a serialization failure is a normal event, not a bug.

The last example shows the checklist in action. The WHERE guard means an overdraft updates zero rows, the code checks the result before committing, and every path ends the transaction.

• Verify with a SELECT before COMMIT, so you see the result while you can still undo it.
• Add WHERE guards to every UPDATE and DELETE, and check how many rows changed.
• Always end the transaction with COMMIT or ROLLBACK, including on error paths.
• Keep it short, lock in a consistent order, and retry on 40001 or a deadlock.

Try each question before opening the answer. They ask you to predict what happens or to spot the bug, not to recite definitions.

• A transaction groups statements into one all-or-nothing unit: BEGIN, then COMMIT to keep the work or ROLLBACK to undo it.
• Savepoints give partial rollback inside an open transaction, but nothing is permanent until the outer COMMIT.
• ACID: atomic (all or nothing), consistent (rules hold before and after), isolated (concurrent work does not interfere), durable (committed data survives a crash).
• Isolation is a dial: READ COMMITTED is the Postgres default and REPEATABLE READ is the MySQL default. Higher levels block more anomalies at the cost of more waiting and retries.
• Know the anomalies: dirty read, non-repeatable read, phantom, lost update and write skew.
• Locks protect rows. Deadlocks are circular waits the database breaks by aborting one victim, so lock rows in a consistent order and retry on deadlock or serialization errors.
• Keep transactions short, guard updates with WHERE, check the row count before COMMIT, and always end every transaction.