Tagged articles

GAP Lock

27 articles · Page 1 of 1
Wukong Talks Architecture
Wukong Talks Architecture
May 28, 2026 · Databases

Understanding MySQL InnoDB Locks: Types, Queries, and Common Pitfalls

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.

GAP LockInnoDBIsolation Levels
0 likes · 20 min read
Understanding MySQL InnoDB Locks: Types, Queries, and Common Pitfalls
New Oriental Technology
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
Su San Talks Tech
Su San Talks Tech
Nov 15, 2023 · Databases

Understanding MySQL SELECT … FOR UPDATE Locks: Row, Gap, or Table?

This article explains how MySQL's SELECT … FOR UPDATE statement applies different lock types—row, gap, or table—depending on whether the WHERE clause uses primary keys, unique indexes, regular indexes, range queries, or empty results, and demonstrates each case with practical SQL examples and screenshots.

GAP LockLockingSELECT FOR UPDATE
0 likes · 10 min read
Understanding MySQL SELECT … FOR UPDATE Locks: Row, Gap, or Table?
ITPUB
ITPUB
Apr 19, 2023 · Databases

Can InnoDB’s REPEATABLE READ Truly Prevent Phantom Reads? A Deep Dive

This article explains how InnoDB’s REPEATABLE READ isolation level uses MVCC and gap locks to prevent most phantom reads, illustrates the mechanisms with SQL examples and diagrams, and outlines the remaining edge cases and best practices for fully avoiding phantom reads.

GAP LockInnoDBMVCC
0 likes · 9 min read
Can InnoDB’s REPEATABLE READ Truly Prevent Phantom Reads? A Deep Dive
ITPUB
ITPUB
Feb 8, 2023 · Databases

Why Does One MySQL UPDATE Block While Another Doesn’t? A Deep Dive into Row‑Level Locks

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+TreeGAP LockNext-Key Lock
0 likes · 9 min read
Why Does One MySQL UPDATE Block While Another Doesn’t? A Deep Dive into Row‑Level Locks
政采云技术
政采云技术
Apr 26, 2022 · Databases

Analysis of Lock Wait Timeout and Gap Locks in InnoDB

This article analyzes a MySQL InnoDB lock wait timeout error, explains how gap locks cause blocked inserts and phantom reads under repeatable‑read isolation, and offers optimization strategies such as primary‑key deletions, while concluding with a recruitment invitation for the Zero technology team.

GAP LockInnoDBTransaction
0 likes · 7 min read
Analysis of Lock Wait Timeout and Gap Locks in InnoDB
Top Architect
Top Architect
Oct 3, 2021 · Databases

Understanding the INSERT Locking Process in MySQL by Analyzing the Source Code

This article examines MySQL's INSERT locking behavior, explains why phantom reads do not occur under REPEATABLE READ isolation, and walks through compiling the MySQL source, debugging lock acquisition, and the role of insert‑intention and gap locks using detailed code examples and execution traces.

GAP LockInnoDBInsert
0 likes · 18 min read
Understanding the INSERT Locking Process in MySQL by Analyzing the Source Code
Beike Product & Technology
Beike Product & Technology
Jan 6, 2021 · Databases

Analyzing MySQL Deadlock Cases and Prevention Strategies

This article investigates a MySQL InnoDB deadlock observed during a holiday period, explains how gap locks and next‑key locks on a composite index cause mutual waiting, reproduces the issue with large test data, and offers practical guidelines to avoid similar deadlocks in production environments.

DeadlockGAP LockInnoDB
0 likes · 12 min read
Analyzing MySQL Deadlock Cases and Prevention Strategies
Programmer DD
Programmer DD
Apr 26, 2020 · Databases

Understanding MySQL Table and Row Locks: Mechanisms, Modes, and Types

This article explains the differences between MySQL table locks and row locks, how they are implemented by the server and storage engines, the lock modes and types (including intention, auto‑increment, gap, next‑key, and insert‑intention locks), and provides practical SQL examples with diagrams.

GAP LockInnoDBTransaction Isolation
0 likes · 13 min read
Understanding MySQL Table and Row Locks: Mechanisms, Modes, and Types
dbaplus Community
dbaplus Community
Mar 17, 2019 · Databases

Understanding InnoDB Locks: From Record Locks to Deadlocks

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.

DeadlockGAP LockInnoDB
0 likes · 28 min read
Understanding InnoDB Locks: From Record Locks to Deadlocks
Youzan Coder
Youzan Coder
Feb 1, 2019 · Databases

Why Gap Locks Trigger Deadlocks in InnoDB: A Real‑World MySQL Case Study

An in‑depth MySQL case study shows how concurrent gap‑locks under REPEATABLE READ can cause a deadlock when two transactions delete non‑existent rows and then attempt inserts, includes full DDL, log excerpts, step‑by‑step analysis, and practical mitigation strategies.

DeadlockGAP LockInnoDB
0 likes · 8 min read
Why Gap Locks Trigger Deadlocks in InnoDB: A Real‑World MySQL Case Study
ITPUB
ITPUB
Oct 17, 2016 · Databases

Why Some MySQL Inserts Are Blocked: Decoding Next‑Key Lock Ranges

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.

GAP LockInnoDBNext-Key Lock
0 likes · 9 min read
Why Some MySQL Inserts Are Blocked: Decoding Next‑Key Lock Ranges