Skip to content
Tutorials Center
My learning

Topics

Lesson 56 of 75 ยท Transactions and Concurrency

Transactions and ACID in InnoDB

Learn MySQL transactions in InnoDB: ACID, autocommit, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, statements that commit implicitly, and durability settings.

A transaction groups several statements into one unit of work: either all of them take effect, or none do. Moving money between two accounts is the classic example. Subtracting from one balance and adding to the other must succeed or fail together, otherwise money appears or vanishes. InnoDB, the default MySQL storage engine, gives you full transactions, and nearly every serious application depends on them.

This lesson explains the four ACID guarantees, how autocommit changes what "one statement" means, the commands START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT, the statements that quietly commit for you, and the settings that decide how safe a commit is when the power fails. You can try the transaction commands live, because SQLite supports the same ideas.

ACID in four words

Atomic All the statements apply, or none do.
Consistent Constraints hold before and after.
Isolated Concurrent work does not see half-done changes.
Durable A committed change survives a crash.
1 / 2

What ACID means in practice

Atomicity is "all or nothing". InnoDB records the old version of every row it changes in an undo log, so a rollback (or a crash before commit) restores the original values. Consistency means your constraints (primary keys, foreign keys, CHECK, NOT NULL) are never left violated by a committed transaction; the database enforces the rules, but you still design the rules (Keys and Constraints). Isolation controls how much one running transaction sees of another's work, and it has its own lessons: Isolation Levels and MVCC and Locking, Gap Locks and Deadlocks. Durability means that once COMMIT returns, the change is on disk even if the server dies a millisecond later, which InnoDB achieves with the redo log (InnoDB Architecture).

Only transactional engines give you these guarantees. In InnoDB they apply to every table. The old MyISAM engine ignores ROLLBACK for its tables, which is one of the reasons it is no longer used for ordinary data (Storage Engines Overview).

Autocommit: every statement is a transaction

MySQL starts each session with autocommit on. Each statement is then its own tiny transaction that commits as soon as it finishes:

SQL
SELECT @@autocommit;      -- 1

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

If the server crashes between those two statements, account 1 has lost 100 and nobody has gained it. The fix is to put both statements in one transaction. You do that in either of two ways. The usual one is to start the transaction explicitly, which suspends autocommit until you end it. The other is SET autocommit = 0, after which every statement joins an open transaction that you must end yourself with COMMIT or ROLLBACK. The second style is easy to forget about and leaves transactions open for hours, so prefer explicit START TRANSACTION, and let your driver's transaction helper do it for you (MySQL from PHP with PDO).

START TRANSACTION, COMMIT and ROLLBACK

The commands are the same in MySQL and SQLite apart from the opening keyword: MySQL uses START TRANSACTION or BEGIN, while SQLite uses BEGIN (and START TRANSACTION is not accepted).

SQL
START TRANSACTION;

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

COMMIT;      -- make both changes permanent

If something goes wrong, ROLLBACK throws away everything since the transaction began. Try both outcomes below. The first transfer commits; the second is rolled back, because we decide halfway that the sender cannot afford it:

SQL COMMIT keeps changes, ROLLBACK discards them

Press Run (or Ctrl + Enter) to see the result here.

After the first block, Ada has 300 and Ben 300. Inside the second transaction, the SELECT shows Ada with 50, because a transaction always sees its own changes. After ROLLBACK she is back to 300.

A transaction also ends when the connection drops: MySQL rolls back anything uncommitted. A crash of the server does the same during recovery, which is atomicity at work.

Errors inside a transaction

MySQL does not roll back the whole transaction when a single statement fails. A statement that violates a constraint is undone on its own and the transaction stays open, so your code must decide what to do next. Failing to check, then calling COMMIT, would commit the other statements and silently skip the failed one. The pattern is always: run the statements, and if any call raises an error, run ROLLBACK. Some errors do roll the transaction back for you; a deadlock is one (Locking, Gap Locks and Deadlocks).

The next example shows a constraint stopping a bad update. The CHECK refuses a negative balance, so the second statement of the transfer fails, and a ROLLBACK restores the first:

SQL A failed statement means you must roll back

Press Run (or Ctrl + Enter) to see the result here.

The run stops with a CHECK constraint failed error at the second UPDATE; that is the point of the demo. In a real application you would catch the error and issue the ROLLBACK yourself, and the table would show 100 for both people.

SAVEPOINT: partial rollback

A savepoint is a named mark inside a transaction. You can roll back to it, undoing only the work after it, and then continue the same transaction:

SQL
START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (7, 120.00);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 99, 1);
-- product 99 turns out to be unavailable
ROLLBACK TO SAVEPOINT after_order;

-- the order row is still here; try another item
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 12, 1);

COMMIT;

RELEASE SAVEPOINT name removes a savepoint without undoing anything. Rolling back to a savepoint does not end the transaction, and it does not release the locks taken after the savepoint in a way you can count on, so do not use savepoints as a lock-management trick. Savepoints are how frameworks implement "nested transactions": the inner call sets a savepoint and rolls back to it on failure.

SQL ROLLBACK TO SAVEPOINT undoes only part of the work

Press Run (or Ctrl + Enter) to see the result here.

The first SELECT shows two rows, the last shows base order and safe extra. The rolled-back row is gone, and its id (2) is reused here by SQLite. MySQL's AUTO_INCREMENT behaves differently: a rolled-back insert still burns its id, so you would see a gap (AUTO_INCREMENT, UUIDs and ID Strategies).

Statements that commit implicitly

Some statements end the current transaction on their own, committing it first. Most are DDL and administrative statements:

  • CREATE, ALTER, DROP, RENAME and TRUNCATE TABLE on tables, indexes, views and databases
  • CREATE USER, GRANT, REVOKE
  • LOCK TABLES and UNLOCK TABLES
  • START TRANSACTION itself, when a transaction is already open (it commits the old one)
  • SET autocommit = 1, when it was 0

Each of these performs an implicit commit before it runs, and the DDL statements cannot be rolled back afterwards. The danger is a migration script that mixes data changes and DDL and hopes to undo everything on failure:

SQL
START TRANSACTION;
UPDATE products SET price = price * 1.1;
ALTER TABLE products ADD COLUMN discontinued TINYINT NOT NULL DEFAULT 0;  -- commits the UPDATE!
ROLLBACK;                                                                 -- too late, nothing to undo

Keep DDL out of transactions you expect to roll back. SQLite is different here (its DDL is transactional), so do not trust SQLite to demonstrate this MySQL behavior. In MySQL 8, DDL statements are at least atomic on their own: a failed ALTER TABLE leaves the table unchanged, thanks to the transactional data dictionary (Data Dictionary and information_schema).

Durability settings

A commit is only as durable as the settings behind it. Two variables matter most:

Variable Value Meaning
innodb_flush_log_at_trx_commit 1 (default) Write and flush the redo log to disk at every commit. Full durability.
2 Write at commit, flush about once per second. An OS crash can lose about a second.
0 Write and flush about once per second. A server crash can lose about a second.
sync_binlog 1 Flush the binary log at every commit, which keeps it consistent with InnoDB.
0 Let the operating system decide when to flush. Faster, can lose binlog events.

The defaults (innodb_flush_log_at_trx_commit = 1, sync_binlog = 1) give the "no committed transaction is ever lost" guarantee, and they cost one disk sync per commit. Relaxing them can raise write throughput a lot, but you accept losing the last second of commits in a crash. That trade is sometimes right (a cache table, a bulk import you can redo) and nearly never right for orders or payments. Tuning is covered in Server Configuration and Tuning.

Keep transactions short

A transaction holds its locks until it ends and forces InnoDB to keep old row versions for as long as it lives. A few habits help:

  • Open the transaction late, do the minimum inside it, and commit quickly.
  • Never wait for a user, an HTTP call or a slow job while a transaction is open.
  • Batch big imports into chunks of a few thousand rows rather than one transaction of millions.
  • Check information_schema.INNODB_TRX to find transactions that have been open too long.

What ran live here

BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO and a failing CHECK ran in SQLite, which implements the same transaction model. START TRANSACTION, autocommit, implicit commits, the InnoDB durability variables and information_schema.INNODB_TRX are MySQL-specific and shown as read-only code.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. With autocommit on (the default), what happens to two separate UPDATE statements you send one after the other?

    Autocommit makes every statement its own transaction. To make several statements succeed or fail together, start an explicit transaction first.
  2. 2. A statement inside an open transaction violates a CHECK constraint. What does MySQL do with the rest of the transaction?

    A failing statement is undone on its own, but the transaction continues. Your code must check for the error and issue ROLLBACK (or handle it) before committing.
  3. 3. Why is it risky to put ALTER TABLE in the middle of a transaction you may want to roll back?

    DDL statements commit the current transaction before they run. Anything done earlier is permanent, and a later ROLLBACK has nothing left to undo.
  4. 4. Which setting gives the strongest durability for the InnoDB redo log?

    Value 1 flushes the redo log to disk on every commit, so no committed transaction is lost in a crash. Values 0 and 2 can lose about a second of commits.

Summary

  • A transaction makes several statements atomic; InnoDB provides ACID using undo and redo logs.
  • Autocommit makes every statement its own transaction. Use START TRANSACTION ... COMMIT or ROLLBACK for multi-statement work.
  • A failed statement does not roll back the transaction by itself: handle the error and roll back.
  • SAVEPOINT and ROLLBACK TO undo part of a transaction; frameworks use them for nested transactions.
  • DDL, GRANT and LOCK TABLES commit implicitly, so keep them out of transactions you want to undo.
  • innodb_flush_log_at_trx_commit = 1 with sync_binlog = 1 is the safe default; keep transactions short.

Next: Isolation Levels and MVCC.