63
What is locking in MySQL and what are the types of locks?
Level: Advanced — Locking is how MySQL enforces isolation and prevents data corruption under concurrency.
Locking is the mechanism MySQL uses to control concurrent access to data, ensuring that multiple transactions don't interfere with each other and corrupt data. A lock temporarily restricts what other transactions can do to a resource.
Locks by Granularity
- Table-Level Lock: Locks the entire table. Simple but low concurrency (used by MyISAM).
- Row-Level Lock: Locks only specific rows. High concurrency (used by InnoDB).
- Gap Lock: Locks the "gap" between index records to prevent phantom inserts (InnoDB).
- Next-Key Lock: A combination of a row lock + gap lock — InnoDB's default for REPEATABLE READ.
Locks by Mode
- Shared Lock (S): A "read lock." Many transactions can hold it simultaneously to read, but none can write.
- Exclusive Lock (X): A "write lock." Only one transaction can hold it; it blocks all other reads and writes on that resource.
Key point: Multiple shared locks can coexist (many readers), but an exclusive lock is incompatible with any other lock (one writer, no readers). This is how InnoDB balances concurrency and safety.
Lock Compatibility Matrix
| Requesting → |
Shared (S) |
Exclusive (X) |
| Shared (S) held |
Compatible |
Blocked |
| Exclusive (X) held |
Blocked |
Blocked |
Quick Example
START TRANSACTION;
-- Acquire a SHARED (read) lock: others can read but not modify
SELECT * FROM accounts WHERE acc_id = 1 LOCK IN SHARE MODE;
-- Acquire an EXCLUSIVE (write) lock: others cannot read or write these rows
SELECT * FROM accounts WHERE acc_id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 500 WHERE acc_id = 1;
COMMIT; -- releases the locks
-- Explicit table-level locks
LOCK TABLES accounts WRITE;
-- ... operations ...
UNLOCK TABLES;
Note: In MySQL 8.0+, LOCK IN SHARE MODE can also be written as FOR SHARE, and both support options like NOWAIT and SKIP LOCKED for non-blocking behaviour.
Interviewer tip: The one-liner they want — "Locking controls concurrent access. By granularity there are table, row, gap, and next-key locks; by mode there are shared (read) and exclusive (write) locks. InnoDB uses row-level locking for high concurrency."