ACID: What Transactions Guarantee

ACID is the set of four guarantees that make a database trustworthy for important data. InnoDB, MySQL’s default engine, provides all four.

Atomicity: all or nothing

If any step fails, the whole transaction is undone. InnoDB keeps an undo log with the old versions of changed rows, so a ROLLBACK (or a crash recovery) can put them back.

1
2
3
4
5
6
7

CREATE TABLE stock (product_id INT PRIMARY KEY, quantity INT NOT NULL, CHECK (quantity >= 0)) ENGINE = InnoDB;
CREATE TABLE order_lines (id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, quantity INT NOT NULL) ENGINE = InnoDB;
INSERT INTO stock VALUES (1, 5);

START TRANSACTION;
INSERT INTO order_lines (product_id, quantity) VALUES (1, 8);
UPDATE stock SET quantity = quantity - 8 WHERE product_id = 1;

1

ERROR 3819 (HY000): Check constraint 'stock_chk_1' is violated.

The stock update fails (the check requires at least 0), and the application rolls back:

1
2

ROLLBACK;
SELECT (SELECT COUNT(*) FROM order_lines) AS order_lines, (SELECT quantity FROM stock WHERE product_id = 1) AS stock_left;

1
2
3
4
5
6

+-------------+------------+
| order_lines | stock_left |
+-------------+------------+
|           0 |          5 |
+-------------+------------+
1 row in set

The order line that was inserted a moment ago is gone, and the stock is unchanged. Without atomicity, the order would exist without the stock being reserved.

Consistency: the rules always hold

Consistency means that the rules of the database (constraints, foreign keys, unique keys, checks) hold after every transaction. The database refuses any change that would break them, as the CHECK above did (Constraints).

Be aware: the “C” in ACID only covers rules the database knows. Business rules that you did not declare as constraints (“total must equal the sum of lines”) are your application’s responsibility.

Isolation: transactions do not see each other’s half-done work

When many transactions run at once, each one should behave as if it were alone (up to a chosen level). InnoDB uses MVCC (multi-version concurrency control) and locks to do it (Isolation Levels, MVCC).

Without isolation:

• You could read a balance while another transaction is halfway through changing it.
• Two transactions could both read “5 in stock” and both sell the last 5.

Durability: committed means saved

When COMMIT returns, the data will survive a crash or power loss. InnoDB writes every change to the redo log and, by default, flushes it to disk at every commit. After a crash, recovery replays the redo log.

The settings that control it:

1

SELECT @@innodb_flush_log_at_trx_commit AS redo_flush, @@sync_binlog AS binlog_sync, @@innodb_doublewrite AS doublewrite;

1
2
3
4
5

+------------+-------------+-------------+
| redo_flush | binlog_sync | doublewrite |
+------------+-------------+-------------+
|          1 |           1 | ON          |
+------------+-------------+-------------+

(The values come from the test server. 1, 1 is the safe default.) The doublewrite buffer protects against half-written pages after a crash.

Durability has a cost: a disk flush per commit. That is why committing every single row in a loop is slow, and why batching rows per transaction is fast (Fast Inserts).

What ACID does not give you

• Protection from bad application logic. A transaction commits whatever you tell it to.
• Protection from lost updates unless you use atomic updates, locks or version checks (Updating Under Concurrency).
• Cross-database or cross-service atomicity. A transaction covers one MySQL server. A payment API call plus a database write cannot be made atomic by MySQL alone. You need patterns such as the outbox, idempotency keys or sagas.
• Durability with unsafe settings. innodb_flush_log_at_trx_commit = 2 trades some of it for speed.

ACID and NoSQL

Many NoSQL systems relax some of these guarantees to scale. Relational databases such as MySQL keep ACID as the default, which is why they remain the standard for money, orders and accounts.