#Transactions and ACID
AddedA transaction is a group of statements that the database treats as one unit: either all of their changes happen, or none do. The classic example is a bank transfer — subtract 200 from Rohit, add 200 to Maya. If the server crashes or the second statement fails between the two, you must never end up with the money gone from one account and not arrived in the other.
ACID names the four guarantees, and in InnoDB each one is backed by a concrete mechanism:
| Letter | Guarantee | How InnoDB provides it |
|---|---|---|
| Atomicity | All of the transaction's changes or none | The undo log: every change records how to reverse it; ROLLBACK (or crash recovery) applies those records |
| Consistency | A transaction moves the data from one valid state to another | Constraints the database checks (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL) plus the rules your code keeps inside transactions |
| Isolation | Concurrent transactions don't see each other's half-finished work | MVCC snapshots for plain reads and locks for writes and locking reads, tuned by the isolation level |
| Durability | Once COMMIT returns, the change survives a crash | The redo log is flushed to disk at commit (innodb_flush_log_at_trx_commit = 1), and the doublewrite buffer protects against torn pages |
The mental model: a transaction is a shopping basket at a till. You can fill it, change your mind and put things back; nothing leaves the shop until you pay (COMMIT), and if you walk away (ROLLBACK, disconnect, crash) everything goes back on the shelves.
A transfer that works, and one that breaks a rule halfway through:
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
SELECT * FROM accounts;
START TRANSACTION;
UPDATE accounts SET balance = balance + 1000 WHERE id = 3;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
SELECT * FROM accounts;
ROLLBACK;
SELECT * FROM accounts;mysql> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
mysql> UPDATE accounts SET balance = balance - 200 WHERE id = 1;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE accounts SET balance = balance + 200 WHERE id = 2;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.004 sec)
mysql> SELECT * FROM accounts;
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
| 1 | Rohit | 300.00 |
| 2 | Maya | 500.00 |
| 3 | Liam | 100.00 |
+----+-------+---------+
3 rows in set (0.000 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
mysql> UPDATE accounts SET balance = balance + 1000 WHERE id = 3;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
ERROR 3819 (HY000): Check constraint 'balance_not_negative' is violated.
mysql> SELECT * FROM accounts;
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
| 1 | Rohit | 300.00 |
| 2 | Maya | 500.00 |
| 3 | Liam | 1100.00 |
+----+-------+---------+
3 rows in set (0.000 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.003 sec)
mysql> SELECT * FROM accounts;
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
| 1 | Rohit | 300.00 |
| 2 | Maya | 500.00 |
| 3 | Liam | 100.00 |
+----+-------+---------+
3 rows in set (0.000 sec)What’s happening
START TRANSACTIONopens a transaction (BEGINis a synonym). The firstUPDATEtakes 200 from Rohit, the second gives it to Maya; neither change is visible to other sessions yet.COMMITmakes both changes permanent and visible at the same moment: 300 and 500. That's atomicity and durability in the happy path — and the total money (900) is unchanged, the invariant the application cares about.- The second transaction gives Liam 1000 first, then tries to take 1000 from Rohit, who only has 300. The
CHECK (balance >= 0)constraint rejects that statement:ERROR 3819 (HY000): Check constraint 'balance_not_negative' is violated. - The important part: the
SELECTstraight after the error shows Liam at 1100. In MySQL an error rolls back only the failing statement, not the transaction. The transaction is still open, holding Liam's half of the transfer. ROLLBACKundoes everything sinceSTART TRANSACTIONusing the undo log, and the table is back to 300 / 500 / 100. Application code must do this itself: catch the error, roll back, and only then report failure.
AdvancedPitfall: committing after a failed statement
AdvancedWhat durability depends on
Durability is a setting, not a law of nature. These are the values on the sandbox, which are MySQL's defaults:
SELECT @@innodb_flush_log_at_trx_commit AS flush_log_at_commit, @@sync_binlog AS sync_binlog,
@@innodb_doublewrite AS doublewrite, @@log_bin AS binlog;+---------------------+-------------+-------------+--------+
| flush_log_at_commit | sync_binlog | doublewrite | binlog |
+---------------------+-------------+-------------+--------+
| 1 | 1 | ON | 1 |
+---------------------+-------------+-------------+--------+What’s happening
innodb_flush_log_at_trx_commit = 1: everyCOMMITwrites and fsyncs the redo log before returning. With2the log is written at commit but fsynced about once a second, and with0both happen about once a second — faster, but a crash (or, with2, an operating-system crash) can lose up to about a second of committed transactions.sync_binlog = 1: the binary log (used for replication and point-in-time recovery) is also fsynced at every commit, so the binlog and InnoDB agree after a crash. Together,1and1are the "fully durable" setting.innodb_doublewrite = ON: pages are first written to a doublewrite area, so a crash in the middle of writing a 16 KB page can't leave a half-old, half-new page that the redo log can't repair.- The fsync at commit is the main cost of a write transaction. That's why one transaction with 1,000 inserts is far faster than 1,000 autocommitted inserts — later in this chapter 20,000 one-row autocommit updates took about 20 seconds.