Transactions — Cheat Sheet

MySQL · 7 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org

Transactions

MySQL7 topicsQuick revision reference

InnoDB Storage Engine

InnoDB is MySQL's default ACID-compliant engine providing row-level locking, MVCC, foreign keys, crash recovery via redo/undo logs, and clustered primary key index.

✓InnoDB uses a clustered primary key B-Tree — leaf nodes contain full row data; use AUTO_INCREMENT INT/BIGINT, not UUID.
✓Secondary indexes store the PK value as a row pointer — secondary index lookups require two B-Tree traversals unless covered by a covering index.
✓innodb_buffer_pool_size is the #1 tuning parameter — target 70–80% of RAM; monitor hit ratio (should be > 99%).
✓MVCC allows concurrent reads and writes without blocking each other by maintaining multiple versions of each row in undo logs.
✓InnoDB uses row-level locking — shared (S), exclusive (X), and gap locks; always acquire locks in consistent order to avoid deadlocks.
✓InnoDB automatically detects and resolves deadlocks by rolling back one transaction; the application must retry the rolled-back transaction.

SQL — Clustered Index Internals

-- Clustered index — row data lives in the B-Tree leaf nodes
CREATE TABLE orders (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- clustered index key
    customer_id INT    NOT NULL,
    status      VARCHAR(20),
    total       DECIMAL(10,2),
    created_at  DATETIME,
    INDEX idx_customer (customer_id)   -- secondary index
);

-- PK lookup: 1 B-Tree traversal → leaf contains full row
SELECT * FROM orders WHERE id = 12345;

-- Secondary index lookup: 2 B-Tree traversals
-- Step 1: traverse idx_customer B-Tree → find id=12345 for customer_id=99
-- Step 2: traverse clustered index with id=12345 → get full row
SELECT * FROM orders WHERE customer_id = 99;

-- Covering index avoids step 2 (secondary lookup) entirely
-- Add all needed columns to the index:
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status, total);
-- Query can now be answered from index alone (Extra: Using index in EXPLAIN)
SELECT customer_id, status, total FROM orders WHERE customer_id = 99;

ACID Properties

Atomicity (all-or-nothing), Consistency (constraints maintained), Isolation (concurrent transactions are invisible to each other), Durability (committed data persists) are guaranteed by InnoDB.

✓Atomicity: undo log enables rollback — partial updates never persist
✓Consistency: FK, UNIQUE, NOT NULL, CHECK constraints enforced at commit
✓Isolation: four levels (READ UNCOMMITTED → SERIALIZABLE); default is REPEATABLE READ
✓Durability: redo log (WAL) ensures committed changes survive crashes
✓innodb_flush_log_at_trx_commit=1 is the only fully durable setting
✓InnoDB is ACID-compliant; MyISAM has no transaction support

MySQL — atomicity and constraint enforcement

-- Classic bank transfer: must be atomic
START TRANSACTION;

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

-- If second UPDATE fails (e.g. account 2 not found), rollback both
COMMIT;

-- Consistency: FK prevents orphan rows
ALTER TABLE orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;   -- rejects DELETE on customers if orders exist

-- Attempted violation → ERROR 1452, transaction rolled back
INSERT INTO orders (customer_id, total) VALUES (9999, 50.00);

Transaction Isolation Levels

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), SERIALIZABLE — each trades read anomalies (dirty read, non-repeatable read, phantom) against concurrency performance.

✓Four levels: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ (InnoDB default) < SERIALIZABLE — higher level = fewer anomalies, less concurrency.
✓Dirty read: reading uncommitted data. Non-repeatable read: re-reading a row gets a different value. Phantom read: a range query returns different rows on re-execution.
✓InnoDB REPEATABLE READ uses MVCC snapshots (not locks) for reads — readers and writers do not block each other.
✓READ COMMITTED is often used for reporting or OLTP: each query sees the latest committed data, but a transaction sees different data across queries.
✓SERIALIZABLE prevents all anomalies using range locks; expect deadlocks under concurrency — always add retry logic.
✓MySQL InnoDB REPEATABLE READ also prevents most phantom reads (using gap locks), unlike the SQL standard where phantoms are allowed at this level.

SQL — Isolation Level Demo

-- Set isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or for next transaction only
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Read anomaly demonstration

-- ── Dirty Read (READ UNCOMMITTED) ──────────────────────────────
-- Transaction A: UPDATE orders SET total = 1000 WHERE id = 1  (not committed)
-- Transaction B: SELECT total FROM orders WHERE id = 1  → reads 1000 (dirty!)
-- Transaction A: ROLLBACK
-- Transaction B: acted on data that never existed

-- ── Non-Repeatable Read (READ COMMITTED) ───────────────────────
-- Transaction A: SELECT total FROM orders WHERE id = 1  → 500
-- Transaction B: UPDATE orders SET total = 800 WHERE id = 1; COMMIT;
-- Transaction A: SELECT total FROM orders WHERE id = 1  → 800 (changed!)
-- Same row, same query, different result within Transaction A

-- ── Phantom Read (REPEATABLE READ / SQL standard) ───────────────
-- Transaction A: SELECT COUNT(*) FROM orders WHERE status='PENDING'  → 10
-- Transaction B: INSERT INTO orders(...) VALUES(..., 'PENDING'); COMMIT;
-- Transaction A: SELECT COUNT(*) FROM orders WHERE status='PENDING'  → 11 (phantom!)
-- In MySQL InnoDB: gap locks prevent this phantom — still returns 10

MVCC (Multi-Version Concurrency Control)

InnoDB stores multiple row versions in the undo log; readers see a snapshot consistent with their transaction start, allowing readers and writers to proceed without blocking each other.

✓MVCC lets readers and writers proceed simultaneously without blocking each other
✓Each row has hidden DB_TRX_ID and DB_ROLL_PTR columns tracking version history
✓REPEATABLE READ: read view created at first statement; READ COMMITTED: per statement
✓InnoDB walks the undo log chain to find the version visible to a transaction's read view
✓Long-running transactions prevent purge thread from clearing old versions → undo log bloat
✓Monitor "History list length" in INNODB STATUS; kill transactions older than ~30s in OLTP

MySQL — REPEATABLE READ vs READ COMMITTED snapshot timing

-- Session A: REPEATABLE READ (default)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- sees $100

-- Session B: commits an update
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session A: same transaction, same read view → still sees $100
SELECT balance FROM accounts WHERE id = 1;   -- $100 (snapshot isolation)
COMMIT;

-- Session A: READ COMMITTED
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- $100 (read view created here)

-- Session B commits UPDATE balance=200

SELECT balance FROM accounts WHERE id = 1;   -- $200 (new read view per statement)
COMMIT;

Deadlocks & Locking

Deadlocks occur when two transactions hold locks each other needs; InnoDB detects the cycle and rolls back the lighter transaction; access rows in consistent order to avoid them.

✓Deadlock = circular lock wait; InnoDB rolls back the cheapest victim automatically (error 1213)
✓Prevention: always access rows in the same consistent order across all transactions
✓Missing indexes cause large gap locks → more rows locked → higher deadlock probability
✓Keep transactions short — minimise the lock-hold window
✓Never make HTTP calls or cache operations inside a DB transaction
✓Retry deadlock victims: use @Retryable(DeadlockLoserDataAccessException.class) in Spring

MySQL — classic deadlock scenario

-- Session A                              -- Session B
START TRANSACTION;                       START TRANSACTION;
UPDATE accounts SET bal=bal-100           UPDATE accounts SET bal=bal+50
  WHERE id = 1;    ← locks row 1           WHERE id = 2;    ← locks row 2
                                          -- (waits for A to release row 2)
UPDATE accounts SET bal=bal+100
  WHERE id = 2;    ← waits for B's lock
-- DEADLOCK DETECTED
-- InnoDB rolls back one victim (usually B — lighter undo log)
-- ERROR 1213 (40001): Deadlock found

-- Diagnose
SHOW ENGINE INNODB STATUS;              -- "LATEST DETECTED DEADLOCK" section
SELECT * FROM performance_schema.data_lock_waits;

Row-Level vs Table-Level Locking

InnoDB row-level locking (shared S, exclusive X, intent locks) allows high concurrency; table-level locks (LOCK TABLES) are used for bulk operations and DDL on MyISAM.

✓InnoDB row-level locking (S/X) maximises concurrency — only conflicting rows are locked, not the whole table
✓Intent locks (IS/IX) are automatic table markers that prevent LOCK TABLES from racing with row locks
✓Next-Key locks (record + gap) prevent phantom reads under REPEATABLE READ by locking index ranges
✓Gap locks can block inserts in ranges even when no matching record exists — a common source of unexpected deadlocks
✓READ COMMITTED removes gap locks (only record locks) — reduces deadlocks but allows phantom reads
✓Long-running transactions hold metadata locks (MDL) and can block ALTER TABLE, causing table-wide write queues

SQL — shared, exclusive, and intent lock demonstration

-- Shared lock: allows other readers, blocks writers
SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- Another transaction CAN also SELECT ... FOR SHARE (concurrent reads)
-- Another transaction CANNOT SELECT ... FOR UPDATE (blocked)

-- Exclusive lock: blocks all other lock acquisition
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- No other transaction can read or write row id=1 until commit/rollback

-- Intent locks (automatic) prevent concurrent LOCK TABLES
-- Transaction A holds X lock on row 1 → IX on orders table
-- Another session: LOCK TABLE orders WRITE → blocked by IX on orders

-- Check current locks (MySQL 8+)
SELECT * FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'orders';
-- LOCK_TYPE: TABLE (intent), ROW (record/gap/next-key)

Foreign Keys & Referential Integrity

InnoDB foreign keys enforce parent-child integrity with ON DELETE/UPDATE CASCADE, SET NULL, or RESTRICT; always index the FK column to avoid full-table scans on parent updates.

✓InnoDB is the only MySQL engine that enforces FK constraints — MyISAM silently accepts invalid data.
✓Always add an index on the FK column — without it MySQL scans the entire child table on every parent UPDATE/DELETE.
✓RESTRICT is the safe default; CASCADE is convenient but dangerous on large tables (one delete triggers thousands of child deletes).
✓FK validation acquires a shared lock on the parent row — under high insert concurrency this can cause contention and deadlocks.
✓Use SET FOREIGN_KEY_CHECKS=0 only for bulk loads; verify integrity with a LEFT JOIN check before re-enabling.
✓FK constraints catch bugs in application code but add write overhead — some large-scale systems enforce integrity at the application layer instead.

SQL — FK declaration with cascade rules

-- Parent table
CREATE TABLE customers (
    id         BIGINT      NOT NULL AUTO_INCREMENT,
    email      VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

-- Child table with FK and appropriate cascade
CREATE TABLE orders (
    id          BIGINT       NOT NULL AUTO_INCREMENT,
    customer_id BIGINT       NOT NULL,
    status      VARCHAR(30)  NOT NULL DEFAULT 'PENDING',
    total       DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id),
    INDEX       idx_customer_id (customer_id),   -- ALWAYS index the FK column
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (id)
        ON DELETE RESTRICT    -- prevent deleting a customer with orders
        ON UPDATE CASCADE     -- if customer PK changes, update FK automatically
);

-- Cascade options:
-- CASCADE:     propagate DELETE/UPDATE to child rows automatically
-- SET NULL:    nullify FK column on parent DELETE/UPDATE (column must be nullable)
-- RESTRICT:    reject the parent operation if children exist (default)
-- NO ACTION:   same as RESTRICT but deferred check (InnoDB treats same as RESTRICT)
-- SET DEFAULT: not supported by InnoDB

-- Add FK to existing table
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

Learn this free with Aria, your AI tutor → AiCanCode.org/learn/mysql

Cheat Sheet

Transactions

MySQL

7 topics · swipe →

AiCanCode.org

Transactions

InnoDB Storage Engine

✓InnoDB uses a clustered primary key B-Tree — leaf nodes contain full row data; use AUTO_INCREMENT INT/BIGINT, not UUID.
✓Secondary indexes store the PK value as a row pointer — secondary index lookups require two B-Tree traversals unless covered by a covering index.
✓innodb_buffer_pool_size is the #1 tuning parameter — target 70–80% of RAM; monitor hit ratio (should be > 99%).
✓MVCC allows concurrent reads and writes without blocking each other by maintaining multiple versions of each row in undo logs.
✓InnoDB uses row-level locking — shared (S), exclusive (X), and gap locks; always acquire locks in consistent order to avoid deadlocks.

-- Clustered index — row data lives in the B-Tree leaf nodes
CREATE TABLE orders (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- clustered index key
    customer_id INT    NOT NULL,
    status      VARCHAR(20),
    total       DECIMAL(10,2),
    created_at  DATETIME,
    INDEX idx_customer (customer_id)   -- secondary index
);
…

AiCanCode.org

Transactions

ACID Properties

✓Atomicity: undo log enables rollback — partial updates never persist
✓Consistency: FK, UNIQUE, NOT NULL, CHECK constraints enforced at commit
✓Isolation: four levels (READ UNCOMMITTED → SERIALIZABLE); default is REPEATABLE READ
✓Durability: redo log (WAL) ensures committed changes survive crashes
✓innodb_flush_log_at_trx_commit=1 is the only fully durable setting

-- Classic bank transfer: must be atomic
START TRANSACTION;

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

-- If second UPDATE fails (e.g. account 2 not found), rollback both
COMMIT;

…

AiCanCode.org

Transactions

Transaction Isolation Levels

✓Four levels: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ (InnoDB default) < SERIALIZABLE — higher level = fewer anomalies, less concurrency.
✓Dirty read: reading uncommitted data. Non-repeatable read: re-reading a row gets a different value. Phantom read: a range query returns different rows on re-execution.
✓InnoDB REPEATABLE READ uses MVCC snapshots (not locks) for reads — readers and writers do not block each other.
✓READ COMMITTED is often used for reporting or OLTP: each query sees the latest committed data, but a transaction sees different data across queries.
✓SERIALIZABLE prevents all anomalies using range locks; expect deadlocks under concurrency — always add retry logic.

-- Set isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or for next transaction only
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Read anomaly demonstration

-- ── Dirty Read (READ UNCOMMITTED) ──────────────────────────────
…

AiCanCode.org

Transactions

MVCC (Multi-Version Concurrency Control)

✓MVCC lets readers and writers proceed simultaneously without blocking each other
✓Each row has hidden DB_TRX_ID and DB_ROLL_PTR columns tracking version history
✓REPEATABLE READ: read view created at first statement; READ COMMITTED: per statement
✓InnoDB walks the undo log chain to find the version visible to a transaction's read view
✓Long-running transactions prevent purge thread from clearing old versions → undo log bloat

-- Session A: REPEATABLE READ (default)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- sees $100

-- Session B: commits an update
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session A: same transaction, same read view → still sees $100
…

AiCanCode.org

Transactions

Deadlocks & Locking

✓Deadlock = circular lock wait; InnoDB rolls back the cheapest victim automatically (error 1213)
✓Prevention: always access rows in the same consistent order across all transactions
✓Missing indexes cause large gap locks → more rows locked → higher deadlock probability
✓Keep transactions short — minimise the lock-hold window
✓Never make HTTP calls or cache operations inside a DB transaction

-- Session A                              -- Session B
START TRANSACTION;                       START TRANSACTION;
UPDATE accounts SET bal=bal-100           UPDATE accounts SET bal=bal+50
  WHERE id = 1;    ← locks row 1           WHERE id = 2;    ← locks row 2
                                          -- (waits for A to release row 2)
UPDATE accounts SET bal=bal+100
  WHERE id = 2;    ← waits for B's lock
-- DEADLOCK DETECTED
-- InnoDB rolls back one victim (usually B — lighter undo log)
…

AiCanCode.org

Transactions

Row-Level vs Table-Level Locking

✓InnoDB row-level locking (S/X) maximises concurrency — only conflicting rows are locked, not the whole table
✓Intent locks (IS/IX) are automatic table markers that prevent LOCK TABLES from racing with row locks
✓Next-Key locks (record + gap) prevent phantom reads under REPEATABLE READ by locking index ranges
✓Gap locks can block inserts in ranges even when no matching record exists — a common source of unexpected deadlocks
✓READ COMMITTED removes gap locks (only record locks) — reduces deadlocks but allows phantom reads

-- Shared lock: allows other readers, blocks writers
SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- Another transaction CAN also SELECT ... FOR SHARE (concurrent reads)
-- Another transaction CANNOT SELECT ... FOR UPDATE (blocked)

-- Exclusive lock: blocks all other lock acquisition
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- No other transaction can read or write row id=1 until commit/rollback

…

AiCanCode.org

Transactions

Foreign Keys & Referential Integrity

✓InnoDB is the only MySQL engine that enforces FK constraints — MyISAM silently accepts invalid data.
✓Always add an index on the FK column — without it MySQL scans the entire child table on every parent UPDATE/DELETE.
✓RESTRICT is the safe default; CASCADE is convenient but dangerous on large tables (one delete triggers thousands of child deletes).
✓FK validation acquires a shared lock on the parent row — under high insert concurrency this can cause contention and deadlocks.
✓Use SET FOREIGN_KEY_CHECKS=0 only for bulk loads; verify integrity with a LEFT JOIN check before re-enabling.

-- Parent table
CREATE TABLE customers (
    id         BIGINT      NOT NULL AUTO_INCREMENT,
    email      VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

-- Child table with FK and appropriate cascade
CREATE TABLE orders (
…

AiCanCode.org

Master Transactions

Full lessons + an AI tutor, free.

AiCanCode.org

/learn/mysql