Transactions, ACID, Isolation and Locking

A bKash transfer is two changes: money leaves one wallet and arrives in another. If the system crashes between them, money vanishes. If two transfers touch the same wallet at the same moment, money can be created out of nothing. A transaction is the database's answer to both: a group of statements that succeeds or fails as one unit, and that is protected from other sessions working at the same time.

AI systems hit these problems constantly, just with different nouns: an ETL load that must not leave half a day of data behind, several workers writing evaluation results at once, a per-user token budget decremented by parallel requests. This page shows each guarantee working — and failing — on real databases, including two live sessions on PostgreSQL that you step through by hand.

What you will learn

• BEGIN, COMMIT, ROLLBACK, savepoints, and what ACID promises
• Cascading foreign-key actions: CASCADE, SET NULL, RESTRICT
• Isolation levels and the anomalies they allow: dirty, non-repeatable and phantom reads, lost updates
• Locks, SELECT … FOR UPDATE, deadlocks, and optimistic vs pessimistic locking
• Atomic updates and retrying failed transactions safely

BEGIN, COMMIT, ROLLBACK

Make two wallets in shop.db. The CHECK stops a balance going negative:

CREATE TABLE wallets (
    wallet_id INTEGER PRIMARY KEY,
    owner     TEXT NOT NULL,
    balance   INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO wallets VALUES (1, 'Nadia', 5000), (2, 'Tanvir', 3000);

BEGIN;
UPDATE wallets SET balance = balance - 1500 WHERE wallet_id = 1;
UPDATE wallets SET balance = balance + 1500 WHERE wallet_id = 2;
COMMIT;

SELECT * FROM wallets ORDER BY wallet_id;

+-----------+--------+---------+
| wallet_id | owner  | balance |
+-----------+--------+---------+
|         1 | Nadia  |    3500 |
|         2 | Tanvir |    4500 |
+-----------+--------+---------+

Between BEGIN and COMMIT, the changes are visible only to this session. COMMIT makes both permanent at once. Now a transfer that cannot succeed — Nadia sends 5000 but has only 3500. This time the credit runs first:

BEGIN;
UPDATE wallets SET balance = balance + 5000 WHERE wallet_id = 2;
UPDATE wallets SET balance = balance - 5000 WHERE wallet_id = 1;

Error: CHECK constraint failed: balance >= 0

The debit failed, but the credit has already happened inside the open transaction. Committing now would create 5000 taka out of nothing. Roll back instead:

ROLLBACK;
SELECT * FROM wallets ORDER BY wallet_id;

+-----------+--------+---------+
| wallet_id | owner  | balance |
+-----------+--------+---------+
|         1 | Nadia  |    3500 |
|         2 | Tanvir |    4500 |
+-----------+--------+---------+

Both changes are gone; the wallets are exactly as before. Without BEGIN, every statement is its own transaction (autocommit), and the credit would have stayed. In application code you write this as "try the whole thing; on any error, roll back". Python's sqlite3 module also opens transactions for you: in its default (legacy isolation_level) mode it silently sends BEGIN before an INSERT, UPDATE, DELETE or REPLACE, so nothing is saved until you call con.commit(). Python 3.12 adds the autocommit attribute (autocommit=False keeps a transaction always open, as the DB-API expects). with con: commits when the block succeeds and rolls back on an exception, but it does not close the connection. The details are in Python and Databases: Connections, Parameters and Transactions.

ACID

Savepoints: undo part of a transaction

A savepoint is a bookmark inside a transaction. ROLLBACK TO undoes only the work after it, and the transaction carries on. Loading a batch where a bad row should be skipped, not sink the whole batch, is the classic use:

BEGIN;
INSERT INTO wallets VALUES (3, 'Mitu', 1000);
SAVEPOINT before_bad_row;
INSERT INTO wallets VALUES (4, 'Rafiq', -50);

Error: CHECK constraint failed: balance >= 0

ROLLBACK TO before_bad_row;
INSERT INTO wallets VALUES (5, 'Sadia', 700);
COMMIT;
SELECT * FROM wallets ORDER BY wallet_id;

+-----------+--------+---------+
| wallet_id | owner  | balance |
+-----------+--------+---------+
|         1 | Nadia  |    3500 |
|         2 | Tanvir |    4500 |
|         3 | Mitu   |    1000 |
|         5 | Sadia  |     700 |
+-----------+--------+---------+

Mitu and Sadia are in, Rafiq's invalid row is not. A real loader would also write the rejected row to a quarantine table to fix later. The same commands work in PostgreSQL and MySQL (RELEASE SAVEPOINT name forgets a savepoint you no longer need).

Cascading actions

A foreign key also decides what happens when the row it points at is deleted. The default (NO ACTION, or the almost identical RESTRICT) refuses; CASCADE deletes the dependent rows too; SET NULL keeps them but clears the reference. In SQLite all of this happens only with PRAGMA foreign_keys = ON, which sqlhelp.py turns on. A product-reviews table shows both choices:

CREATE TABLE reviews (
    review_id   INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL,
    customer_id INTEGER,
    stars       INTEGER NOT NULL CHECK (stars BETWEEN 1 AND 5),
    FOREIGN KEY (product_id)  REFERENCES products (product_id)   ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE SET NULL
);
INSERT INTO reviews VALUES (1, 10, 8, 4), (2, 10, 1, 5), (3, 4, 8, 3);

DELETE FROM products WHERE product_id = 10;     -- never sold: its reviews go with it
DELETE FROM customers WHERE customer_id = 8;    -- never ordered: reviews stay, anonymised
SELECT * FROM reviews ORDER BY review_id;

DELETE FROM customers WHERE customer_id = 1;    -- has orders: refused

+-----------+------------+-------------+-------+
| review_id | product_id | customer_id | stars |
+-----------+------------+-------------+-------+
|         3 |          4 | NULL        |     3 |
+-----------+------------+-------------+-------+
Error: FOREIGN KEY constraint failed

The whole cascade runs inside the DELETE's transaction, so it is all-or-nothing too. Use CASCADE for rows that cannot exist without their parent (order lines, reviews of a deleted product); SET NULL where the child should outlive the parent (keeping review text for a model while dropping the personal link); and the refusing default for anything with business or legal value, like orders. A careless CASCADE on customers → orders would let one DELETE erase sales history.

Two sessions at once: isolation on PostgreSQL

Isolation problems only appear when two sessions work at the same time, so we need two connections. We open them from Python with psycopg2 against PostgreSQL, where the shop database is loaded. psycopg2.connect() with no arguments reads the standard variables PGHOST, PGPORT, PGUSER, PGPASSWORD and PGDATABASE, so set those first (for a local Docker server: PGHOST=localhost, your port, user and password, and the database you loaded shop.sql into). No threads: we call A, then B, then A again, one step at a time, so you can see exactly what each session sees.

import psycopg2
from psycopg2 import errors

a = psycopg2.connect()          # session A
b = psycopg2.connect()          # session B

def run(conn, sql, params=None):
    """Run one statement in conn's current transaction; return the single value or row count."""
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchone()[0] if cur.description else cur.rowcount

run(a, "DROP TABLE IF EXISTS accounts")
run(a, """CREATE TABLE accounts (
              account_id INTEGER PRIMARY KEY,
              owner      TEXT NOT NULL,
              balance    INTEGER NOT NULL CHECK (balance >= 0))""")
run(a, "INSERT INTO accounts VALUES (1, 'Nadia', 5000), (2, 'Tanvir', 3000)")
a.commit()
print("A sees", run(a, "SELECT COUNT(*) FROM accounts"), "accounts")
a.commit()

A sees 2 accounts

psycopg2 starts a transaction automatically at the first statement and keeps it open until commit() or rollback(). That is what lets us leave A's transaction open while B looks at the data.

Isolation levels and anomalies

The SQL standard names four isolation levels, each ruling out more anomalies. Stricter levels are safer and cost more waiting or more retries:

Dirty read

A dirty read sees another session's uncommitted change — data that may be rolled back and never have existed. PostgreSQL never allows it; even if you ask for READ UNCOMMITTED you get READ COMMITTED:

b.set_session(isolation_level="READ UNCOMMITTED")

run(a, "UPDATE accounts SET balance = 0 WHERE account_id = 2")      # A: not committed
print("B sees Tanvir's balance:", run(b, "SELECT balance FROM accounts WHERE account_id = 2"))
a.rollback()
b.rollback()

B sees Tanvir's balance: 3000

MySQL does allow it. The same experiment with two PyMySQL connections (they read the MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD and MYSQL_DATABASE variables you set) on the shop's products table:

import os
import pymysql

def mysql_connect():
    return pymysql.connect(host=os.environ["MYSQL_HOST"], port=int(os.environ["MYSQL_PORT"]),
                           user=os.environ["MYSQL_USER"], password=os.environ["MYSQL_PASSWORD"],
                           database=os.environ["MYSQL_DATABASE"])

ma, mb = mysql_connect(), mysql_connect()
run(mb, "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

run(ma, "UPDATE products SET stock = 0 WHERE product_id = 3")       # A: not committed
print("B sees keyboard stock:", run(mb, "SELECT stock FROM products WHERE product_id = 3"))
ma.rollback()
print("after A's rollback:   ", run(mb, "SELECT stock FROM products WHERE product_id = 3"))
mb.rollback()
ma.close()
mb.close()

B sees keyboard stock: 0
after A's rollback:    12

B read a stock of 0 that never really existed. A dashboard or a feature pipeline reading at READ UNCOMMITTED can record numbers from transactions that were later undone. There is almost never a good reason to use it.

Non-repeatable and phantom reads

A non-repeatable read: B reads a row twice in one transaction and gets different values, because A committed a change in between. A phantom read: B runs the same filtered query twice and gets a different set of rows, because A inserted one. The function below runs the identical interleaving at two levels:

def read_twice(level):
    b.set_session(isolation_level=level)
    sql_balance = "SELECT balance FROM accounts WHERE account_id = 1"
    sql_count = "SELECT COUNT(*) FROM accounts WHERE balance >= 3000"
    balance1, count1 = run(b, sql_balance), run(b, sql_count)      # B: first look

    run(a, "UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1")
    run(a, "INSERT INTO accounts VALUES (3, 'Mitu', 4000)")
    a.commit()                                                       # A: change + insert, committed

    balance2, count2 = run(b, sql_balance), run(b, sql_count)      # B: same queries again
    b.commit()
    print(f"{level:16} balance {balance1} -> {balance2}   accounts >= 3000: {count1} -> {count2}")

    run(a, "DELETE FROM accounts WHERE account_id = 3")              # put things back
    run(a, "UPDATE accounts SET balance = balance + 1000 WHERE account_id = 1")
    a.commit()

read_twice("READ COMMITTED")
read_twice("REPEATABLE READ")

READ COMMITTED   balance 5000 -> 4000   accounts >= 3000: 2 -> 3
REPEATABLE READ  balance 5000 -> 5000   accounts >= 3000: 2 -> 2

At READ COMMITTED each statement sees everything committed before it started, so B's second look differs. At REPEATABLE READ the whole transaction sees one snapshot taken at its first statement. That is what you want for a report or a training-data export made of several queries: all of them must describe the same moment, or totals will not add up.

Lost updates, and four ways to prevent them

The most common real bug: both sessions read a value, compute a new one in the application, and write it back. The second write silently overwrites the first. Two customers buy the last keyboards:

b.set_session(isolation_level="READ COMMITTED")
stock_a = run(a, "SELECT stock FROM products WHERE product_id = 3")   # A reads 12
stock_b = run(b, "SELECT stock FROM products WHERE product_id = 3")   # B reads 12
run(a, "UPDATE products SET stock = %s WHERE product_id = 3", (stock_a - 1,))
a.commit()
run(b, "UPDATE products SET stock = %s WHERE product_id = 3", (stock_b - 1,))
b.commit()
print("two sold, stock is now", run(a, "SELECT stock FROM products WHERE product_id = 3"))
a.commit()

two sold, stock is now 11

Two keyboards sold, but stock dropped by one. No error, no warning. Here are the fixes, from simplest to most general.

1. Atomic update: let the database do the arithmetic

Do the read, the check and the write in one statement. The row is locked while it is updated, so concurrent decrements queue up instead of overwriting each other, and the WHERE refuses to oversell:

for product_id in (3, 9):
    sold = run(a, "UPDATE products SET stock = stock - 1 WHERE product_id = %s AND stock > 0",
               (product_id,))
    print(f"product {product_id}:", "reserved" if sold == 1 else "sold out")
a.commit()
print("keyboard stock:", run(a, "SELECT stock FROM products WHERE product_id = 3"))
a.commit()

product 3: reserved
product 9: sold out
keyboard stock: 10

Product 9 has stock 0, so the update matched no row: rowcount 0 means "sold out", with no race between checking and writing. If B ran the same statement while A's was uncommitted, B would wait for A's row lock, then re-check stock > 0 against A's committed value. Whenever the change can be written as one UPDATE … SET x = x + …, do that.

2. Pessimistic locking: SELECT … FOR UPDATE

When the application must read, think, then write (check a credit limit, call a pricing service), lock the row as you read it. Other sessions that try to update, delete or lock the same row must wait until you commit or roll back; plain SELECTs are not blocked and still read the last committed value. To see the lock without hanging our single Python process, B uses NOWAIT, which errors instead of waiting:

stock = run(a, "SELECT stock FROM products WHERE product_id = 3 FOR UPDATE")   # A locks the row
try:
    run(b, "SELECT stock FROM products WHERE product_id = 3 FOR UPDATE NOWAIT")
except errors.LockNotAvailable as exc:
    print("B:", type(exc).__name__, "-", str(exc).splitlines()[0])
    b.rollback()

run(a, "UPDATE products SET stock = %s WHERE product_id = 3", (stock - 1,))
a.commit()                                                                      # lock released
print("B now reads", run(b, "SELECT stock FROM products WHERE product_id = 3 FOR UPDATE"))
b.commit()

B: LockNotAvailable - could not obtain lock on row in relation "products"
B now reads 9

Without NOWAIT, B would simply have waited and then read 9 — the correct, updated value. In production you usually wait, with a cap: SET lock_timeout = '2s' (PostgreSQL) or innodb_lock_wait_timeout (MySQL). FOR UPDATE works the same in MySQL 8. A related option, FOR UPDATE SKIP LOCKED (PostgreSQL and MySQL 8), skips rows that someone else has locked instead of waiting: the usual way to let several workers take jobs from one queue table without picking the same job. SQLite has no row locks at all (see below).

3. Optimistic locking: a version column

Locks held while a person edits a form, or while an LLM call takes ten seconds, block everyone else. Optimistic locking holds no lock: each row carries a version; you remember the version you read and write back only if it is unchanged:

run(a, "ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0")
a.commit()

def save_stock(conn, product_id, new_stock, seen_version):
    changed = run(conn, """UPDATE products SET stock = %s, version = version + 1
                           WHERE product_id = %s AND version = %s""",
                  (new_stock, product_id, seen_version))
    conn.commit()
    return changed == 1

read = "SELECT stock || ' v' || version FROM products WHERE product_id = 4"
print("A read", run(a, read), "| B read", run(b, read))
a.commit()
b.commit()
print("A saves:", save_stock(a, 4, 59, 0))
print("B saves:", save_stock(b, 4, 59, 0))
print("now", run(a, read))
a.commit()

A read 60 v0 | B read 60 v0
A saves: True
B saves: False
now 59 v1

B's save matched no row because the version had moved on, so B knows its data is stale: reload, recompute, try again (or show the user "this item changed"). ORMs support this directly (SQLAlchemy's version_id_col, Django packages, JPA's @Version).

4. A stricter isolation level

Run the original read-then-write at REPEATABLE READ and PostgreSQL detects the conflict itself:

b.set_session(isolation_level="REPEATABLE READ")
stock_b = run(b, "SELECT stock FROM products WHERE product_id = 3")   # B's snapshot
run(a, "UPDATE products SET stock = stock - 1 WHERE product_id = 3")
a.commit()                                                              # A changes it meanwhile
try:
    run(b, "UPDATE products SET stock = %s WHERE product_id = 3", (stock_b - 1,))
except errors.SerializationFailure as exc:
    print("B:", type(exc).__name__, "-", str(exc).splitlines()[0])
    b.rollback()

B: SerializationFailure - could not serialize access due to concurrent update

The update is refused rather than lost. The price is that your code must be ready to retry the whole transaction. (MySQL's REPEATABLE READ does not do this for a plain read followed by an update — there you need FOR UPDATE or an atomic update.) SERIALIZABLE in PostgreSQL goes further and also catches subtler conflicts between transactions that touch different rows.

Deadlocks

A deadlock is two transactions each waiting for a lock the other holds: A locks account 1 then wants 2; B locks 2 then wants 1. Neither can continue, so the database picks a victim and aborts it. To show it without threads, session C is an asynchronous psycopg2 connection: it can send a statement that blocks while we carry on with B:

import select
import psycopg2.extensions as ext

def wait(conn):
    """Let an asynchronous connection finish its current statement."""
    while True:
        state = conn.poll()
        if state == ext.POLL_OK:
            return
        select.select([conn.fileno()] if state == ext.POLL_READ else [],
                      [conn.fileno()] if state == ext.POLL_WRITE else [], [])

c = psycopg2.connect(async_=1)
wait(c)
cur_c = c.cursor()

cur_c.execute("BEGIN; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1")
wait(c)                                                                  # C holds account 1
run(b, "UPDATE accounts SET balance = balance - 100 WHERE account_id = 2")     # B holds account 2
cur_c.execute("UPDATE accounts SET balance = balance + 100 WHERE account_id = 2")   # C waits for B
while not run(a, "SELECT cardinality(pg_blocking_pids(%s)) > 0", (c.get_backend_pid(),)):
    a.commit()                                                           # until C is really waiting
a.commit()

run(b, "UPDATE accounts SET balance = balance + 100 WHERE account_id = 1")     # B waits for C: deadlock
try:
    wait(c)
except errors.DeadlockDetected as exc:
    print("C:", type(exc).__name__, "-", str(exc).splitlines()[0])
cur_c.execute("ROLLBACK")
wait(c)
b.commit()
print("B's transfer went through:",
      run(a, "SELECT string_agg(owner || ' ' || balance, ', ' ORDER BY account_id) FROM accounts"))
a.commit()

C: DeadlockDetected - deadlock detected
B's transfer went through: Nadia 5100, Tanvir 2900

PostgreSQL looks for a deadlock once a session has waited deadlock_timeout (1 second by default). C had been waiting longest, so its check ran first, found the cycle and aborted C; B's statement then went through. (MySQL detects deadlocks immediately and rolls back the smaller transaction.) Deadlocks are not bugs in the database; they are a normal outcome of locking. You reduce them by always locking rows in the same order (for example, lowest account_id first in every transfer), keeping transactions short, and you handle the rest by retrying.

Retrying failed transactions

Serialization failures (SQLSTATE 40001) and deadlocks (40P01) mean "nothing was changed, try again". Retry the whole transaction — re-reading the data — with a short, growing pause, a few times at most; never retry other errors such as a CHECK violation, which will fail the same way every time. To make the first attempt fail on purpose, B changes Nadia's balance in the middle of it:

import time

def transfer(conn, src, dst, amount, attempts=3, interfere=None):
    conn.set_session(isolation_level="REPEATABLE READ")
    for attempt in range(1, attempts + 1):
        try:
            balance = run(conn, "SELECT balance FROM accounts WHERE account_id = %s", (src,))
            if attempt == 1 and interfere:
                interfere()                                  # another session sneaks in
            if balance < amount:
                raise ValueError("insufficient funds")
            run(conn, "UPDATE accounts SET balance = balance - %s WHERE account_id = %s", (amount, src))
            run(conn, "UPDATE accounts SET balance = balance + %s WHERE account_id = %s", (amount, dst))
            conn.commit()
            print(f"attempt {attempt}: committed")
            return
        except (errors.SerializationFailure, errors.DeadlockDetected) as exc:
            conn.rollback()
            print(f"attempt {attempt}: {type(exc).__name__}, retrying")
            time.sleep(0.05 * 2 ** attempt)
    raise RuntimeError("transfer failed after retries")

def b_pays_bill():
    run(b, "UPDATE accounts SET balance = balance - 200 WHERE account_id = 1")
    b.commit()

transfer(a, 1, 2, 1000, interfere=b_pays_bill)
print(run(a, "SELECT string_agg(owner || ' ' || balance, ', ' ORDER BY account_id) FROM accounts"))
a.commit()
for conn in (a, b, c):
    conn.close()

attempt 1: SerializationFailure, retrying
attempt 2: committed
Nadia 3900, Tanvir 3900

Attempt 1 read Nadia's balance from a snapshot that B then made stale, so its update was refused. Attempt 2 re-read the current balance and committed. Both changes — B's 200 and the 1000 transfer — are in the result; nothing was lost. Libraries and ORMs offer retry decorators, but the rule is the same: retry the whole unit of work, only for retryable errors, with a limit.

SQLite, MySQL and PostgreSQL compared

SQLite's one-writer model has one trap. A plain BEGIN is DEFERRED: it takes no lock until the first read, and the write lock only at the first write. If another connection writes in between, the upgrade can fail at once with database is locked, in the middle of your transaction. BEGIN IMMEDIATE takes the write lock at the start, so a read-then-write transaction waits (up to the busy timeout) at BEGIN instead of failing halfway. The one-writer model is also why SQLite is perfect for a notebook or a single-process app and wrong for a web app with many concurrent writers.

Common mistakes

1. Read-modify-write in the application. stock = SELECT …; UPDATE … SET stock = stock_from_python - 1 loses updates. Use UPDATE … SET stock = stock - 1 WHERE stock > 0, FOR UPDATE, or a version column.
2. Leaving transactions open. An idle connection inside a transaction holds its locks and its snapshot; others wait and PostgreSQL cannot clean up old row versions. Commit or roll back promptly — never hold a transaction open across a user action or a slow API/LLM call.
3. Committing after an error. After a failed statement, roll back (or roll back to a savepoint). PostgreSQL refuses further statements in a failed transaction (and a COMMIT there actually rolls back); SQLite and MySQL let you commit the half that worked.
4. Locking rows in different orders. Transfer A→B locking A first and B→A locking B first will deadlock under load. Lock in a fixed order (lowest id first).
5. Retrying everything, or forever. Retry only serialization failures and deadlocks, a few times, with backoff, and retry the whole transaction, not just the failed statement.

Try it yourself

1. Easy: In SQLite, inside a transaction, move 500 from Tanvir (wallet 2) to Mitu (wallet 3), look at the balances, then roll back and show that nothing changed.
2. Medium: In Python with sqlhelp's SQLite connection, write reserve(product_id, qty) that reserves stock with one atomic UPDATE and returns True/False. Try 5 of product 8 (Laptop Stand, 25 in stock) three times, then once more with 15.
3. Hard: Write update_with_version(con, product_id, change) for SQLite that reads stock and version, writes back with the version check and retries up to 3 times on conflict. Add the version column first.

Answers

-- Easy
BEGIN;
UPDATE wallets SET balance = balance - 500 WHERE wallet_id = 2;
UPDATE wallets SET balance = balance + 500 WHERE wallet_id = 3;
SELECT * FROM wallets ORDER BY wallet_id;
ROLLBACK;
SELECT * FROM wallets ORDER BY wallet_id;

# Medium
from sqlhelp import con

def reserve(product_id, qty):
    with con:                                   # commits, or rolls back on error (does not close con)
        cur = con.execute("UPDATE products SET stock = stock - ? "
                          "WHERE product_id = ? AND stock >= ?", (qty, product_id, qty))
    return cur.rowcount == 1

print([reserve(8, 5) for _ in range(3)], reserve(8, 15))
print(con.execute("SELECT stock FROM products WHERE product_id = 8").fetchone()[0])

# Hard
con.execute("ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0")
con.commit()

def update_with_version(con, product_id, change, attempts=3):
    for attempt in range(1, attempts + 1):
        stock, version = con.execute("SELECT stock, version FROM products WHERE product_id = ?",
                                     (product_id,)).fetchone()
        with con:
            cur = con.execute("UPDATE products SET stock = ?, version = version + 1 "
                              "WHERE product_id = ? AND version = ?",
                              (stock + change, product_id, version))
        if cur.rowcount == 1:
            return attempt
    raise RuntimeError("too many conflicts")

print(update_with_version(con, 4, -2))
print(con.execute("SELECT stock, version FROM products WHERE product_id = 4").fetchone())

Summary

• A transaction is all-or-nothing: BEGIN … COMMIT, or ROLLBACK on any error; savepoints undo part of one.
• ACID: atomic, consistent (constraints hold), isolated, durable. Foreign-key actions (CASCADE, SET NULL, refuse) run inside the same transaction.
• Isolation levels trade safety for concurrency: READ COMMITTED allows non-repeatable and phantom reads; PostgreSQL's REPEATABLE READ gives a stable snapshot; of the three databases here, only MySQL allows dirty reads, at READ UNCOMMITTED.
• Prevent lost updates with atomic updates first, then FOR UPDATE, version columns or stricter isolation.
• Deadlocks and serialization failures are normal: lock in a consistent order, keep transactions short, retry the whole transaction a few times.

Next: Indexes: How They Work and When They Help turns from correctness to speed: how an index lets the database find rows without reading the whole table, and what it costs on every write.