ACID and Transactions

Article · 3:00

The four guarantees, and the statements that let you use them.

A transaction groups statements so they succeed or fail together. The guarantees are remembered as ACID. Atomicity: all of it or none of it, so a transfer never debits without crediting. Consistency: the database moves from one valid state to another, with constraints enforced. Isolation: concurrent transactions do not see each other's half-finished work. Durability: once committed, it survives a crash.

By default MySQL runs in autocommit, so every statement is its own transaction. START TRANSACTION suspends that until you COMMIT or ROLLBACK. A savepoint is a marker you can roll back to without abandoning the whole transaction, which is useful in a long routine where one optional step may fail.

Isolation has levels, and the default matters. MySQL's default is REPEATABLE READ, which means that within one transaction the same SELECT returns the same rows each time, even if somebody else commits a change in between. READ COMMITTED - PostgreSQL's default - sees each committed change, which is often what application developers actually expect. The difference causes real confusion when a developer moves between the two databases, and it is worth knowing which one you are on.

Keep transactions short. A transaction holds locks from its first write until it commits, so one that opens, calls an external API and then commits has been holding locks for the length of a network request.

SELECT @@autocommit, @@transaction_isolation;

-- Atomicity: both updates, or neither. The money cannot vanish.
START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Savepoints: abandon part of the work without losing all of it.
START TRANSACTION;
  INSERT INTO orders (customer_id, reference, status)
  VALUES (1, 'ORD0000042', 'pending');

  SAVEPOINT order_created;

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (LAST_INSERT_ID(), 999999, 1, 0.00);     -- suppose this fails

  ROLLBACK TO SAVEPOINT order_created;            -- the order survives
COMMIT;

-- REPEATABLE READ: this SELECT returns the same rows both times, even
-- if another session commits a change in between. READ COMMITTED - the
-- PostgreSQL default - would show the change.
START TRANSACTION;
  SELECT total FROM orders WHERE id = 1;
  -- another session commits an UPDATE here
  SELECT total FROM orders WHERE id = 1;          -- unchanged
COMMIT;

-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- DDL commits implicitly, so this ends the transaction whatever you meant.
-- START TRANSACTION; CREATE TABLE t (id INT); ROLLBACK;  -- table remains