An UPDATE in MySQL 8.0 acquires three distinct locks—a server‑level metadata lock, an InnoDB intention exclusive lock (IX), and a row‑level exclusive lock—so understanding the three‑layer lock architecture (metadata, intention, row) helps both interview preparation and troubleshooting.
This article examines MySQL's comprehensive lock taxonomy, starting from common row and table locks, then delving into lesser‑known mechanisms such as global locks, intention locks, auto‑increment locks, and Latch, while explaining their commands, use‑cases, and performance implications for developers and DBAs.
This article explains why MySQL acquires row locks that persist until a transaction commits, using analogies with multithreading, detailed transaction scenarios, the role of intention locks, and how isolation levels affect lock behavior, all illustrated with code examples and diagrams.
