Understanding InnoDB Record Locking When Inserting Duplicate Primary Keys

This article examines how InnoDB acquires shared record locks during a duplicate‑primary‑key INSERT, explains the underlying lock‑checking mechanism, demonstrates lock behavior with concurrent DELETE and INSERT transactions, and provides SQL examples and performance_schema queries to illustrate the process.

InnoDBmysqlrecord lock

0 likes · 8 min read

Understanding InnoDB Record Locking When Inserting Duplicate Primary Keys

New Oriental Technology

Jun 13, 2024 · Databases

Understanding InnoDB Row-Level Locks and Locking Rules in MySQL

This article explains the types of InnoDB row‑level locks—record lock, gap lock, and next‑key lock—describes how MySQL determines lock ranges based on indexing, outlines the two fundamental locking principles with associated optimizations, and provides concrete examples for primary‑key and ordinary indexes.

Gap LockInnoDBNext-key Lock

0 likes · 11 min read

Understanding InnoDB Row-Level Locks and Locking Rules in MySQL