Row-Level vs Table-Level Locking
IntermediateInnoDB row-level locking (shared S, exclusive X, intent locks) allows high concurrency; table-level locks (LOCK TABLES) are used for bulk operations and DDL on MyISAM.
Overview
InnoDB uses row-level locking, which allows multiple transactions to concurrently read and write different rows of the same table. Locks are acquired implicitly by DML statements and explicitly by SELECT ... FOR UPDATE / SELECT ... FOR SHARE. Lock types include Shared (S) locks (multiple readers allowed), Exclusive (X) locks (single writer), and Intent locks (IS/IX) placed on the table to signal what row locks are held below. Gap locks and Next-Key locks prevent phantom reads under REPEATABLE READ by locking index gaps between rows, not just existing rows. Understanding lock granularity is critical for debugging deadlocks and contention in high-throughput applications.
InnoDB row-level lock types: S, X, and Intent
Shared locks (S) allow concurrent reads but block writes. Exclusive locks (X) block both reads and writes from other transactions. Intent locks (IS, IX) are table-level markers that signal row-level locks exist below — they prevent full table locks from being acquired while row locks are held. InnoDB sets intent locks automatically; you do not set them manually.
-- Shared lock: allows other readers, blocks writers
SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- Another transaction CAN also SELECT ... FOR SHARE (concurrent reads)
-- Another transaction CANNOT SELECT ... FOR UPDATE (blocked)
-- Exclusive lock: blocks all other lock acquisition
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- No other transaction can read or write row id=1 until commit/rollback
-- Intent locks (automatic) prevent concurrent LOCK TABLES
-- Transaction A holds X lock on row 1 → IX on orders table
-- Another session: LOCK TABLE orders WRITE → blocked by IX on orders
-- Check current locks (MySQL 8+)
SELECT * FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'orders';
-- LOCK_TYPE: TABLE (intent), ROW (record/gap/next-key)Gap locks and Next-Key locks (phantom prevention)
Under REPEATABLE READ, InnoDB uses Next-Key locks (record lock + gap lock on the preceding gap) to prevent phantom reads. Gap locks lock the space between index values, preventing inserts into that range. This is key to MySQL's REPEATABLE READ isolation — it prevents phantoms without SERIALIZABLE. Gap locks can cause surprising lock waits when range predicates are used.
-- Gap lock example: prevents phantom rows
-- Table: orders with index on status, rows with status 1,3,5
-- Transaction A: locks record 3 AND gap (1,3) AND gap (3,5)
SELECT * FROM orders WHERE status = 3 FOR UPDATE;
-- Transaction B: cannot insert status=2 (blocked by gap lock (1,3))
INSERT INTO orders (status) VALUES (2); -- BLOCKED
-- Transaction B: cannot insert status=4 (blocked by gap lock (3,5))
INSERT INTO orders (status) VALUES (4); -- BLOCKED
-- Transaction B: CAN insert status=0 or status=6 (outside locked range)
-- Disable gap locks (READ COMMITTED isolation — less protection)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Now only record locks — no gap locks — phantom reads possible
-- Inspect gap vs record locks
SELECT LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks;
-- LOCK_MODE: S,GAP | X,GAP | S,REC_NOT_GAP | X | NEXT-KEYTable-level locks: LOCK TABLES and implicit DDL locks
LOCK TABLES acquires an explicit table-level lock (READ or WRITE). It is rarely needed with InnoDB — use only for bulk operations that must prevent any concurrent access. DDL operations (ALTER TABLE, TRUNCATE) acquire implicit metadata locks (MDL) that block DML during schema changes. Long-running transactions hold MDLs and can block DDL, causing table-wide lock queues.
-- Explicit table lock (rarely needed with InnoDB)
LOCK TABLE orders WRITE; -- exclusive: blocks all reads and writes
-- ... perform bulk operation ...
UNLOCK TABLES;
LOCK TABLE orders READ; -- shared: allows reads, blocks writes
-- ... export/backup ...
UNLOCK TABLES;
-- Metadata lock (MDL) contention example
-- Session A: long-running transaction (holds MDL shared)
BEGIN;
SELECT COUNT(*) FROM orders; -- holds MDL shared on orders table
-- Session B: ALTER TABLE blocks waiting for MDL exclusive
ALTER TABLE orders ADD COLUMN notes TEXT; -- WAITING
-- Session C: any DML also waits (MDL queue behind DDL)
UPDATE orders SET status=2 WHERE id=1; -- WAITING
-- Diagnose MDL contention
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop' AND OBJECT_NAME = 'orders';Key Points to Remember
- 1InnoDB row-level locking (S/X) maximises concurrency — only conflicting rows are locked, not the whole table
- 2Intent locks (IS/IX) are automatic table markers that prevent LOCK TABLES from racing with row locks
- 3Next-Key locks (record + gap) prevent phantom reads under REPEATABLE READ by locking index ranges
- 4Gap locks can block inserts in ranges even when no matching record exists — a common source of unexpected deadlocks
- 5READ COMMITTED removes gap locks (only record locks) — reduces deadlocks but allows phantom reads
- 6Long-running transactions hold metadata locks (MDL) and can block ALTER TABLE, causing table-wide write queues
Interview Questions
Sign in to ask AriaWhat is the difference between a shared lock and an exclusive lock in InnoDB?
What is a gap lock and why does it exist under REPEATABLE READ isolation?
A SELECT ... FOR UPDATE on a range is blocking unrelated inserts. What type of lock is causing this?
How do metadata locks (MDL) interact with long-running transactions and DDL operations?
How would you change isolation level to reduce gap lock contention and what is the trade-off?
Ask Aria about Row-Level vs Table-Level Locking
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.