Storage engines
MySQL stores tables through a storage engine. Today you should use InnoDB for practically everything – it is the default since MySQL 5.5.
| Engine | Notes |
|---|---|
| InnoDB | transactions, row-level locking, foreign keys, crash recovery – the default |
| MyISAM | old; no transactions, locks the whole table, can be damaged by crashes |
| MEMORY | data in RAM only, lost on restart |
| Aria | MariaDB's crash-safe replacement for MyISAM (used for internal tables) |
Find tables still using another engine:
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine <> 'InnoDB'
AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');
ALTER TABLE old_table ENGINE=InnoDB; -- convert (take a backup first)What a transaction is
A transaction groups several statements so that either all of them happen or none. Classic example: moving money between two accounts.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- make it permanentIf something goes wrong in between, ROLLBACK; undoes everything since START TRANSACTION. If the connection drops or the server crashes before COMMIT, InnoDB rolls back automatically.
Transactions follow the ACID rules:
| Letter | Meaning |
|---|---|
| Atomicity | all or nothing |
| Consistency | constraints (keys, foreign keys) are always respected |
| Isolation | parallel transactions do not see each other's unfinished work |
| Durability | after COMMIT the data survives a crash |
Autocommit
By default every single statement is its own transaction and is committed immediately (autocommit = 1). START TRANSACTION switches this off until the next COMMIT or ROLLBACK.
SELECT @@autocommit;Statements that cannot be rolled back
DDL statements such as CREATE, ALTER, DROP, TRUNCATE and RENAME cause an implicit commit – they commit anything pending and cannot be undone.
Savepoints
START TRANSACTION;
INSERT INTO orders ...;
SAVEPOINT before_items;
INSERT INTO order_items ...;
ROLLBACK TO SAVEPOINT before_items; -- undo only the items
COMMIT;Isolation levels
| Level | What other transactions' changes you can see |
|---|---|
READ UNCOMMITTED | even uncommitted changes ("dirty reads") |
READ COMMITTED | committed changes, re-read each statement |
REPEATABLE READ | default in InnoDB: a consistent snapshot for the whole transaction |
SERIALIZABLE | strictest, plain reads also take locks |
SELECT @@transaction_isolation; -- MySQL 8 (MariaDB: @@tx_isolation in older versions)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;The default is right for most applications.
Locking
InnoDB locks rows, not whole tables. Normal SELECTs read a snapshot and do not wait for locks. To read a row and make sure nobody else changes it before you update it:
START TRANSACTION;
SELECT stock FROM products WHERE id = 7 FOR UPDATE; -- locks the row
UPDATE products SET stock = stock - 1 WHERE id = 7;
COMMIT;FOR SHARE (MySQL 8; LOCK IN SHARE MODE in older versions and MariaDB) allows others to read but not change the row.
Locks are only precise when the WHERE uses an index. Without one, InnoDB may lock far more rows than intended.
Deadlocks
A deadlock happens when two transactions each wait for a row the other has locked. InnoDB detects this, rolls one of them back and returns error 1213 ("Deadlock found when trying to get lock"). The application should simply retry that transaction.
To reduce deadlocks:
- keep transactions short
- touch rows in the same order everywhere
- index the columns used in
WHEREofUPDATE/DELETE
See the last deadlock and current lock waits:
SHOW ENGINE INNODB STATUS\G -- section "LATEST DETECTED DEADLOCK"
SELECT * FROM sys.innodb_lock_waits\G -- MySQL 8: who is waiting for whomError 1205 ("Lock wait timeout exceeded") means a transaction waited longer than innodb_lock_wait_timeout (default 50 s) – usually because another transaction was left open. Find it with SHOW PROCESSLIST and KILL <id> if needed.
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.