Tagged articles
next-key-lock
18 articles · Page 1 of 1
This comprehensive guide explains InnoDB's locking mechanisms including record, gap, next-key, and insert intention locks, derives locking rules with concrete examples, walks through UPDATE locking flow and lock0lock.cc source code, and analyzes three real-world deadlock cases with solutions.
InnoDBMySQLdeadlock
0 likes · 51 min read

This article explains how MySQL's Repeatable Read isolation level prevents phantom reads by combining MVCC snapshot reads for consistent views and Next-Key Locks (record + gap locks) to block inserts during current reads like UPDATE, detailing the ReadView mechanism, version chains, and a concrete phantom read scenario.
Isolation LevelMVCCMySQL
0 likes · 8 min read

The article walks through a real‑world MySQL deadlock incident, shows how to extract the LATEST DETECTED DEADLOCK section from SHOW ENGINE INNODB STATUS, explains gap and next‑key locks, and presents a three‑step method plus practical fixes to eliminate the deadlock.
InnoDBMySQLSHOW ENGINE INNODB STATUS
0 likes · 10 min read

This article walks through a concrete MySQL InnoDB deadlock scenario caused by Gap Locks, showing the exact SQL statements, lock states at each step, log excerpts, and practical tips to avoid similar deadlocks in repeatable‑read transactions.
InnoDBMySQLRepeatable Read
0 likes · 11 min read

This article explains InnoDB's lock mechanisms—including table, intention, row, GAP, next‑key, and auto‑increment locks—shows how to inspect them via performance_schema tables, demonstrates lock behavior under different isolation levels with concrete SQL examples, and clarifies lock compatibility rules.
InnoDBLocksMySQL
0 likes · 20 min read

This article explains MySQL InnoDB's Next-Key Lock mechanism, how it combines row and gap locks to prevent phantom reads under the REPEATABLE READ isolation level, illustrates lock ranges with examples, discusses when it degrades to row or gap locks, and highlights its advantages and potential deadlock risks.
InnoDBMySQLdeadlock
0 likes · 8 min read

This article examines a real‑world MySQL deadlock incident, explains the inner workings of shared, exclusive, gap, next‑key, and insert‑intention locks, and shows how concurrent delete‑then‑insert operations can saturate connection pools and degrade overall database performance.
Database PerformanceMySQLdeadlock
0 likes · 15 min read

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.
InnoDBMySQLgap-lock
0 likes · 11 min read


ITPUBJul 28, 2023 · Databases
This article explains InnoDB's three row‑lock types, when MySQL statements acquire implicit or explicit locks, and walks through concrete examples for unique and non‑unique index queries, showing how Next‑key, Record and Gap locks are determined.
Database ConcurrencyInnoDBMySQL
0 likes · 10 min read


政采云技术Feb 23, 2023 · Databases
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause MySQL Next‑Key lock deadlocks, explains the underlying gap‑lock and row‑lock mechanisms, demonstrates practical experiments, and proposes strategies such as postponing locks or using primary‑key updates to avoid such deadlocks.
MySQLSQLdatabase
0 likes · 14 min read


政采云技术Feb 21, 2023 · Databases
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause Next‑Key lock deadlocks in MySQL under repeatable‑read isolation, demonstrates the locking behavior with practical SQL examples, and proposes strategies such as post‑locking and primary‑key updates to prevent such deadlocks.
Isolation LevelMySQLSQL
0 likes · 14 min read


ITPUBFeb 8, 2023 · Databases
This article explains why an UPDATE on a MySQL row that changes a regular column proceeds without blocking, while an UPDATE that modifies the primary‑key value gets blocked, by analyzing the locks set by preceding transactions, the B+‑tree index structure, and the delete‑plus‑insert rewrite performed by InnoDB.
B+TreeMySQLgap-lock
0 likes · 9 min read

This comprehensive guide explains how MySQL's InnoDB engine applies row‑level locks—including shared, exclusive, gap, and next‑key locks—based on different SQL statements, isolation levels, and index types, and shows how to analyze and avoid phantom reads with practical experiments.
InnoDBMySQLRow-Level Locking
0 likes · 46 min read


政采云技术Jul 28, 2022 · Databases
This article walks through a real MySQL deadlock scenario, explaining the four necessary deadlock conditions, illustrating how Next‑Key locks on non‑unique indexes cause gap locks, and presenting step‑by‑step analysis, experimental verification, and practical recommendations to avoid such deadlocks in production systems.
MySQLSQLdatabase
0 likes · 15 min read

This article explains the four transaction isolation levels, defines phantom reads, and details how MySQL's InnoDB engine uses MVCC snapshot reads and next‑key locking (including the next‑key principle and lock composition) to prevent phantom reads under the REPEATABLE READ isolation level.
InnoDBIsolation LevelMVCC
0 likes · 2 min read

This article explains why phantom reads occur under MySQL's REPEATABLE READ isolation, how InnoDB uses MVCC for snapshot reads and next‑key locks for current reads, and demonstrates the mechanisms with code examples and version‑tracking diagrams.
Isolation LevelMVCCMySQL
0 likes · 7 min read

This article explains MySQL InnoDB’s transaction locking mechanisms—including shared, exclusive, intention, gap, and next‑key locks—covers lock compatibility, demonstrates lock behavior with practical examples, explores phantom reads, lost updates, optimistic vs. pessimistic locking, and analyzes common deadlock scenarios.
InnoDBLocksMySQL
0 likes · 28 min read


ITPUBOct 17, 2016 · Databases
This article examines MySQL InnoDB's next‑key lock behavior by creating a test table, running concurrent transactions, and analyzing how the lock range is determined across primary and secondary index columns, revealing why certain insert statements are blocked while others succeed.
ConcurrencyInnoDBMySQL
0 likes · 9 min read
