MySQL Transactions, Isolation, and Concurrency

InnoDB transactions protect database invariants by committing related changes atomically and controlling how concurrent statements observe them. Isolation is not a replacement for constraints: use primary, unique, foreign-key, and check constraints for invariants the schema can express, then select a transaction and locking pattern for multi-statement decisions.

Transaction Boundaries and Autocommit #

MySQL enables autocommit by default, so each statement outside an explicit transaction commits or rolls back as one transaction. Start a multi-statement unit explicitly and keep it short:

sql

START TRANSACTION;

UPDATE account
SET balance = balance - 100
WHERE id = 42 AND balance >= 100;

UPDATE account
SET balance = balance + 100
WHERE id = 84;

COMMIT;

Check the first update’s affected-row count before crediting the destination. Use ROLLBACK on any failed invariant or statement. A connection pool must restore autocommit, isolation, access mode, and other session state before returning a connection, because those properties survive between transactions.

Many data-definition and account-management statements implicitly commit. Atomic DDL makes a supported schema statement crash-safe; it does not make that statement part of the surrounding application transaction.

Isolation Configuration #

The MySQL 8.4 default for InnoDB is REPEATABLE READ. Configure the server default with --transaction-isolation or transaction_isolation, and override it only at a deliberate scope:

sql

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

GLOBAL establishes the default for new sessions. SESSION applies to subsequent transactions on the connection. An unqualified SET TRANSACTION applies to the next transaction. READ ONLY and READ WRITE can be selected with the same statement.

MVCC and Consistent Reads #

InnoDB keeps earlier row versions in undo data while active snapshots can still need them. A plain SELECT normally performs a nonlocking consistent read:

READ UNCOMMITTED can expose uncommitted versions.
READ COMMITTED creates a fresh snapshot for each consistent read.
REPEATABLE READ creates a snapshot at the first consistent read and reuses it for later consistent reads in that transaction.
SERIALIZABLE converts plain SELECT statements to shared locking reads when autocommit is disabled.

Writes and explicit locking reads inspect current committed state rather than the transaction’s older consistent-read snapshot. Mixing snapshot reads with current reads is valid, but the application must account for the distinction. Long-lived snapshots also retain undo history and can increase purge pressure.

InnoDB Lock Types #

InnoDB locks index records and index ranges selected by a statement:

A record lock protects an index entry.
A gap lock protects the interval before an index entry against insertion without locking the entry itself.
A next-key lock combines a record lock with its preceding gap.
Intention locks declare at table level that a transaction holds or plans row-level shared or exclusive locks; they make table-lock compatibility checks efficient.

Under REPEATABLE READ, locking range scans use next-key locks to prevent phantom inserts. A unique-index equality search that finds one row can use only a record lock. READ COMMITTED disables gap locking for most searches, except where MySQL requires it for foreign-key and duplicate-key checks.

The access path determines which entries InnoDB examines and can lock. A missing or nonselective index therefore broadens both work and contention. Review the plan for every locking predicate.

MySQL also takes metadata locks while statements use schema objects. An open transaction that has accessed a table can make ALTER TABLE wait even when no row conflict exists. Performance Schema exposes metadata and data lock tables for diagnosing these waits.

Optimistic Compare-and-Swap #

Optimistic concurrency performs the business condition and version comparison in one write:

sql

UPDATE account
SET balance = balance - 100,
    version = version + 1
WHERE id = 42
  AND version = 17
  AND balance >= 100;

One affected row means the state transition succeeded. Zero means that the row, version, or business predicate no longer matched. Reload current state and retry only if the operation remains valid; do not turn every conflict into an unbounded retry.

Pessimistic Locking Reads #

Use a locking read when a transaction must inspect current state before deciding what to write:

sql

START TRANSACTION;

SELECT available
FROM inventory
WHERE sku = 'A-42'
FOR UPDATE;

UPDATE inventory
SET available = available - 1
WHERE sku = 'A-42' AND available > 0;

COMMIT;

FOR SHARE takes shared locks and FOR UPDATE takes exclusive locks. NOWAIT returns instead of waiting for a conflicting lock. SKIP LOCKED omits locked rows and can support queue consumers, but it presents an intentionally inconsistent view and is unsuitable for general business queries. Keep network calls, remote services, and user input outside the locked interval.

Advisory and Table Locks #

GET_LOCK(name, timeout) acquires a server-wide named advisory lock owned by the connection. A commit does not release it; RELEASE_LOCK, connection termination, or server shutdown does. Advisory locks do not protect table rows, so every participant must honor the same naming convention:

sql

SELECT GET_LOCK('schema-migration', 10);
-- Perform bounded, connection-pinned work.
SELECT RELEASE_LOCK('schema-migration');

Use advisory locks for cooperative singleton work, not as a substitute for database constraints or row locks. A connection pool must not return a connection that still owns named locks.

LOCK TABLES establishes a separate, session-level explicit table-lock boundary and can implicitly commit. It reduces concurrency and interacts with transactional tables differently from ordinary InnoDB row locking. Reserve it for documented administrative or compatibility workflows, and always terminate the boundary with UNLOCK TABLES.

Deadlocks, Lock Waits, and Retries #

Deadlocks are expected under concurrency. InnoDB detects a wait cycle and rolls back one transaction with error 1213. A lock wait that exceeds innodb_lock_wait_timeout returns error 1205; depending on configuration, the failed statement rather than the whole transaction can be rolled back. The application should roll back explicitly and begin any retry from a clean transaction boundary.

Reduce avoidable conflicts by locking rows in a consistent order, using selective indexes, and limiting transaction duration. Diagnose recurring cases with SHOW ENGINE INNODB STATUS and Performance Schema tables including data_locks, data_lock_waits, and metadata_locks.

A retry can repeat work outside the database after the original outcome becomes uncertain. Give externally visible operations an idempotency key or durable operation identifier, bound retry attempts with backoff and jitter, and report a final conflict rather than retrying forever.