Understanding MySQL Locks: Table, Row, and Gap Locks in InnoDB and MyISAM

Lock Classification

Locks can be categorized by the granularity of data they affect:

Table lock: locks the entire table during an operation.

Row lock: locks only the rows involved in the operation.

They can also be classified by operation type:

Read lock (shared): multiple reads can occur simultaneously without interference.

Write lock (exclusive): blocks other reads and writes until the operation completes.

MySQL Locks Overview

MySQL lock characteristics are summarized as follows:

Table‑level lock (mainly MyISAM): low overhead, fast acquisition, no deadlocks, but large lock granularity leads to high contention and low concurrency.

Row‑level lock (mainly InnoDB): slightly higher overhead, slower acquisition, can deadlock, but fine granularity yields low contention and high concurrency.

Page lock: lock cost and granularity lie between table and row locks; deadlocks may occur and concurrency is moderate.

MyISAM Table Locks

MyISAM automatically acquires a read lock before a SELECT and a write lock before UPDATE/DELETE/INSERT, requiring no explicit LOCK TABLE commands.

lock table table_name read; lock table table_name write; unlock tables;

Read locks do not block other reads but block writes; write locks block both reads and writes, and MyISAM prioritizes writes, making it unsuitable for write‑heavy workloads.

InnoDB Locks

Two Types of Row Locks

Shared lock (S) : also called a read lock; multiple transactions can read the same row concurrently but cannot modify it.

Exclusive lock (X) : also called a write lock; once a transaction holds an X lock on a row, no other transaction can obtain any lock (S or X) on that row, but the holder can read and modify the row.

Locking statements:

select * from table_name where … LOCK IN SHARE MODE; select * from table_name where … FOR UPDATE;

Tips: UPDATE, DELETE, and INSERT automatically acquire exclusive locks; plain SELECT does not acquire any lock.

Row‑to‑Table Lock Upgrade

If an UPDATE lacks an index or the index is ineffective (e.g., implicit conversion), row locks may be upgraded to table locks.

-- name is varchar(16) update test_innodb_lock set sex='2' where name = 400;

Gap Locks (Next‑Key Locks)

When a range condition is used, InnoDB locks existing rows that satisfy the condition and also locks the gaps between rows that do not exist, preventing phantom reads.

update test_innodb_lock set sex = '0' where id < 4;

A concurrent transaction attempting to insert a new row will be blocked:

insert into test_innodb_lock values(2, '200', '1');

Monitoring Row Lock Contention

Useful status variables:

Innodb_row_lock_current_waits – number of locks currently waiting.

Innodb_row_lock_time – total time spent waiting for locks since server start.

Innodb_row_lock_time_avg – average wait time per lock.

Innodb_row_lock_time_max – longest single wait time.

Innodb_row_lock_waits – total number of lock waits since server start.

show status like 'innodb_row_lock%';

Summary

InnoDB’s row‑level locking provides higher concurrency than MyISAM’s table‑level locking, especially under high load, but misuse (e.g., missing indexes, large transactions) can degrade performance and even make InnoDB slower than MyISAM.

Optimization Recommendations

Ensure all queries can use indexes to avoid row‑to‑table lock upgrades.

Design indexes to narrow lock scope.

Minimize index range conditions to reduce gap locks.

Keep transactions short to limit lock duration and resource usage.

Use the lowest isolation level that satisfies business requirements.