Log in Sign up

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.

EngineNotes
InnoDBtransactions, row-level locking, foreign keys, crash recovery – the default
MyISAMold; no transactions, locks the whole table, can be damaged by crashes
MEMORYdata in RAM only, lost on restart
AriaMariaDB'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 permanent

If 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:

LetterMeaning
Atomicityall or nothing
Consistencyconstraints (keys, foreign keys) are always respected
Isolationparallel transactions do not see each other's unfinished work
Durabilityafter 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

LevelWhat other transactions' changes you can see
READ UNCOMMITTEDeven uncommitted changes ("dirty reads")
READ COMMITTEDcommitted changes, re-read each statement
REPEATABLE READdefault in InnoDB: a consistent snapshot for the whole transaction
SERIALIZABLEstrictest, 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 WHERE of UPDATE/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 whom

Error 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.

Next: MySQL Users, Privileges and Security

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.