Transactions and isolation (practical level)

Transactions and isolation at a practical level: grouping changes so they commit together or roll back, and understanding enough isolation to avoid nasty surprises without memorising every edge case. Is one of those topics that pays off as soon as you use it on a real page.

Why transactions matter

Transferring stock for an order might touch three tables: insert order, insert line items, decrement product stock. If the server dies after step two, you have an order with no stock movement – or stock removed with no order. A transaction wraps those steps: all succeed or none do.

Basic syntax

START TRANSACTION;

INSERT INTO orders (customer_id, status) VALUES (5, 'paid');
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, 12, 2, 19.99);

UPDATE products SET stock = stock - 2 WHERE id = 12 AND stock >= 2;

-- If the UPDATE matched 0 rows, stock was insufficient
-- ROLLBACK; otherwise:
COMMIT;

START TRANSACTION (or BEGIN) opens the unit. COMMIT makes changes permanent. ROLLBACK undoes everything since the transaction started.

InnoDB and autocommit

InnoDB (the default storage engine) supports transactions. With autocommit on (default), each statement is its own transaction. Turn it off for multi-statement units, or use explicit START TRANSACTION which overrides autocommit until COMMIT or ROLLBACK.

SHOW VARIABLES LIKE 'autocommit';
SET autocommit = 0;  -- session only; prefer explicit START TRANSACTION

ACID in plain terms

Atomicity – all or nothing
Consistency – constraints still hold after commit (foreign keys, checks)
Isolation – concurrent transactions do not step on each other (in theory)
Durability – committed data survives a crash (InnoDB redo logs)

You feel isolation and atomicity most often in application code.

Isolation levels (practical view)

MySQL InnoDB default is REPEATABLE READ. For most web apps you leave it alone. Know the phenomena:

Dirty read – reading uncommitted data from another transaction (InnoDB prevents this)
Non-repeatable read – same row read twice, value changed because another transaction committed in between
Phantom read – same query run twice, new matching rows appeared

-- Inspect or change session isolation (rare in app code)
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Row locking and deadlocks

Updates lock rows. Two transactions updating the same rows in opposite order can deadlock – MySQL kills one and returns an error. Retry the failed transaction in application code. Keep transactions short; do not hold locks while waiting for HTTP calls or user input.

-- Pessimistic lock when you must read then update safely
START TRANSACTION;
SELECT stock FROM products WHERE id = 12 FOR UPDATE;
-- app checks stock, then updates
UPDATE products SET stock = stock - 2 WHERE id = 12;
COMMIT;

Savepoints

Partial rollback inside a transaction:

START TRANSACTION;
INSERT INTO audit_log (message) VALUES ('start');
SAVEPOINT before_risky;
INSERT INTO orders (customer_id) VALUES (999);  -- might fail FK
-- ROLLBACK TO SAVEPOINT before_risky;
COMMIT;

Useful in complex scripts; most app code uses a single COMMIT/ROLLBACK boundary.

Application habits

Wrap money, inventory, and double-entry style updates in transactions
Handle deadlock errors with retry
Let PHP/Python DB libraries manage transactions – they expose begin, commit, rollback
Do not mix MyISAM tables (no transactions) with InnoDB in the same logical unit

The next tutorial covers constraints – foreign keys, UNIQUE, and CHECK – so the database enforces rules even when application code slips.