MySQL – Transactions

A transaction groups several statements so they all happen or none of them do. Saving an order in the pizza application writes to customer_order, then order_item, then order_item_topping — three statements that must not be allowed to half-succeed. An order with no line items is worse than no order.

The basics

START TRANSACTION;

SELECT COUNT(*) AS paid_before FROM customer_order WHERE status = 'PAID';

+-------------+
| paid_before |
+-------------+
|        8000 |
+-------------+

UPDATE customer_order SET status = 'PAID' WHERE id = 1;

SELECT COUNT(*) AS paid_inside FROM customer_order WHERE status = 'PAID';

+-------------+
| paid_inside |
+-------------+
|        8001 |
+-------------+

Inside the transaction the change is visible to you and to nobody else. Undo it:

ROLLBACK;

SELECT COUNT(*) AS paid_after_rollback FROM customer_order WHERE status = 'PAID';

+---------------------+
| paid_after_rollback |
+---------------------+
|                8000 |
+---------------------+

COMMIT makes it permanent instead. This is measured on pizza_lab — 400,000 orders, 8,000 of them PAID.

Autocommit

Autocommit is ON by default, so every statement is its own transaction unless you say otherwise. That is why a stray UPDATE at the prompt takes effect immediately with nothing to roll back.

SELECT @@autocommit;
SET autocommit = 0;      -- now statements accumulate until COMMIT or ROLLBACK

START TRANSACTION suspends autocommit until the transaction ends, which is why you rarely need to change the setting. Note that with autocommit = 0 even a plain SELECT opens a transaction that stays open — a connection sitting idle in a transaction holds locks and blocks purging, and is a genuinely common cause of trouble.

ACID, briefly

Isolation levels

SELECT @@transaction_isolation AS default_isolation;

+-------------------+
| default_isolation |
+-------------------+
| REPEATABLE-READ   |
+-------------------+

MySQL's default is REPEATABLE READ; almost every other database defaults to READ COMMITTED. That is worth knowing if you have worked on Postgres or SQL Server, because it changes behaviour under concurrency and it is a common cause of deadlocks that would not occur elsewhere — the extra gap locks that make REPEATABLE READ work are exactly what two transactions end up fighting over.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Locking reads

A plain SELECT in InnoDB takes no locks — it reads a consistent snapshot. When you intend to read a row and then change it based on what you read, that is not enough:

START TRANSACTION;
SELECT total FROM customer_order WHERE id = 5 FOR UPDATE;
-- other transactions now block on this row until we finish
UPDATE customer_order SET total = 42.00 WHERE id = 5;
ROLLBACK;

FOR UPDATE takes an exclusive lock; FOR SHARE takes a shared one that others may also hold for reading but not for writing. Without one, two transactions can both read the old value, both compute a new one, and the second silently overwrites the first — the classic lost update.

MySQL 8 adds FOR UPDATE NOWAIT (fail immediately rather than wait) and SKIP LOCKED (ignore locked rows), which is the neat way to build a work queue that several workers can consume without stepping on each other.

Savepoints

START TRANSACTION;
SAVEPOINT before_extras;
-- ... some work ...
ROLLBACK TO SAVEPOINT before_extras;   -- undo to here, transaction still open
COMMIT;

A partial rollback. Useful when part of a long transaction is allowed to fail without discarding everything before it. RELEASE SAVEPOINT discards the marker.

⚠️ DDL commits your transaction

This is the one that catches people out, and it has no equivalent in Postgres:

CREATE TABLE tx_demo (id INT PRIMARY KEY, note VARCHAR(40));
INSERT INTO tx_demo VALUES (1, 'original');

START TRANSACTION;
UPDATE tx_demo SET note = 'changed' WHERE id = 1;
CREATE TABLE tx_demo_side (id INT);     -- implicit COMMIT happens HERE
ROLLBACK;

SELECT note AS note_after_rollback FROM tx_demo WHERE id = 1;

+---------------------+
| note_after_rollback |
+---------------------+
| changed             |
+---------------------+

The ROLLBACK did nothing. MySQL has no transactional DDL: CREATE, ALTER, DROP, TRUNCATE, RENAME and several others each cause an implicit commit of whatever was open.

The practical consequences: never mix DDL into a data transaction, and a migration that fails halfway leaves the schema partly changed. That is why migration tools apply one change per changeset and why every changeset in the pizza schema declares a rollback.

Transactions from an application

@Transactional
public Order placeOrder(NewOrder request) {
    Order order = orderRepository.save(toOrder(request));   // customer_order
    itemRepository.saveAll(toItems(order, request));        // order_item
    toppingRepository.saveAll(toToppings(order, request));  // order_item_topping
    return order;                                           // commit on normal return
}

Spring commits when the method returns and rolls back on an unchecked exception. Two things to know: it rolls back on RuntimeException and Error but not on a checked exception unless you say rollbackFor; and it works by proxy, so calling a @Transactional method from another method of the same class bypasses it entirely.

Keep transactions short. A transaction holds locks for its entire life, so an HTTP call or a file upload inside one blocks other writers for as long as that takes. Do the slow work first, then open the transaction.

What to remember

• Autocommit is on; START TRANSACTION suspends it.
• MySQL defaults to REPEATABLE READ where most databases use READ COMMITTED.
• Read-then-write needs FOR UPDATE, or you get lost updates.
• DDL implicitly commits. Never mix it into a data transaction.
• Short transactions. Locks are held for the whole duration.