Tagged articles
gap-lock
36 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 comprehensive guide explains MySQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and lock mechanisms (table, row, gap, next-key locks), demonstrating how dirty reads, non-repeatable reads, and phantom reads occur, with practical deadlock prevention strategies and enterprise-level isolation level selection guidelines.
ACIDLock MechanismsMySQL
0 likes · 12 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


liandkAug 20, 2026 · Databases
Understanding MySQL’s lock types—table, row, and gap locks—reveals how they act as resource tokens to ensure data consistency under concurrency, while the article details their characteristics, appropriate and prohibited use cases, common pitfalls like lock degradation and deadlocks, and provides hands‑on SQL examples to reproduce and avoid these issues.
ConcurrencyLocksMySQL
0 likes · 10 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.
IndexingInnoDBMySQL
0 likes · 10 min read

This article breaks down MySQL’s locking mechanisms—table, row, and gap locks—explaining their principles, performance trade‑offs, when they are triggered, how they relate to index usage, and provides practical deadlock avoidance techniques and a concise cheat‑sheet for common concurrency problems.
ConcurrencyLocksMySQL
0 likes · 7 min read

The article explains that MVCC in MySQL InnoDB prevents phantom reads for snapshot (ordinary SELECT) queries by using ReadView, while write‑conflict scenarios that appear as phantom reads are handled by unique index checks and gap locks, highlighting the distinction between snapshot and current reads.
InnoDBMVCCPhantom Read
0 likes · 5 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 walks through ten essential MySQL InnoDB interview questions, covering why InnoDB uses B+ trees, MVCC isolation levels, two‑phase commit, gap locks, buffer‑pool LRU design, undo‑log mechanics, binlog formats, lock‑upgrade behavior, double‑write buffering, and the differences between clustered and non‑clustered indexes.
2PCB+TreeBinlog Formats
0 likes · 17 min read

This article investigates a deadlock that occurs when using INSERT … ON DUPLICATE KEY UPDATE in a multi‑threaded MySQL 8.0 environment, reproduces the issue, examines InnoDB lock logs, walks through the relevant source‑code call chain, and demonstrates how gap locks cause the deadlock.
DebuggingINSERTInnoDB
0 likes · 22 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

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.
MySQLRow LockSELECT FOR UPDATE
0 likes · 10 min read

This article sets up a MySQL 5.7 environment, runs a series of SELECT FOR UPDATE queries on primary keys, unique indexes, normal indexes, range scans and non‑indexed columns, then analyzes the resulting lock types—row, gap, next‑key and table locks—and distills practical locking rules.
InnoDBMySQLRow Lock
0 likes · 13 min read

This article analyzes a production deadlock caused by MyBatis‑Plus’s saveOrUpdate method, explains how MySQL gap locks lead to the issue, demonstrates reproducible tests, and offers practical solutions such as disabling gap locks or customizing the saveOrUpdate implementation.
MyBatis PlusMySQLdeadlock
0 likes · 13 min read

This article investigates how the MyBatis‑Plus saveOrUpdate method can trigger gap‑lock deadlocks in MySQL, explains the underlying concepts of deadlocks and gap locks, reproduces the issue with concrete SQL scripts, and proposes both risky and recommended mitigation strategies.
MyBatis PlusMySQLdatabase
0 likes · 13 min read


ITPUBApr 19, 2023 · Databases
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.
InnoDBMVCCMySQL
0 likes · 9 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 article explains MySQL row‑level locking by analyzing three transactions—A, B, and C—to show why an UPDATE on a primary‑key column can block while an UPDATE on a non‑indexed column proceeds without waiting.
B+TreeMySQLUpdate
0 likes · 7 min read

This article experimentally verifies how SELECT FOR UPDATE behaves under different MySQL versions, isolation levels, and index conditions, revealing when it acquires row‑level, gap, or table‑level locks across 20 distinct scenarios.
InnoDBMySQLRow Lock
0 likes · 22 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

This article investigates why S‑type GAP locks appear in deadlocks under the READ COMMITTED isolation level in MySQL InnoDB, detailing the transaction workflow, lock acquisition during INSERT operations, lock inheritance after rollbacks, and reproducing the deadlock scenario with code examples and diagrams.
InnoDBMySQLRead Committed
0 likes · 11 min read


政采云技术Apr 26, 2022 · Databases
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.
InnoDBMySQLdatabase
0 likes · 7 min read

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.
INSERTInnoDBMySQL
0 likes · 18 min read

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.
InnoDBMySQLdeadlock
0 likes · 12 min read

This article explains how InnoDB implements row locks, gap locks, and next‑key locks to prevent phantom reads, describes how the lock ranges are determined, and demonstrates their effects through multiple transaction scenarios with detailed SQL code examples.
InnoDBMySQLRR
0 likes · 11 min read

This article provides a detailed overview of MySQL's locking mechanisms—including table, row, gap, and deadlock handling—across MyISAM and InnoDB storage engines, with practical code examples, performance considerations, and solutions for high‑concurrency scenarios.
InnoDBLock MechanismMyISAM
0 likes · 19 min read

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.
InnoDBMySQLRow Lock
0 likes · 13 min read

This article explains the differences between MySQL table locks and row locks, describes how MyISAM and InnoDB handle locking, provides explicit lock syntax with examples, discusses gap locks, and offers practical recommendations for avoiding lock contention in concurrent applications.
ConcurrencyInnoDBLocks
0 likes · 7 min read

This article examines MySQL's insert locking behavior, including unique‑key checks, S Next‑Key locks, insert‑intention locks, gap locks, and the resulting deadlock scenarios, and provides practical recommendations for avoiding such lock conflicts in production environments.
INSERTMySQLdatabase
0 likes · 10 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

This article walks through MySQL InnoDB lock mechanisms, demonstrates how concurrent INSERTs on a unique key generate intention and gap locks, and uses detailed engine status logs to explain why those locks can cause deadlocks.
InnoDBInsert LockMySQL
0 likes · 14 min read

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.
InnoDBMySQLSQL
0 likes · 8 min read

This article examines why concurrent upsert operations in a high‑throughput MySQL service can cause deadlocks due to GAP locks and phantom reads, explains the underlying InnoDB locking mechanisms, and offers practical solutions such as adjusting isolation levels or redesigning data sharding logic.
MySQLPhantom Readdeadlock
0 likes · 9 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
