Row-Level Locking vs. Table Locking in MySQL

1. Locking granularity: why it matters

The granularity of a lock determines how much of a table gets blocked for other transactions while one transaction works on it. A table lock blocks the entire table, regardless of which individual rows are actually affected. A row lock blocks only the rows actually read or changed, and lets parallel transactions work unhindered on other rows of the same table. The difference between these two granularity levels largely decides the achievable parallelism of a system under load.

InnoDB was designed from the ground up as a row-level locking engine, in clear contrast to the older MyISAM engine, which only knows table locks. This difference is one of the main reasons why InnoDB is used as the default engine almost everywhere today: in a shop system with thousands of concurrent orders, InnoDB can work in parallel on different order rows, while MyISAM would block the entire orders table for every single write operation.

Still, table locking has not disappeared completely from InnoDB. Certain operations, particularly DDL statements and explicit LOCK TABLES commands, still fall back on table-wide locking mechanisms. This article shows in detail when row-level locking applies, when table locking becomes unavoidable, and how the underlying lock modes technically work together.

For Magento operators and other applications with high write volume on central tables, such as stock or prices, this understanding is particularly relevant in practice: a carelessly placed ALTER TABLE during business hours can bring the entire checkout process to a halt for minutes through a single table lock, while a cleanly planned online DDL window performs the same schema change without noticeable downtime.

2. InnoDB row locks in detail

A row lock in InnoDB is not set on the row itself, but on the underlying index entry. This technical detail has far-reaching consequences: if no suitable index exists for a WHERE condition, InnoDB must perform a full table scan and potentially locks every single row in the table in the process, even though semantically only a few rows are actually changed. A missing index can thereby effectively turn row-level locking into table-level locking, without an explicit table lock being involved in the strict sense.

The size of this internal lock structure can be observed indirectly through SHOW ENGINE INNODB STATUS in the TRANSACTIONS section, where InnoDB reports the number of lock structs per transaction. A transaction with a conspicuously high number of lock structs, for example several tens of thousands, is a clear signal that the batch size should be reduced or the transaction split into several smaller ones, before it puts unnecessary strain on the buffer pool.

Row locks are set for UPDATE, DELETE and SELECT ... FOR UPDATE, while plain SELECT queries without an explicit locking clause run entirely without locking under the default isolation level REPEATABLE READ, through the MVCC snapshot. That means: a reading access does not, as a rule, block a writing access to the same row in InnoDB, a fundamental difference from classic table-locking systems, where even reading access can block writing operations.

A row lock is technically managed as an entry in an internal lock table in memory, which references, per transaction, which index entries it holds. This structure lets InnoDB check very quickly on every new lock request whether a competing lock already exists, without having to search the actual data pages. With very many row locks held at once, this memory usage grows noticeably, which can become its own scaling problem for extremely large batch transactions.

-- Row lock via primary key index, only this one row is locked
START TRANSACTION;
SELECT * FROM orders WHERE id = 4711 FOR UPDATE;
-- other transactions can freely update orders with a different id

-- Without a suitable index, InnoDB must scan and lock far more
SELECT * FROM orders WHERE customer_note LIKE '%urgent%' FOR UPDATE;
-- no index on customer_note: full scan, many rows examined and locked

-- Adding an index restores true row-level locking
CREATE INDEX idx_customer_note ON orders (customer_note(20));

3. Table locks: when they still occur

The most obvious case of a table lock in MySQL is the explicit command LOCK TABLES table WRITE, which locks the entire table for all other sessions until UNLOCK TABLES is called. This command historically comes from the MyISAM era and is hardly needed anymore in modern InnoDB applications, since row locks and transactions cover most of the use cases that used to require explicit table locks.

The practically more relevant case is DDL: classic ALTER TABLE operations that require a full table copy, for example changing a column type, block nearly all access to the affected table for the entire duration of the operation. TRUNCATE TABLE also works with table-wide locks, since internally it does not operate row by row like DELETE, but by recreating the data file. On large production tables, such an ALTER TABLE can take several minutes or even hours, during which the application effectively cannot access this table.

-- Explicit table lock, historically relevant, rarely needed with InnoDB
LOCK TABLES orders WRITE;
-- all other sessions are blocked from reading or writing orders
UPDATE orders SET status = 'archived' WHERE created_at < '2020-01-01';
UNLOCK TABLES;

-- Classic ALTER TABLE that requires a full table copy
-- blocks nearly all access to the table for its entire duration
ALTER TABLE orders MODIFY COLUMN customer_note TEXT;

-- TRUNCATE also uses table-level locking internally
TRUNCATE TABLE session_log;

-- COPY algorithm forces a full table lock for the whole duration
ALTER TABLE orders ALGORITHM=COPY, ADD COLUMN priority TINYINT DEFAULT 0;

4. Lock modes: S, X, IS and IX explained

InnoDB distinguishes at the row level between two fundamental lock modes: shared locks (S) and exclusive locks (X). A shared lock allows other transactions to also hold a shared lock on the same row, but prevents any exclusive lock. Several transactions can therefore access a row for reading at the same time, but none can change it while a shared lock is active. An exclusive lock, on the other hand, prevents both further shared and further exclusive locks on the same row, fully exclusive access for exactly one transaction.

At the table level, InnoDB supplements these two modes with intention locks: intention shared (IS) and intention exclusive (IX). These locks signal that a transaction intends to set shared or exclusive locks further down the hierarchy, that is, on individual rows. An IS lock at the table level is set before an S lock on a row, an IX lock accordingly before an X lock. This combination lets InnoDB check very efficiently whether a table-wide operation, such as a LOCK TABLES or ALTER TABLE, conflicts with already running row-level transactions, without having to search through every single row.

In addition to S and X, InnoDB also knows finer lock forms such as the update lock, an intermediate form that is first set while evaluating the WHERE condition of an UPDATE and only converted into a full exclusive lock once the row is actually changed. This intermediate step reduces the risk of certain deadlock patterns, where several transactions concurrently read the same rows for a potential update without immediately locking exclusively.

5. Intention locks and their interplay with row locks

Without intention locks, InnoDB would have to check every single row of a table for existing locks whenever a table-wide operation, such as a planned LOCK TABLES ... WRITE, was requested, a process that would be unacceptably slow on large tables. Instead, a single look at the table's intention locks is enough: if InnoDB finds an active IX lock from another transaction there, it knows immediately that at least one X lock is active somewhere in the table, without having to search through every single row.

This hierarchical lock system of row and table locks is the reason InnoDB can consistently combine row-level locking with occasional table locking. An ALTER TABLE that requires an exclusive table lock must first wait until all active intention locks from other transactions are resolved, meaning until all running row-level transactions on that table have finished. This exactly explains why a single long running transaction can block an otherwise fast ALTER TABLE for hours, even though no obvious lock appears at first glance.

The compatibility matrix between the four lock modes follows a simple rule: IS is compatible with IS and IX, IX is compatible with IS and IX, but neither S nor X are compatible with IX, and S is compatible only with IS and S. InnoDB checks this matrix internally for every lock request before a transaction even enters the wait state for an actual row lock, reducing conflict checking at the table level to a single, very fast table lookup operation.

6. Metadata locks during DDL operations

Besides InnoDB's internal row and table locks, another server-wide locking layer exists in MySQL: metadata locks (MDL). Every transaction that accesses a table implicitly holds a shared metadata lock on that table for as long as the transaction stays open, regardless of whether it is a reading or writing operation. DDL statements like ALTER TABLE, on the other hand, need an exclusive metadata lock, which is only granted once all shared metadata locks from other sessions have been released.

This rule explains a phenomenon frequently observed in practice: an ALTER TABLE command appears to freeze, even though the table does not seem actively used. The cause is almost always a forgotten, still open transaction in another session, which no longer holds any active row locks, but continues to hold a shared metadata lock simply by staying open. The ALTER command then waits until this transaction is committed or rolled back, and in the meantime blocks even new, otherwise independent requests against the same table, since they in turn queue up behind the waiting exclusive lock.

-- Find sessions holding metadata locks that block a pending ALTER TABLE
SELECT
  waiting_pid, waiting_query,
  blocking_pid, blocking_query
FROM sys.innodb_lock_waits;

-- Alternative: inspect metadata lock waits directly
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_NAME = 'orders' AND LOCK_STATUS = 'PENDING';

-- Kill the offending idle-in-transaction session if confirmed safe
-- KILL <blocking_pid>;

7. Online DDL vs. classic ALTER TABLE

Since MySQL 5.6, InnoDB has supported online DDL for many DDL operations, which limits the necessary exclusive metadata lock to a very short moment at the start and end of the operation. During the actual restructuring, for example when adding an index, reading and writing access to the table continues nearly undisturbed, InnoDB logs concurrent changes in a row log and applies them afterwards at the end of the operation.

Not every DDL operation supports online DDL to its full extent. Changing a column type to an incompatible type, for example from VARCHAR to INT, still requires a full table copy with a correspondingly long table lock. The command ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE can be explicitly requested and fails with a clear error message if the concrete operation does not support this method, which protects against unexpectedly long locks in production environments.

For particularly large tables, where even online DDL hits limits through the size of the row log, external tools such as pt-online-schema-change or gh-ost offer an alternative that controls, through triggers and incremental copying, even more granularly how much load the migration puts on the production system. These tools become especially relevant for tables in the hundreds-of-gigabytes range, where native online DDL can still create noticeable I/O pressure despite its short metadata lock phase.

-- Explicitly require online DDL, fail fast if not supported
ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- This type change is not compatible with online DDL and will
-- error out immediately instead of silently locking the table
-- ALTER TABLE orders MODIFY COLUMN quantity VARCHAR(10)
--   ALGORITHM=INPLACE, LOCK=NONE;  -- fails: incompatible change

8. MyISAM vs. InnoDB locking compared

A brief look at MyISAM illustrates why row-level locking became the standard in the first place. MyISAM knows only table locks: every write operation locks the entire table, every read operation sets a shared table lock that blocks any parallel write operation. In a system with high write frequency, that results in serial processing of all write operations, regardless of how many rows are actually affected.

InnoDB's row-level locking, in contrast, allows thousands of transactions to work simultaneously on different rows of the same table, as long as they do not overlap with each other. This difference is the main reason MyISAM today is practically only relevant for very specific use cases such as pure read-only tables without concurrent write load, while InnoDB is the de facto standard for nearly every transactional MySQL application.

Another, often overlooked difference concerns crash safety: MyISAM keeps no transaction logs, so a server crash in the middle of a write operation can leave tables corrupted, requiring manual repair with REPAIR TABLE. InnoDB, on the other hand, uses its redo log and crash recovery mechanism to ensure the table is automatically restored to a consistent state after a restart, without manual intervention.

9. Diagnosing lock conflicts in practice

When an application reports unexpected wait times, the first diagnostic step is to determine whether it is a row-level or a table-level conflict. SHOW ENGINE INNODB STATUS shows active locks and waiting transactions in the TRANSACTIONS section. performance_schema.data_locks provides a structured, queryable view of all currently held locks, including lock mode, affected index and locked data range, which makes manually searching the text output of SHOW ENGINE INNODB STATUS unnecessary.

A typical symptom of missing row-level locking caused by a missing index is a transaction locking far more rows than the application logic would suggest. The lock_data column in performance_schema.data_locks then shows an unusually high number of locked rows for what should be a very specific WHERE condition, a clear signal that an index is missing and InnoDB is instead performing a broad table scan with correspondingly many locks.

For metadata locks, performance_schema.metadata_locks provides the matching diagnostic table, complemented by the more convenient view sys.schema_table_lock_waits, which directly compares blocking and blocked sessions, including the respective thread ID and the waiting query. This combination of three diagnostic sources, row locks through data_locks, metadata locks through metadata_locks, and the classic SHOW ENGINE INNODB STATUS, covers virtually every form of lock conflict in InnoDB.

10. Summary

InnoDB is fundamentally designed as a row-level locking engine, meaning by default it only locks the rows actually affected, through the associated index entry. Table locking still remains relevant, particularly for DDL operations without online DDL support, for explicit LOCK TABLES, and implicitly through metadata locks, which can block even short, otherwise uncritical ALTER TABLE commands if another session has left a transaction open. The lock modes S, X, IS and IX form an efficient hierarchical system that lets InnoDB coordinate row and table locks without expensive full table scans.

Anyone who really wants to make use of row-level locking must ensure that every WHERE condition is covered by a suitable index, run DDL operations with ALGORITHM=INPLACE, LOCK=NONE wherever possible, and consistently avoid transactions left open, which would otherwise block even harmless schema changes through metadata locks.

11. FAQ: Row-Level Locking vs. Table Locking