Transactions and ACID

Transactions and ACID

When One Step Is Not Enough

Some operations touch several rows and must succeed or fail as a unit. Transferring money between two accounts updates two balances; if the second update fails, the first must never happen alone. A transaction groups statements so the database applies all of them together.

The Classic Example

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

COMMIT makes the changes permanent. If anything goes wrong before then, ROLLBACK undoes everything since the transaction started, leaving the data untouched.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- detect a problem, then abort everything
ROLLBACK;

The ACID Guarantees

Relational databases describe transactions with four properties, remembered as ACID:

• Atomicity: the whole transaction applies or nothing does.
• Consistency: a transaction moves data from one valid state to another, preserving constraints and rules.
• Isolation: concurrent transactions do not interfere in ways that corrupt data.
• Durability: once committed, changes survive crashes and power loss.

Isolation Levels

MySQL balances isolation with performance through isolation levels. READ COMMITTED (the default in MySQL) prevents reading other transactions' uncommitted changes. The strictest level, SERIALIZABLE, makes concurrent transactions behave as if run one after another, at the cost of lower concurrency. Choose the weakest level that still protects your data.

Tables That Need Transactions

Not all storage engines support transactions. InnoDB, the default engine in modern MySQL, does; the legacy MyISAM engine does not. When you need to move money, reserve inventory, or write multiple rows that belong together, put the statements in a transaction on an InnoDB table.

Key Points

• Transactions group statements into all-or-nothing units.
• COMMIT finalizes; ROLLBACK undoes everything in the transaction.
• ACID stands for Atomicity, Consistency, Isolation, and Durability.
• Isolation levels balance data protection against concurrency.
• Use transactions for multi-row updates such as money transfers on InnoDB tables.