Transactions and ACID

MySQL Course · lesson 7 of 21 · 5 min read

Several statements, one all-or-nothing unit - and what happens when it fails midway.

Open this lesson in the learning hub

Key points

  • A transaction groups statements so they either all apply or none do.
  • Atomicity is that guarantee; a crash mid-way rolls everything back.
  • Consistency means constraints still hold afterwards; Durability means a commit survives a crash.
  • InnoDB supports transactions; the older MyISAM engine does not, which is why InnoDB replaced it as the default.
  • Keep transactions short - they hold row locks, and a long one blocks everyone else.

Example

START TRANSACTION;

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

-- both applied, or neither
COMMIT;
-- ROLLBACK;  would undo both

A transaction is a promise that a half-finished change never becomes visible.

Indexes: why one query is slow Isolation levels

This is a reading copy. The full lesson — with the visual explainer, the interactive lab and a Run button for the code — lives in the MySQL Course course, and every lesson in it is listed on the MySQL Course contents page.