Table-Level Concurrency Control in MySQL

When several clients use a MySQL database at the same time, concurrency control decides which reads and writes can run together and which have to wait. “Table-level locking” describes one way MySQL protects table contents, but it does not describe every MySQL table. The storage engine determines much of the behavior.

InnoDB, the engine commonly used for transactions, generally locks index records when transactions change data. It also uses multiversion concurrency control (MVCC), which keeps earlier versions of rows so ordinary reads can use a consistent view of the data. MyISAM, MEMORY, and MERGE use table-level locks to protect table contents. MySQL also has separate locks for table definitions and explicit statements that request table locks. Knowing which kind of lock is involved helps explain why a query is waiting and what to inspect.

The storage engine determines how MySQL locks data

MySQL’s storage engines use different methods to protect table contents. InnoDB uses row-level locking for data changes, while MyISAM, MEMORY, and MERGE use table-level locking. With a table-level lock, a write must wait for other writes to the same table to finish. This approach uses less lock memory and can suit work that touches much of a table, but it limits concurrent writes to that table. The MySQL manual’s overview of internal locking describes these engine differences.

Table-level locking does not mean every read and write always blocks every other operation. MySQL manages read and write lock queues, and gives updates priority over reads. That policy prevents a steady stream of readers from indefinitely delaying writers, but readers can wait when updates keep arriving. MyISAM also supports concurrent inserts in documented circumstances.

To find out whether a table-level lock is involved, first check the table’s storage engine. For engines that use MySQL’s table-lock counters, SHOW STATUS LIKE 'Table%'; displays Table_locks_immediate, for requests granted immediately, and Table_locks_waited, for requests that had to wait. Those counters do not measure all InnoDB row-lock contention.

InnoDB locks index records, not whole tables for ordinary updates

InnoDB places row locks on index records that a statement encounters. If a table has no index, InnoDB creates a hidden clustered index for record locking. As a result, the statement’s access path, the indexes and records it reads, affects how many records it locks. A query that scans many index records can lock more than the rows its author expects to change. The InnoDB locking documentation explains record locks and the table-level intention locks InnoDB uses alongside them.

An intention lock is a table-level marker that tells MySQL a transaction plans to lock records in that table. For example, an intention-exclusive lock indicates that the transaction intends to take exclusive locks on records. These table-level markers help MySQL coordinate row locks with locks requested at a coarser level. They do not mean that InnoDB has locked the whole table for an ordinary UPDATE.

Consider two reads of an order:

-- A plain consistent read
SELECT * FROM orders WHERE id = 100;

-- A locking read
SELECT * FROM orders WHERE id = 100 FOR UPDATE;

InnoDB normally runs the first statement as a nonlocking consistent read. The second statement requests a lock so the transaction can safely work with the selected record. If id is a unique index, a search that checks whether id equals one value can lock the matching index record without locking the gap around it. A search that checks a span of values can involve locks on index ranges, depending on the indexes and isolation level.

MVCC lets ordinary reads proceed alongside writes

InnoDB’s MVCC lets a plain SELECT read a consistent version of data without taking data locks that block writers. When another transaction changes a row, InnoDB can use an earlier row version to satisfy a reader’s snapshot. The reader therefore does not have to wait for every write to finish.

At the default REPEATABLE READ isolation level, consistent reads in a transaction use the same snapshot, or view of the data, established by the transaction’s first consistent read. At READ COMMITTED, each consistent read gets a fresh snapshot. A transaction being open does not, by itself, mean that every read locks rows or sees the latest commit. The documentation on consistent reads describes these snapshot rules.

Locking reads such as SELECT ... FOR UPDATE and SELECT ... FOR SHARE behave differently from ordinary reads. They request locks on the records needed by the search. UPDATE and DELETE also take locks as they run. When a statement blocks, check whether the code issued a locking read or a write rather than assuming that any SELECT caused the wait.

Range locks depend on indexes and isolation level

Under InnoDB’s default REPEATABLE READ, a range search can use next-key locks to prevent phantom rows, which are newly inserted rows that would appear in a later result for the same search. A next-key lock covers an index record and the gap immediately before it; a gap lock prevents another transaction from inserting into that space in index order. This means an insert can wait even though the new row does not yet exist.

For example, a locking range search such as SELECT ... WHERE id BETWEEN 100 AND 200 FOR UPDATE may lock records beyond those that currently match its condition. InnoDB’s lock footprint depends on the index it scans and the transaction’s isolation level. Inspect the query plan and lock information before diagnosing the statement from its WHERE clause alone.

At READ COMMITTED, InnoDB disables gap locking for searches and index scans, except when it checks foreign keys or duplicate keys. This permits phantom rows, while also allowing some statements to release locks on rows that do not match their conditions earlier. Changing isolation levels therefore changes the behavior the application relies on; it is a correctness decision as well as a way to alter contention. See MySQL’s transaction isolation details.

Metadata locks can delay schema changes

MySQL also locks database-object definitions, including table definitions. A transaction that has used a table can retain a metadata lock until it ends. A statement such as ALTER TABLE or DROP TABLE then waits until the conflicting metadata lock is released. This is distinct from an InnoDB lock on table data. The behavior is described in the MySQL 8.0 metadata locking documentation.

For MySQL 8.4, the Performance Schema metadata_locks table shows granted and pending metadata-lock requests. Use it when a schema change is waiting. For a suspected InnoDB data-lock wait, inspect the data_locks and data_lock_waits information instead, as described in the InnoDB lock and wait documentation. Finding the statement that is waiting and the lock or other resource it needs is more useful than calling every delay a “table lock.”

Use explicit table locks only when coarse serialization fits

The LOCK TABLES statement requests locks on whole tables. These locks can serialize work, but they are easy to combine incorrectly with InnoDB transactions: in MySQL 8.4, LOCK TABLES implicitly commits an active transaction before it tries to acquire the locks. The manual documents a specific pattern for using table locks with transactional tables, including disabling autocommit and explicitly committing before releasing the locks. Check the LOCK TABLES rules before using the statement in transactional code.

Most InnoDB workflows should first use transactions and record-level locking. Explicit table locks make sense only when the work requires serialization at the table level and the resulting waits are acceptable.

Reduce waits and recover from deadlocks

A deadlock occurs when transactions wait on locks held by one another. InnoDB detects deadlocks by default and rolls back one transaction to break the cycle. The application must handle that rollback and retry the entire transaction when appropriate. MySQL recommends keeping transactions short, accessing records in a consistent order. It also recommends indexing columns used in locking and update conditions. Its deadlock guidance covers these practices.

Start a lock investigation by confirming the storage engine and isolation level. Then identify whether the statement is a plain read, a locking read, or a write. Inspect the indexes and query plan, then use the lock instrumentation for the suspected layer. If InnoDB repeatedly locks a wider range than expected, improve the access path or reconsider the transaction’s isolation requirements before reaching for explicit table locks.

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