Understanding MySQL Locks: From Table Locks to InnoDB Row Locks

This article records learning the MyISAM and InnoDB storage engines.

Why Learn Lock Mechanisms

Locks coordinate concurrent access to a shared resource; because data is shared among many users, databases must ensure consistency and effectiveness of concurrent access, making lock mechanisms a key topic when studying MySQL.

MySQL Lock Overview

Different storage engines support different lock mechanisms. MyISAM and MEMORY use table‑level locking; BDB uses page‑level (and also table‑level) locking; InnoDB supports both row‑level and table‑level locking, defaulting to row‑level.

Key characteristics of lock types include:

Table‑level lock: low overhead, fast acquisition, no deadlocks, large granularity, high contention, low concurrency.

Row‑level lock: high overhead, slower acquisition, possible deadlocks, fine granularity, low contention, high concurrency.

Page‑level lock: overhead and speed between table and row locks, possible deadlocks, medium granularity and concurrency.

Table‑level locks suit read‑heavy, few indexed updates (e.g., typical web apps); row‑level locks suit heavy concurrent updates on indexed rows (e.g., OLTP systems).

MyISAM Storage Engine

MyISAM only supports table‑level locks.

MySQL Table‑Level Locks

Querying Table Lock Contention

<code>mysql> show status like 'table%';</code>

If

Table_locks_waited

is high, serious table‑level lock contention exists.

Two Modes of Table Locks

Table Read Lock

Table Write Lock

Read lock allows other reads but blocks writes; write lock blocks both reads and writes.

Testing MyISAM Write Lock

<code>mysql> desc user;</code>
<code>+---------+-------------+------+-----+---------+----------------+</code>
<code>| Field   | Type        | Null | Key | Default | Extra          |</code>
<code>+---------+-------------+------+-----+---------+----------------+</code>
<code>| id      | int(11)     | NO   | PRI | NULL    | auto_increment |</code>
<code>| name    | varchar(20) | YES  |     | NULL    |                |</code>
<code>| age     | int(3)      | YES  |     | NULL    |                |</code>
<code>| address | varchar(60) | YES  |     | NULL    |                |</code>
<code>+---------+-------------+------+-----+---------+----------------+</code>

<code>Session A: lock table user write; insert ...</code>

<code>Session B: select * from userG   // blocked</code>

After unlocking, Session B can read the inserted row.

Testing MyISAM Read Lock

<code>Session A: lock table user read;</code>

<code>Session B: select * from user where id=1;   // not blocked</code>

<code>Session B: select * from order;   // error if order not locked</code>

<code>Session A: update user set name='wahaha' where id=1;   // error – read lock prevents update</code>

Read lock blocks writes but not other reads.

MyISAM Concurrent Insert

The

concurrent_insert

variable controls concurrent inserts:

0 – no concurrent insert.

1 – allows concurrent insert at the end when the table has no gaps (default).

2 – always allows concurrent insert at the end.

MyISAM Lock Scheduling

Write locks have higher priority than read locks. To give reads higher priority, you can:

Start MySQL with

low-priority-updates

.

Execute

SET LOW_PRIORITY_UPDATES=1

for the session.

Use the

LOW_PRIORITY

attribute on

INSERT

,

UPDATE

, or

DELETE

statements.

InnoDB

InnoDB differs from MyISAM in two major ways: it supports transactions and uses row‑level locking.

Transaction Concepts (ACID)

A (Atomicity): all changes are all‑or‑nothing.

C (Consistency): data remains consistent before and after a transaction.

I (Isolation): concurrent transactions do not interfere with each other.

D (Durability): committed changes survive crashes.

Problems Caused by Concurrent Transactions

Lost update.

Dirty read.

Non‑repeatable read.

Phantom read.

Transaction Isolation Levels

Four levels trade off consistency vs. concurrency:

Read Uncommitted – allows dirty reads.

Read Committed – prevents dirty reads, allows non‑repeatable reads and phantoms.

Repeatable Read – prevents dirty and non‑repeatable reads, but allows phantoms (default in InnoDB).

Serializable – highest isolation, prevents all three anomalies.

Checking InnoDB Row‑Lock Contention

<code>mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+</code>

High values of

Innodb_row_lock_waits

or

Innodb_row_lock_time_avg

indicate serious lock contention.

InnoDB Row‑Lock Modes and Acquisition

Two basic lock types:

Shared lock (S) – multiple transactions can read.

Exclusive lock (X) – only one transaction can write.

Intention locks (table‑level) are automatically added:

Intention Shared (IS) – before acquiring a row‑level shared lock.

Intention Exclusive (IX) – before acquiring a row‑level exclusive lock.

To request locks explicitly:

Shared lock:

SELECT * FROM table WHERE ... LOCK IN SHARE MODE;

Exclusive lock:

SELECT * FROM table WHERE ... FOR UPDATE;

How InnoDB Implements Row Locks

InnoDB locks index records, not the physical rows. Therefore, a row is locked only when the query uses an index. Without an index, InnoDB falls back to a table lock.

If a query does not use an index, InnoDB uses a table lock.

Locks are on index entries; the same index key can cause lock conflicts even for different rows.

Multiple indexes allow different transactions to lock different rows simultaneously.

MySQL decides whether to use an index based on the optimizer's cost estimate; a full table scan may trigger a table lock.

Use

EXPLAIN

to verify index usage.

Next‑Key (Gap) Locks

When a range condition is used, InnoDB locks the matching index records and also the gaps between them. This prevents other sessions from inserting rows into the locked range, which can cause lock waiting.

Deadlocks

MyISAM is deadlock‑free because it acquires all required locks at once. InnoDB acquires locks incrementally, so deadlocks can occur.

Typical deadlock scenario: two sessions lock different rows in different tables and then each tries to lock the row the other session holds, forming a circular wait.

InnoDB detects most deadlocks and rolls back one transaction, but external or table locks may require adjusting

innodb_lock_wait_timeout

to avoid long waits.

How to Avoid Deadlocks

Access tables in a consistent order across all applications.

Sort data before batch processing so each thread follows the same order.

Request the appropriate lock level directly (e.g., exclusive lock) instead of upgrading from shared to exclusive.

Use

READ COMMITTED

isolation when range‑based

SELECT ... FOR UPDATE

may cause phantom inserts.

When inserting, handle duplicate‑key errors gracefully and roll back to release any locks.

Summary

This learning article deepens understanding of MySQL lock mechanisms, helping developers write SQL and troubleshoot issues while avoiding deadlocks and improving performance.