How InnoDB Manages Concurrent Transactions

When several MySQL clients read or change the same data, InnoDB lets clients read and change data concurrently, and coordinates conflicting changes. In MySQL 8.4, its default storage engine keeps older row versions so ordinary reads can use a consistent snapshot, a view of the data that stays consistent while other transactions make changes. InnoDB also uses locks to coordinate writes and reads that need protection.

A transaction is a group of database operations that MySQL commits together or rolls back together. A plain SELECT usually reads a snapshot without blocking writers, while an UPDATE or locking read can make another transaction wait. Knowing which behavior applies helps you choose transaction boundaries, indexes, and isolation levels that fit your workload. The details below describe InnoDB in MySQL 8.4.

Ordinary reads use snapshots

InnoDB uses multi-version concurrency control, or MVCC, a method that keeps older versions of rows when transactions change them. An ordinary SELECT can read the version that matches its snapshot instead of waiting for a writer to finish. This lets readers and writers work at the same time. InnoDB’s transaction model combines these row versions with locks for operations that need coordination.

At InnoDB’s default isolation level, REPEATABLE READ, a transaction’s ordinary reads share the snapshot created by its first ordinary read. At READ COMMITTED, each ordinary read gets a fresh snapshot. For example, if one transaction reads an account balance and another transaction updates and commits it, the first transaction’s next ordinary read sees the earlier snapshot under REPEATABLE READ, but sees a fresh view under READ COMMITTED.

A transaction still sees its own earlier changes. Also, a locking read does not behave like an ordinary snapshot read. Mixing reads, writes, and locking reads can therefore expose different views inside one transaction. The consistent-read documentation describes how InnoDB chooses row versions for ordinary reads.

InnoDB keeps old row versions while active transactions still need them. A long-running transaction can delay cleanup of those versions, increasing the work required to reconstruct snapshots. Keep transactions open only as long as the related work requires.

Isolation levels change what reads see

An isolation level sets rules for how a transaction sees changes made by other transactions and, in some cases, what locks InnoDB takes. InnoDB supports four levels. The default, REPEATABLE READ, gives ordinary reads a stable snapshot within a transaction; it also allows locking reads and writes to lock ranges of rows. A phantom row is a newly inserted row that would appear if a transaction repeated a query for a range of values. A gap lock protects the space between index records from inserts.

In REPEATABLE READ, next-key locks can prevent inserts into a searched range during locking operations. In READ COMMITTED, InnoDB disables gap locking for searches and index scans except for foreign-key and duplicate-key checks. That reduces some blocking while allowing phantom rows. The isolation-level documentation describes these tradeoffs; changing isolation levels does not guarantee better performance, so evaluate the effect against the queries and consistency rules your workload needs.

Writes and locking reads coordinate through locks

When a transaction changes a row, InnoDB takes locks to keep conflicting transactions from changing that data at the same time. A shared lock permits compatible readers; an exclusive lock prevents other transactions from immediately taking conflicting locks on the same record. InnoDB also uses intention locks at the table level to indicate that a transaction plans to lock rows within that table.

A plain SELECT usually does not lock rows against updates. When code must read a value and then act on it without another transaction changing it in between, use a locking read inside a transaction. SELECT ... FOR SHARE lets other sessions read the selected rows but blocks conflicting changes. SELECT ... FOR UPDATE takes locks comparable to those used for an update. For example, a transaction that checks an account balance before changing it can lock the relevant row during that decision, then commit promptly to release the lock. The locking-read rules explain the available forms.

InnoDB places row locks on index records, the entries it uses to find rows. The index InnoDB chooses determines which records a statement scans and locks. For a locking read, update, or delete, InnoDB generally locks the index records it scans, including records that do not satisfy the final WHERE condition. If the query has no useful index and scans the whole table, it can lock many records and block inserts. A lookup that uses an index to find only a small number of records narrows the scan and usually limits the locks.

Range searches need particular care. Under REPEATABLE READ, InnoDB can use a next-key lock, which combines a lock on an index record with a lock on the gap before it. That protects the range from inserts, including rows that do not exist yet. Check the query plan and indexes for frequently used UPDATE ... WHERE and SELECT ... FOR UPDATE statements; the lock-setting rules show why the scanned index records matter as much as the rows returned.

Transaction boundaries determine when locks end

MySQL enables autocommit by default, so each successful statement commits as its own transaction unless the client explicitly starts a multi-statement transaction. To make related statements succeed or fail together, begin a transaction with START TRANSACTION, then finish it with COMMIT or ROLLBACK. COMMIT makes changes visible to other sessions and releases the transaction’s locks. ROLLBACK cancels its changes and also releases the locks. See the autocommit and transaction rules.

Keep write transactions short. A transaction that waits on a user action or performs unrelated work before committing holds its locks longer, increasing the time other transactions may have to wait. Short transactions also reduce the time InnoDB must preserve older row versions for active snapshots.

Deadlocks require a full transaction retry

A deadlock happens when transactions wait for locks held by one another. For example, one transaction might lock row A and then request row B, while another locks row B and then requests row A. InnoDB detects deadlocks by default, chooses a transaction as the victim, and rolls it back.

After a deadlock, code that runs transactions should treat the failure as recoverable and restart the entire transaction from its first statement. Keep transactions small and have related code access rows in a consistent order to reduce deadlock opportunities. Indexes that narrow the records scanned also help reduce unnecessary locking. Changing isolation level by itself does not eliminate deadlocks; see the deadlock guidance.

A lock wait timeout has different default behavior: InnoDB rolls back the waiting statement, not necessarily the whole transaction. The application must handle the resulting transaction state deliberately and avoid blindly repeating only part of a multi-step operation. The error-handling documentation covers these failure cases.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com