So far each statement stood alone. Real operations often need several statements that must succeed or fail together, while other connections read and write the same rows. Transactions and InnoDB’s locking make that safe. This is the lesson that prevents the bugs that only appear under load.
ACID in one table
| Property | Promise |
|---|---|
| Atomicity | All statements in a transaction take effect, or none do |
| Consistency | Constraints hold before and after every transaction |
| Isolation | Concurrent transactions do not see each other’s half-finished work |
| Durability | Once COMMIT returns, the change survives a crash |
InnoDB provides all four. MyISAM provides none, one more reason it has no place in new schemas.
Autocommit and explicit transactions
By default MySQL runs in autocommit mode: each statement is its own transaction, committed as soon as it succeeds. To group statements, start a transaction explicitly:
START TRANSACTION;
DELETE FROM note_tags WHERE note_id = 4;
SELECT COUNT(*) FROM note_tags;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
Inside the transaction you see your own change. Nobody else does yet. Change your mind:
ROLLBACK;
SELECT COUNT(*) FROM note_tags;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
COMMIT makes the changes permanent and visible; ROLLBACK throws them away. If the connection drops in the middle, InnoDB rolls back automatically. BEGIN is a synonym for START TRANSACTION.
A real Orbit operation, moving a note to another user and tagging it, looks like this in application code:
START TRANSACTION;
UPDATE notes SET user_id = 3 WHERE id = 3;
INSERT INTO note_tags (note_id, tag_id) VALUES (3, 1);
COMMIT;
If the insert fails, the application calls ROLLBACK and the note stays with its original owner. Every database driver has helpers for this pattern; use them. (If you try it at the prompt, finish with ROLLBACK instead of COMMIT so Orbit’s data stays as the next lessons expect.)
Warning: DDL commits implicitly. CREATE, ALTER, DROP, TRUNCATE, RENAME and account statements such as GRANT end the current transaction with a commit before they run. You cannot roll back a schema change, and any pending changes before it are committed too.
Savepoints
A savepoint marks a point inside a transaction you can roll back to without abandoning the whole transaction:
START TRANSACTION;
UPDATE users SET plan = 'pro' WHERE id = 1;
SAVEPOINT before_tags;
DELETE FROM note_tags WHERE note_id = 1;
ROLLBACK TO SAVEPOINT before_tags; -- the tags are back, the plan change remains
ROLLBACK; -- undo everything for this demo
MVCC: readers never block writers
InnoDB uses multi-version concurrency control. When a row changes, the old version is kept in the undo log for as long as any transaction might still need it. A plain SELECT reads from a consistent snapshot and never waits for locks, and writers never wait for readers.
Under the default isolation level, the snapshot is taken at the transaction’s first read and kept until it ends. Two sessions show what that means:
| Time | Session A | Session B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT plan FROM users WHERE id = 1; → free | |
| 3 | UPDATE users SET plan = 'pro' WHERE id = 1; (autocommit) | |
| 4 | SELECT plan FROM users WHERE id = 1; → still free | |
| 5 | COMMIT; | |
| 6 | SELECT plan FROM users WHERE id = 1; → pro |
Session A sees a stable picture of the data for its whole transaction, which makes multi-query reports consistent. (Session B’s change is just for the demo; set Ana back with UPDATE users SET plan = 'free' WHERE id = 1;.)
Isolation levels
| Level | A plain SELECT sees | Notes |
|---|---|---|
READ UNCOMMITTED | Even uncommitted changes (“dirty reads”) | Almost never appropriate |
READ COMMITTED | The latest committed data at each statement | Fewer locks; PostgreSQL’s default |
REPEATABLE READ | One snapshot for the whole transaction | InnoDB default |
SERIALIZABLE | Like REPEATABLE READ, but plain SELECTs take shared locks | Strictest, most blocking |
SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
Change it for the next transaction with SET TRANSACTION ISOLATION LEVEL READ COMMITTED;, for the session with SET SESSION transaction_isolation = 'READ-COMMITTED';, or server-wide in my.cnf. Many high-traffic applications run READ COMMITTED because it takes fewer gap locks and reduces deadlocks; the default is fine to start with.
Locking reads
Snapshots are perfect for reading but wrong for “read, then update based on what I read”. Two requests buying the last ticket would both see one ticket left. A locking read reads the latest committed version and locks the rows until the transaction ends:
START TRANSACTION;
SELECT plan FROM users WHERE id = 2 FOR UPDATE; -- others now wait to change this row
UPDATE users SET plan = 'free' WHERE id = 2;
ROLLBACK; -- a real downgrade would COMMIT here
| Clause | Lock | Others can |
|---|---|---|
FOR SHARE | Shared | Read and also FOR SHARE, but not modify |
FOR UPDATE | Exclusive | Only do plain (snapshot) reads |
... NOWAIT | Same, but fail immediately if a row is locked | |
... SKIP LOCKED | Same, but silently skip locked rows |
Writes (UPDATE, DELETE, INSERT) lock the rows they touch automatically. In REPEATABLE READ, InnoDB also locks the gaps between index entries it scanned (next-key locks) so that no other transaction can insert a “phantom” row matching your condition. That is why an UPDATE with a WHERE on an unindexed column can block far more than you expect: it scans, and locks, the whole table. Index the columns you filter writes on.
A job queue with SKIP LOCKED
SKIP LOCKED (MySQL 8.0+) lets several workers take jobs from the same table without blocking each other:
START TRANSACTION;
SELECT id, payload FROM email_jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- send the emails, then:
UPDATE email_jobs SET status = 'sent' WHERE id IN (...);
COMMIT;
Each worker gets a different batch; rows another worker holds are simply skipped.
Lock waits and deadlocks
When a transaction needs a row another transaction has locked, it waits up to innodb_lock_wait_timeout seconds (50 by default), then fails:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
A deadlock happens when two transactions each hold a lock the other needs. To reproduce one, open two sessions, run START TRANSACTION; in both, follow the timeline and finish with ROLLBACK;:
| Time | Session A | Session B |
|---|---|---|
| 1 | UPDATE notes SET pinned = 1 WHERE id = 1; | |
| 2 | UPDATE notes SET pinned = 1 WHERE id = 2; | |
| 3 | UPDATE notes SET pinned = 1 WHERE id = 2; waits | |
| 4 | UPDATE notes SET pinned = 1 WHERE id = 1; |
InnoDB detects the cycle immediately and rolls back one transaction, usually the one that changed fewer rows:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Deadlocks are normal in busy systems. Handle them properly:
- Retry the whole transaction when you get error 1213 (and usually 1205). The error means nothing was saved.
- Touch rows in a consistent order (for example by id) in every code path.
- Keep transactions short: no user input, HTTP calls or sleeps while a transaction is open.
- Index the columns your writes filter on, so fewer rows are locked.
To see what is blocking right now, and what the last deadlock looked like:
SELECT * FROM sys.innodb_lock_waits\G
SHOW ENGINE INNODB STATUS\G -- see the LATEST DETECTED DEADLOCK section
performance_schema.data_locks lists every lock currently held. Set innodb_print_all_deadlocks = ON to write every deadlock to the error log.
Metadata locks
Every statement that uses a table takes a metadata lock on it, and DDL needs an exclusive one. An ALTER TABLE therefore waits for every open transaction that has touched the table, and every new query then queues behind the waiting ALTER. SHOW PROCESSLIST shows them all in the state Waiting for table metadata lock.
The usual culprit is an idle connection with an open transaction, for example a forgotten interactive session. Find and end it before running DDL:
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;
KILL <thread id>; ends that connection and rolls back its transaction.
Long transactions are expensive
A transaction open for hours keeps every old row version it might need, so the undo log (the “history list”) grows, purge falls behind, and queries slow down as they skip ever more versions. It also holds its locks the whole time. Monitor trx_started in INNODB_TRX and alert on transactions older than a few minutes.
Explicit table locks
LOCK TABLES t WRITE; ... UNLOCK TABLES; locks whole tables. It was needed with MyISAM; with InnoDB, transactions and row locks are almost always better. You may still meet it in old maintenance scripts.
Quick reference
| Task | SQL |
|---|---|
| Begin / end | START TRANSACTION; COMMIT; ROLLBACK; |
| Partial undo | SAVEPOINT s; ROLLBACK TO SAVEPOINT s; |
| Isolation level | SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| Lock rows you will update | SELECT ... FOR UPDATE; |
| Fail fast / skip locked rows | ... FOR UPDATE NOWAIT; / ... FOR UPDATE SKIP LOCKED; |
| Who blocks whom | SELECT * FROM sys.innodb_lock_waits\G |
| Last deadlock | SHOW ENGINE INNODB STATUS\G |
| Open transactions | SELECT * FROM information_schema.INNODB_TRX; |
Exercise
Inside a transaction you run UPDATE, then ALTER TABLE, then ROLLBACK. What happens to the UPDATE?
Show answer
Answer: B. DDL statements cause an implicit commit before they execute. The UPDATE became permanent at that moment, and the ALTER itself cannot be rolled back either.
Exercise
Several workers take jobs from the same table. Which clause lets each worker grab different rows without waiting for the others?
Show answer
Answer: D. SKIP LOCKED locks the rows it can and silently skips rows other transactions already hold, which is exactly what a queue needs. NOWAIT would raise an error instead.
Exercise
Your application receives ERROR 1213, Deadlock found. What is the correct reaction?
Show answer
Answer: A. InnoDB resolves a deadlock by rolling back one transaction entirely. Nothing was saved, so the safe response is to run the transaction again.
Key takeaways
- Group related changes in
START TRANSACTION ... COMMIT; DDL commits implicitly and cannot be rolled back. - InnoDB’s MVCC gives plain reads a consistent snapshot without locking; REPEATABLE READ is the default.
- Use
SELECT ... FOR UPDATEwhen you read in order to write;SKIP LOCKEDbuilds job queues. - Deadlocks are normal: retry on error 1213, lock rows in a consistent order, keep transactions short.
- Idle open transactions block DDL through metadata locks and bloat the undo log.
FAQ
Does autocommit = 0 make my session transactional?
Yes, but in a confusing way: every statement then silently opens a transaction that stays open until you commit, which is how idle sessions end up holding locks for hours. Prefer leaving autocommit on and using explicit START TRANSACTION.
Which isolation level should my application use?
Start with the default REPEATABLE READ. Switch to READ COMMITTED if you see many gap-lock waits or deadlocks on insert-heavy tables, after testing that your code does not rely on repeatable snapshots.
Are single statements atomic without a transaction?
Yes. In autocommit mode each statement is its own transaction: an UPDATE that changes a million rows either changes all of them or, on error, none.
Why did my SELECT … FOR UPDATE lock rows I did not return?
In REPEATABLE READ, InnoDB locks every index record it scanned plus the gaps between them. Without a suitable index, that can be the whole table. Add an index on the filter columns or use READ COMMITTED for that transaction.