Why MyISAM’s Table Locks Can Outperform InnoDB in High‑Concurrency Scenarios

Overview

This piece uses a common MySQL question about table locks to give a systematic explanation of how MySQL table‑level locking works, why it matters, and when MyISAM can be a better choice than InnoDB.

Which storage engines use table locks?

All MySQL storage engines except InnoDB rely on table‑level locks; examples include MyISAM, MEMORY, and MERGE.

Benefits of table locks

Lower memory consumption : Row locks require memory proportional to the number of rows, while a single table lock uses far less.

Faster for large‑portion reads/writes : Only one lock is needed instead of many concurrent row locks.

Faster for GROUP BY queries : The same reason as above makes grouped aggregations quicker.

How table locks work

Table locks behave like classic read/write locks.

Write lock acquisition

If the table has no lock, acquire a write lock.

Otherwise the request joins the write‑lock queue.

Read lock acquisition

If the table has no write lock, acquire a read lock.

Otherwise the request joins the read‑lock queue.

Lock release priority

When both read and write queues contain waiting requests, the write queue is served first. This prevents write‑lock starvation caused by long‑running reads, because write locks usually release quickly.

Monitoring table‑lock activity

MySQL exposes two status variables: Table_locks_immediate: Number of times a lock was obtained without waiting. Table_locks_waited: Number of times a lock request had to wait.

They can be inspected with: show status like 'Table%'; If Table_locks_waited forms a large proportion of the total, table locking may be a performance bottleneck.

Why MyISAM can excel under heavy concurrent inserts

MyISAM stores indexes and row data separately; its primary key is a non‑clustered index. When the data file is tightly packed, new rows are always appended to the file’s end, similar to a log, which makes inserts effectively lock‑free. Concurrent inserts and selects can proceed simultaneously (selects acquire a shared read lock).

Key points:

Data file is continuous and tightly stored.

Concurrent inserts do not contend for table locks (only a simple insert‑queue mutex).

Inserts always occur at the file’s tail.

Concurrent selects can run with a shared read lock.

Impact of holes in the data file

If deletions or updates create free blocks (holes) inside the data file, the lock‑free mechanism breaks: inserts and selects must compete for the table lock until the holes are filled. Once the holes are overwritten, the lock‑free behavior resumes.

Conclusion

Even though MyISAM only supports table‑level locking, its storage layout allows high‑concurrency select and insert workloads to achieve very strong performance, especially on MySQL 5.6. Understanding the lock mechanics and monitoring the relevant status variables helps decide when MyISAM is preferable to InnoDB.