Transactions

A transaction is a sequence of SQL statements that executes as a single logical unit of work. Either all statements succeed and their changes are committed permanently, or one fails and all changes are rolled back — leaving the database in its previous consistent state.

ACID Properties

InnoDB transactions are fully ACID compliant:

• Atomicity — All statements in the transaction succeed or all are rolled back.
• Consistency — The database moves from one valid state to another, respecting all constraints.
• Isolation — Concurrent transactions do not see each other's uncommitted changes.
• Durability — Once committed, changes survive a crash because they are written to the redo log.

Basic Transaction Syntax

START TRANSACTION;

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

COMMIT;

If anything goes wrong between START TRANSACTION and COMMIT, issue a ROLLBACK to undo all changes:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Something went wrong...
ROLLBACK;

Autocommit

By default, MySQL runs in autocommit mode — every individual SQL statement is automatically wrapped in its own transaction and committed immediately. To use explicit multi-statement transactions, either call START TRANSACTION (which temporarily disables autocommit) or set autocommit = 0 for the session:

SET autocommit = 0;

Savepoints

Savepoints allow you to roll back to a specific point within a transaction without abandoning the entire transaction:

START TRANSACTION;

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

INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 3, 2);
-- This insert fails validation...
ROLLBACK TO SAVEPOINT after_order;

-- The order row still exists; only the item insert was rolled back
COMMIT;

Isolation Levels

MySQL InnoDB supports four isolation levels, controlled with:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The levels from lowest to highest isolation are: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (the InnoDB default), and SERIALIZABLE. Higher isolation prevents more anomalies (dirty reads, non-repeatable reads, phantom reads) at the cost of reduced concurrency.

Transactions are essential for any operation that must succeed or fail as a whole — bank transfers, order placements, inventory adjustments, and more.