Transactions and concurrency

What a transaction is

A transaction is a group of statements the database treats as one unit of work. Either all of it happens or none of it does, even after a crash. An order row without its items is corrupt data, so both inserts go in one transaction.

ACID

Four promises, and what each one buys you.

Durability is why each engine writes the change to a log first, InnoDB's redo log and PostgreSQL's write-ahead log, flushed at COMMIT and replayed after a crash.

The commands, autocommit and DDL

Transaction control is a small family: START TRANSACTION or BEGIN, COMMIT, ROLLBACK, and SAVEPOINT. A savepoint is a bookmark inside a transaction, and ROLLBACK TO SAVEPOINT undoes only what came after it while keeping the transaction open.

START TRANSACTION;                   -- BEGIN works in both engines
INSERT INTO orders (id, customer_id, total) VALUES (15, 2, 65.00);
SAVEPOINT items_start;
INSERT INTO order_items (order_id, product_id, qty) VALUES (15, 999, 1);  -- fails
ROLLBACK TO SAVEPOINT items_start;   -- keep the order, drop the failed part
INSERT INTO order_items (order_id, product_id, qty) VALUES (15, 101, 2);
COMMIT;                              -- or ROLLBACK to discard all of it

Autocommit is on by default in both engines, so every statement is its own tiny transaction. A multi-statement transaction exists only once you say START TRANSACTION or BEGIN. MySQL Autocommit is a server variable, autocommit. PostgreSQL The server has no such setting, and clients emulate the switch by sending BEGIN for you. A forgotten toggle in a GUI client is why an update that worked is never seen by anyone else.

MySQL DDL commits implicitly. Any CREATE, ALTER, DROP or TRUNCATE commits the open transaction, and the DDL itself can never be rolled back. PostgreSQL DDL is transactional: create three tables, change your mind, ROLLBACK, and the catalog is exactly as it was.

Isolation levels and the anomalies they prevent

Isolation is a dial. Turned down, transactions overlap more and three anomalies become possible: a dirty read (you read uncommitted work that then rolls back), a non-repeatable read (a row you read again has changed), and a phantom read (a row appeared, so the same query returns more rows).

MVCC: why readers do not block writers

Both engines get their isolation from MVCC, multi-version concurrency control. The engine keeps several versions of a row, and each transaction reads the versions committed as of its own snapshot, so a long report never blocks the checkout. Old versions are cleaned up later, by InnoDB's purge threads and by PostgreSQL's autovacuum, and a transaction left open for hours holds that cleanup back and bloats the server.

Locks

MVCC handles readers. Writers still take turns on the same row: an UPDATE or DELETE holds an exclusive row lock until COMMIT or ROLLBACK, and a second transaction wanting that row waits. Most "the database is hanging" tickets are that wait.

A plain SELECT takes no row locks. When the application reads a row in order to change it, use SELECT ... FOR UPDATE so two clerks cannot overwrite each other. Add SKIP LOCKED and each worker takes the next row nobody else is holding, which is the queue pattern.

-- Each worker runs this inside its own transaction
SELECT id, total FROM orders
WHERE status = 'pending'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;

A row lock wait in MySQL ends after innodb_lock_wait_timeout, 50 seconds, with ERROR 1205; PostgreSQL waits forever unless you set lock_timeout. Metadata locks are the other surprise: every statement holds a shared lock on each table definition until its transaction ends, so a long report makes your ALTER wait and every later query queues behind the ALTER.

Deadlocks

A deadlock is two transactions each waiting for a lock the other holds. Nobody can finish, so both engines detect the cycle, roll one transaction back (MySQL ERROR 1213, PostgreSQL SQLSTATE 40P01) and expect the application to retry. The cures, most effective first: touch tables and rows in a consistent order everywhere, keep transactions short, and retry on those errors. An occasional deadlock is normal; a hundred a minute is a code review.

InnoDB keeps the last one in SHOW ENGINE INNODB STATUS under LATEST DETECTED DEADLOCK: each transaction's statement, the locks it holds, the lock it waits for, and the victim. innodb_print_all_deadlocks = ON writes every deadlock to the error log.

Finding blockers and killing sessions

When the site is frozen, one session usually holds a lock and a queue has formed behind it. Find the head of the queue, kill it only if it is idle or clearly stuck, then find the code path that left the transaction open. A kill rolls the work back, which can take as long as the update did.

The state "idle in transaction" (PostgreSQL) or an innodb_trx row with an old trx_started and no query (MySQL) is the same animal: an application opened a transaction and wandered off. PostgreSQL has a backstop, idle_in_transaction_session_timeout, off by default; MySQL has nothing beyond wait_timeout, so the fix is the application.

Go deeper

• InnoDB transaction isolation levels

  Each level, plus gap locking.

• PostgreSQL transaction isolation

  The anomaly table in full.

• MySQL statements that cause an implicit commit

  The full list.

• InnoDB locking reads

  FOR UPDATE, NOWAIT, SKIP LOCKED.

• PostgreSQL explicit locking

  Lock modes and deadlocks.