Locks are essential for managing concurrent access to shared resources in databases. In MySQL, locks ensure data consistency and integrity when multiple transactions operate simultaneously. Based on granularity, MySQL supports three primary lock types: global, table-level, and row-level locks.
Global Locks
A global lock places the entire database instance in read-only mode. This is typically used during logical backups to capture a consistent snapshot of all tables. Without such a lock, interleaved DML operations during backup can result in inconsistent data across related tables.
To acquire a global lock:
FLUSH TABLES WITH READ LOCK;
After backup completes, release the lock:
UNLOCK TABLES;
However, global locking halts all write operations, which severely impacts availability. For InnoDB, a better alternative is using --single-transaction with mysqldump, which leverages MVCC to produce a consistent backup without blocking writes:
mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql
Table-Level Locks
Table-level locks operate on entire tables and include three subtypes: explicit table locks, metadata locks (MDL), and intention locks.
Explicit Table Locks
These are manually applied using:
LOCK TABLES table_name READ; -- Shared read lock
LOCK TABLES table_name WRITE; -- Exclusive write lock
UNLOCK TABLES;
- READ lock: Allows concurrent reads but blocks writes.
- WRITE lock: Blocks both reads and writes from other sessions.
Metadata Locks (MDL)
Introduced in MySQL 5.5, MDLs are automatically managed to protect table structure changes. Any DML (SELECT, INSERT, UPDATE, DELETE) implicitly acquires a shared MDL, while DDL (ALTER, DROP) requires an exclusive MDL.
If a long-running transaction holds a shared MDL, subsequent DDL statements will block. Since MDL requests queue with write priority, this can stall all future operations on the table.
To inspect active MDLs:
SELECT object_type, object_schema, object_name, lock_type, lock_duration
FROM performance_schema.metadata_locks;
Intention Locks
InnoDB uses intention locks to signal that a transaction intends to acquire row-level locks on a table. These are table-level placeholders that avoid full table scans when checking for conflicts.
- Intention Shared (IS): Compatible with table READ locks, conflicts with WRITE locks.
- Intention Exclusive (IX): Conflicts with both table READ and WRITE locks.
Intention locks are acquired automatically during DML and released upon transaction commit.
Row-Level Locks
Row-level locks offer the finest granularity, maximizing concurency. InnoDB supports three kinds:
- Record Lock: Locks a specific index record.
- Gap Lock: Locks the gap between index records to prevent phantom inserts.
- Next-Key Lock: Combines record and gap locks; used by default in REPEATABLE READ isolation.
InnoDB implements two fundamental row lock modes:
- Shared (S): Permits reading; blocks X locks.
- Exclusive (X): Permits modification; blocks S and X locks.
Important behaviors:
- Row locks only apply when queries use indexed columns. Non-indexed conditions cause full-table locking.
- For unique index equality lookups, Next-Key Locks often degrade to Record Locks.
- Gap locks prevent phantom reads by disallowing inserts into locked ranges.
Example – Record Lock:
Session 1:
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- Holds X lock on row id=1
COMMIT;
Session 2:
BEGIN;
UPDATE orders SET amount = amount - 50 WHERE id = 1; -- Waits for Session 1
Example – Gap Lock:
Session 1:
BEGIN;
SELECT * FROM orders WHERE id > 1 AND id < 10 FOR UPDATE;
-- Locks gaps, e.g., prevents inserting id=5
COMMIT;
Session 2:
INSERT INTO orders (id, amount) VALUES (5, 500); -- Blocked until Session 1 ends
Next-Key Lock Example:
On a non-unique index, a query like WHERE id = 5 may lock the range (3,5], preventing both modification of id=5 and insertion of values in (3,5).
By default, InnoDB uses Next-Key Locking under REPEATABLE READ to eliminate phantoms. Understanding these mechanisms helps optimize concurrency and avoid deadlocks or unexpected blocking.