MENU


Transactions, Locking, and Storage Engines

Two customers can buy the last copy of a book at the same moment, and a checkout that fails halfway leaves the database lying. Transactions make groups of statements all-or-nothing, and InnoDB's locks and row versions decide what concurrent transactions see of each other, on MySQL 9.7's defaults.The examples are transcripts of real runs, in which a script drove one mysql client per session: Session A> is a statement sent, Session A: what came back, and (waiting) a statement still blocked after half a second. Redundant acknowledgments (Query OK, 0 rows affected, 1 row in set) are omitted, and each run starts from a fresh bookshop, where product 5 has 7 copies.

3.14.1 ACID and Why Transactions Matter

A transaction is a group of statements treated as one unit, with four guarantees known as ACID:

  • Atomicity: all statements take effect or none; the undo log reverses them.

  • Consistency: keys, foreign keys and CHECK constraints (Section 3.4.3) always hold.

  • Isolation: transactions do not see each other's unfinished work, thanks to row locks and snapshots (Sections 3.14.4 to 3.14.6).

  • Durability: a commit survives a crash, because the redo log is flushed at COMMIT while innodb_flush_log_at_trx_commit keeps its default of 1.

    • The trap is that an error inside a transaction rolls back only the failing statement, not the transaction. Here a double-clicked button adds the same book to order 1 twice:

    Session A> START TRANSACTION;

    Session A> UPDATE products SET stock = stock - 1 WHERE id = 1;

    Session A: Query OK, 1 row affected

    Session A> INSERT INTO order_items VALUES (1, 1, 1, 39.90);

    Session A: ERROR 1062 (23000) at line 3: Duplicate entry '1-1' for key 'order_items.PRIMARY'

    Session A> SELECT stock FROM products WHERE id = 1\G

    Session A: *************************** 1. row ***************************

    Session A: stock: 24

    Session A> ROLLBACK;

    Session A> SELECT stock FROM products WHERE id = 1\G

    Session A: *************************** 1. row ***************************

    Session A: stock: 25

    Listing 3.122: A failed statement leaves the transaction open The stock decrement survived the error; a COMMIT would have lost a book the shop never sold. Your code must turn any failed statement into a ROLLBACK: PDO in exception mode does it in a catch block (Section 4.16.11), and Laravel's DB::transaction() on any exception (Section 5.10.10).

    3.14.2 START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT

    START TRANSACTION (or BEGIN) opens a transaction, COMMIT makes its changes permanent and visible, and ROLLBACK discards them. Modifiers include READ ONLY and WITH CONSISTENT SNAPSHOT (Section 3.14.5). Transactions do not nest: a second START TRANSACTION commits the first. A savepoint marks a point you can return to with ROLLBACK TO SAVEPOINT without abandoning the transaction; RELEASE SAVEPOINT forgets one, and COMMIT or ROLLBACK clears them all.A safe checkout decrements stock first, with the availability test inside the UPDATE. Its row lock makes a second buyer wait. Below, Session A also adds a free mug and then withdraws it with a savepoint; once A commits, B's UPDATE re-reads the committed row, finds 2 copies, and matches nothing:

    Session A> START TRANSACTION;

    Session A> UPDATE products SET stock = stock - 5 WHERE id = 5 AND stock >= 5;

    Session A: Query OK, 1 row affected

    Session B> START TRANSACTION;

    Session B> UPDATE products SET stock = stock - 5 WHERE id = 5 AND stock >= 5;

    Session B: (waiting)

    Session A> INSERT INTO orders (customer_id, ordered_at) VALUES (7, NOW());

    Session A: Query OK, 1 row affected

    Session A> INSERT INTO order_items VALUES (LAST_INSERT_ID(), 5, 5, 34.00);

    Session A: Query OK, 1 row affected

    Session A> SAVEPOINT gift;

    Session A> UPDATE products SET stock = stock - 1 WHERE id = 7;

    Session A: Query OK, 1 row affected

    Session A> ROLLBACK TO SAVEPOINT gift;

    Session A> COMMIT;

    Session B: Rows matched: 0 Changed: 0 Warnings: 0

    Session B> ROLLBACK;

    Session A> SELECT id, stock FROM products WHERE id IN (5, 7);

    Session A: +----+-------+

    Session A: | id | stock |

    Session A: +----+-------+

    Session A: | 5 | 2 |

    Session A: | 7 | 60 |

    Session A: +----+-------+

    Listing 3.123: Two checkouts race for the last five copies The mug is back at 60 while the order's decrement stands. An affected-row count of 0 (Section 4.16.11) means sold out: roll back and tell the customer. Reading stock with a plain SELECT and writing back a value computed in PHP instead lets two buyers who both read 7 sell ten books from seven. Laravel maps nested DB::transaction() calls onto savepoints (Section 5.10.10).

    3.14.3 Autocommit and Implicit Commits

    With autocommit=1, the default, every statement outside START TRANSACTION commits the moment it succeeds. After SET autocommit = 0, every statement joins an open transaction that lasts until you commit, so a forgotten session holds its locks and its snapshot indefinitely. Leave autocommit on and open transactions explicitly, as PDO and Laravel do.Many statements cause an implicit commit of the open transaction: all DDL (CREATE, ALTER, DROP, RENAME, TRUNCATE), account statements (CREATE USER, GRANT), LOCK TABLES, START TRANSACTION, SET autocommit = 1, and maintenance such as ANALYZE TABLE. CREATE TEMPORARY TABLE does not commit, but cannot be rolled back either. In a test session with autocommit off, UPDATE products SET price = 5.49 WHERE id = 8, then CREATE TABLE price_audit (id INT), then ROLLBACK left the price at 5.49: the CREATE had already committed it. Keep schema changes out of data transactions, and do not expect a failed migration to undo its earlier DDL: each statement is atomic, but a sequence of them is not (Section 5.9.2).

    3.14.4 Isolation Levels and the Anomalies They Prevent

    The SQL standard names three read anomalies. A dirty read sees another transaction's uncommitted change; a non-repeatable read gets a different value when it reads a row again; a phantom is a row that appears in a repeated range query. Four isolation levels trade them against concurrency; SET SESSION TRANSACTION ISOLATION LEVEL ... picks one, and @@transaction_isolation shows it.In this run Session B restocks one Databases title and adds another inside a transaction, while Session A sums the category three times:

    Session A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

    Session A> START TRANSACTION;

    Session A> SELECT COUNT(*), SUM(stock) FROM products WHERE category_id = 3\G

    Session A: *************************** 1. row ***************************

    Session A: COUNT(*): 2

    Session A: SUM(stock): 12

    Session B> START TRANSACTION;

    Session B> UPDATE products SET stock = 20 WHERE id = 2;

    Session B: Query OK, 1 row affected

    Session B> INSERT INTO products VALUES (9, 'BK-SQL-03', 'Query Tuning', 3, 38.00, 5, NULL);

    Session B: Query OK, 1 row affected

    Session A> SELECT COUNT(*), SUM(stock) FROM products WHERE category_id = 3\G

    Session A: *************************** 1. row ***************************

    Session A: COUNT(*): 2

    Session A: SUM(stock): 12

    Session B> COMMIT;

    Session A> SELECT COUNT(*), SUM(stock) FROM products WHERE category_id = 3\G

    Session A: *************************** 1. row ***************************

    Session A: COUNT(*): 3

    Session A: SUM(stock): 25

    Session A> COMMIT;

    Listing 3.124: No dirty read, then a non-repeatable read and a phantom, under READ COMMITTED A's second read ignored B's uncommitted work; its third saw B's commit, so the sum changed (a non-repeatable read) and a row appeared (a phantom). The same file at the other levels gave: Level Second read (B uncommitted) Third read (B committed) Session B READ UNCOMMITTED 3 rows, 25: dirty read 3 rows, 25 Not blocked READ COMMITTED 2 rows, 12 3 rows, 25 Not blocked REPEATABLE READ (default) 2 rows, 12 2 rows, 12 Not blocked SERIALIZABLE 2 rows, 12 2 rows, 12 Waited for A's COMMIT Table 3.9: What Session A's reads returned at each isolation level on MySQL 9.7 The standard permits phantoms under REPEATABLE READ, but InnoDB's snapshot hides them from plain reads, and gap locks keep them out of locking reads (Section 3.14.6). SERIALIZABLE turns each plain SELECT inside a transaction into SELECT ... FOR SHARE, which made B wait. READ COMMITTED, the usual alternative, skips gap locks and so suffers fewer lock waits and deadlocks.

    3.14.5 MVCC, Undo Logs, and Consistent Reads

    Readers and writers share rows without blocking each other through multi-version concurrency control (MVCC). Each clustered-index record (Section 3.11.2) carries a hidden DB_TRX_ID, the last transaction that changed it, and DB_ROLL_PTR, a pointer to the previous version in the undo log. A plain SELECT is a consistent read: it walks back along the pointers to the newest version its read view, the set of transactions committed when the view was made, may see.Figure 3.12: One row and its undo chain: each reader rebuilds the version its read view allows (transaction IDs illustrative)Under REPEATABLE READ the first read creates the view, which lasts until commit; under READ COMMITTED each statement gets a fresh one. Writes and locking reads, however, act on the latest committed version, because that is the one they lock:

    Session A> START TRANSACTION WITH CONSISTENT SNAPSHOT;

    Session B> UPDATE products SET stock = stock - 2 WHERE id = 5;

    Session B: Query OK, 1 row affected

    Session A> SELECT stock FROM products WHERE id = 5\G

    Session A: *************************** 1. row ***************************

    Session A: stock: 7

    Session A> UPDATE products SET stock = stock - 1 WHERE id = 5;

    Session A: Query OK, 1 row affected

    Session A> SELECT stock FROM products WHERE id = 5\G

    Session A: *************************** 1. row ***************************

    Session A: stock: 4

    Session A> COMMIT;

    Listing 3.125: A snapshot read versus the row the UPDATE changes A saw 7, subtracted 1 and read back 4: the UPDATE worked on B's committed 5, and a transaction always sees its own changes. Never compute a value from a snapshot read and write it back; let the UPDATE do the arithmetic or lock the row first (Section 3.14.6). Old versions are purged only when no read view needs them, so one idle open transaction makes the undo log grow.

    3.14.6 Row Locks, Gap Locks, and Locking Reads

    InnoDB locks index entries, so the index a statement scans decides what it locks. A record lock covers one entry, a gap lock the space between two entries, and a next-key lock (the REPEATABLE READ default) an entry plus the gap before it. An insert first requests an insert intention lock on its gap, which waits for other sessions' gap locks. Table-level IX and IS locks merely announce row locks. Plain SELECTs lock nothing, but SELECT ... FOR SHARE takes shared locks and SELECT ... FOR UPDATE exclusive ones. NOWAIT fails at once on a locked row, and SKIP LOCKED leaves locked rows out, which lets several workers share one job queue. This view lists the current database's locks:

    CREATE VIEW locks AS

    SELECT engine_transaction_id AS trx, object_name AS tbl, index_name AS idx,

    lock_mode AS mode, lock_status AS status, lock_data AS data

    FROM performance_schema.data_locks

    WHERE object_schema = DATABASE();

    Listing 3.126: A compact view of performance_schema.data_locks Session A updates both Databases titles through the category_id index. Session B, with a 3-second lock wait timeout, skips A's rows, is refused by NOWAIT, and tries to add a Databases title while Session C looks at the locks:

    Session A> START TRANSACTION;

    Session A> UPDATE products SET stock = stock + 1 WHERE category_id = 3;

    Session A: Query OK, 2 rows affected

    Session B> SET SESSION innodb_lock_wait_timeout = 3;

    Session B> SELECT id FROM products WHERE stock > 0 ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;

    Session B: +----+

    Session B: | id |

    Session B: +----+

    Session B: | 1 |

    Session B: | 4 |

    Session B: +----+

    Session B> SELECT id FROM products WHERE id = 2 FOR UPDATE NOWAIT;

    Session B: ERROR 3572 (HY000) at line 3: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

    Session B> INSERT INTO products VALUES (9, 'BK-SQL-03', 'Query Tuning', 3, 38.00, 5, NULL);

    Session B: (waiting)

    Session C> SELECT * FROM locks ORDER BY trx, tbl, idx, data;

    Session C: +-------+------------+-------------+------------------------+---------+------+

    Session C: | trx | tbl | idx | mode | status | data |

    Session C: +-------+------------+-------------+------------------------+---------+------+

    Session C: | 92399 | products | NULL | IX | GRANTED | NULL |

    Session C: | 92399 | products | category_id | X | GRANTED | 3, 2 |

    Session C: | 92399 | products | category_id | X | GRANTED | 3, 3 |

    Session C: | 92399 | products | category_id | X,GAP | GRANTED | 4, 5 |

    Session C: | 92399 | products | PRIMARY | X,REC_NOT_GAP | GRANTED | 2 |

    Session C: | 92399 | products | PRIMARY | X,REC_NOT_GAP | GRANTED | 3 |

    Session C: | 92402 | categories | NULL | IS | GRANTED | NULL |

    Session C: | 92402 | categories | PRIMARY | S,REC_NOT_GAP | GRANTED | 3 |

    Session C: | 92402 | products | NULL | IX | GRANTED | NULL |

    Session C: | 92402 | products | category_id | X,GAP,INSERT_INTENTION | WAITING | 4, 5 |

    Session C: +-------+------------+-------------+------------------------+---------+------+

    Session B: ERROR 1205 (HY000) at line 4: Lock wait timeout exceeded; try restarting transaction

    Session A> ROLLBACK;

    Listing 3.127: Next-key locks, SKIP LOCKED, NOWAIT, and a lock wait timeout A holds next-key locks (X) on entries (3, 2) and (3, 3), record locks on primary keys 2 and 3, and a gap lock before (4, 5), so no new category-3 row can appear: this is how locking statements prevent phantoms. B's entry (3, 9) falls in that gap and waited until error 1205; its shared lock on categories is the foreign-key check (Section 3.4.4). Gap locks overreach: in another run a category-2 insert, entry (2, 9), timed out too. The timeout (innodb_lock_wait_timeout, 50 seconds by default) rolls back only the waiting statement. Without a usable index, a locking statement locks every row it scans (Section 3.11).

    3.14.7 Table Locks, Metadata Locks, and the Global Read Lock

    LOCK TABLES products WRITE, categories READ takes explicit table locks: READ lets every session read but none write, WRITE gives the holder sole access, and UNLOCK TABLES or START TRANSACTION releases them. MyISAM needs them; InnoDB transactions make them unnecessary.Every statement also takes a metadata lock (MDL) on each table it uses and holds it until its transaction ends, so no DDL can change the table underneath it. DDL needs an exclusive MDL, and a waiting exclusive request queues every later request behind it. In a test run, Session A updated one row of products and left its transaction open; B's ALTER TABLE products ADD isbn CHAR(13) then waited, and so did C's plain one-row SELECT, both shown in performance_schema.processlist as "Waiting for table metadata lock" until A committed. Before DDL on a busy table, look for old transactions in information_schema.INNODB_TRX, and lower lock_wait_timeout (a year by default) to a few seconds in the DDL session.FLUSH TABLES WITH READ LOCK takes the global read lock: every write on the server waits until UNLOCK TABLES, and START TRANSACTION does not release it. Backup tools hold it briefly to record a consistent binary log position. LOCK INSTANCE FOR BACKUP is lighter: it blocks DDL and other file-changing statements but lets DML run (Section 3.17 covers backups).

    3.14.8 Deadlocks: Detection, Diagnosis, and Avoidance

    A deadlock is a cycle of waits: A holds a lock B needs while waiting for one B holds. Two carts that contain the same two books, updated in opposite order, are enough:

    Session A> START TRANSACTION;

    Session A> UPDATE products SET stock = stock - 1 WHERE id = 1;

    Session A: Query OK, 1 row affected

    Session B> START TRANSACTION;

    Session B> UPDATE products SET stock = stock - 1 WHERE id = 2;

    Session B: Query OK, 1 row affected

    Session A> UPDATE products SET stock = stock - 1 WHERE id = 2;

    Session A: (waiting)

    Session B> UPDATE products SET stock = stock - 1 WHERE id = 1;

    Session A: Query OK, 1 row affected

    Session B: ERROR 1213 (40001) at line 3: Deadlock found when trying to get lock; try restarting transaction

    Session A> COMMIT;

    Listing 3.128: Two checkouts lock the same rows in opposite order InnoDB's detector (innodb_deadlock_detect, on by default) found the cycle at once, chose a victim, preferring the transaction that changed fewest rows, and rolled back all of it, releasing A. The right response to SQLSTATE 40001 is to retry the whole transaction, as Laravel's DB::transaction($callback, 3) does (Section 5.10.10). The InnoDB monitor keeps the latest deadlock:

    mysql -E -e 'SHOW ENGINE INNODB STATUS' \

    | grep -E '^(\*\*\* |UPDATE |RECORD LOCKS)' \

    | sed -E 's/space id .* (index \w+) of table (\S+) trx id [0-9]+/\1 \2/'

    Listing 3.129: Extracting the latest deadlock from SHOW ENGINE INNODB STATUS

    *** (1) TRANSACTION:

    UPDATE products SET stock = stock - 1 WHERE id = 2

    *** (1) HOLDS THE LOCK(S):

    RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap

    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

    RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap waiting

    *** (2) TRANSACTION:

    UPDATE products SET stock = stock - 1 WHERE id = 1

    *** (2) HOLDS THE LOCK(S):

    RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

    RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap waiting

    *** WE ROLL BACK TRANSACTION (2)

    Output of Listing 3.129 It names both statements, the index and the lock modes; innodb_print_all_deadlocks logs every deadlock to the error log. To avoid deadlocks, lock rows in one order (sort a cart by product ID), keep transactions short, index the columns your UPDATEs filter on, and consider READ COMMITTED. None of this rules deadlocks out, so every write path needs the retry.

    3.14.9 XA Distributed Transactions

    An XA transaction is one branch of a transaction spanning several resource managers, such as two MySQL servers. It follows the X/Open XA two-phase commit: a transaction manager sends XA PREPARE to every branch (the work becomes durable but undecided), then XA COMMIT once all have prepared, or XA ROLLBACK if any failed. A branch runs between XA START 'id' and XA END 'id'; only InnoDB supports XA.Since MySQL 8.0.29 a prepared branch is detached from its connection (xa_detach_on_prepare, on by default), so it survives a disconnect and any session can finish it: in a test run, Session B saw Session A's prepared branch in XA RECOVER and committed it with XA COMMIT. A prepared branch keeps its row locks until someone decides its fate. XA is unsafe for statement-based replication, and detached XA transactions cannot use temporary tables. PHP applications rarely need XA: keep related data in one database, or write an outbox table in the local transaction and deliver its messages afterwards.

    3.14.10 Storage Engines: InnoDB, MyISAM, MEMORY, and the Rest

    MySQL's storage engines are pluggable: each table names its engine in ENGINE= (Section 3.4.5), and one query may join tables that use different engines. SHOW ENGINES lists what the server offers; the same data in information_schema.ENGINES lets you pick columns and rows:

    SELECT engine, support, transactions, xa, savepoints

    FROM information_schema.ENGINES WHERE support <> 'NO' ORDER BY engine;

    Listing 3.130: The storage engines of a MySQL 9.7 server

    +--------------------+---------+--------------+------+------------+

    | engine | support | transactions | xa | savepoints |

    +--------------------+---------+--------------+------+------------+

    | ARCHIVE | YES | NO | NO | NO |

    | BLACKHOLE | YES | NO | NO | NO |

    | CSV | YES | NO | NO | NO |

    | InnoDB | DEFAULT | YES | YES | YES |

    | MEMORY | YES | NO | NO | NO |

    | MRG_MYISAM | YES | NO | NO | NO |

    | MyISAM | YES | NO | NO | NO |

    | PERFORMANCE_SCHEMA | YES | NO | NO | NO |

    +--------------------+---------+--------------+------+------------+

    8 rows in set (0.000 sec)

    Output of Listing 3.130 Only InnoDB supports transactions, XA and savepoints; FEDERATED (remote tables) and NDB (MySQL Cluster) are present but disabled. The others have narrow uses: Feature InnoDB MyISAM MEMORY ARCHIVE Transactions and foreign keys Yes No No No Locking Row Table Table Row Crash recovery Automatic, from redo log None; REPAIR TABLE Rows lost on restart None Indexes B-tree, full-text, spatial B-tree, full-text, spatial Hash, B-tree AUTO_INCREMENT key only Good for Almost everything Legacy read-mostly data Small scratch lookups Write-once logs Table 3.10: The four storage engines you are most likely to meet MyISAM ignores transactions: in a test run, a MyISAM INSERT survived ROLLBACK, which warned "Some non-transactional changed tables couldn't be rolled back" (1196). MEMORY tables are capped by max_heap_table_size (16 MB by default). ARCHIVE compresses rows and refuses UPDATE and DELETE, CSV stores a comma-separated file, and BLACKHOLE discards rows but still writes the binary log. ALTER TABLE t ENGINE = InnoDB converts an old table by rebuilding it.