Tagged articles
Table Lock
24 articles · Page 1 of 1

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

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


ITPUBFeb 24, 2026 · Databases
A real‑world MySQL incident shows that using INSERT … SELECT without proper indexing can cause full‑table scans and progressive row locks, leading to massive payment failures, but adding an index on the filter column prevents the lock and restores safe batch migration.
INSERT SELECTIndexMySQL
0 likes · 8 min read

This article explains OceanBase's Offline DDL implementation, showing how it rebuilds tables, manages table locks, tracks execution progress, evaluates performance, and answers common questions about lock behavior and time estimation with detailed SQL examples and log analysis.
Database MigrationOceanBaseOffline DDL
0 likes · 24 min read

This guide outlines a step‑by‑step method to diagnose and release MySQL table locks by checking open tables, inspecting running processes, querying InnoDB transaction and lock tables, and generating KILL statements to terminate blocking sessions.
MySQLSQLTable Lock
0 likes · 3 min read

This article explains how InnoDB in MySQL 8.0.32 grants table and row locks, detailing the active versus passive acquisition, the lock‑granting algorithms for tables and rows, and the priority, weight, and FIFO rules that determine lock ordering and deadlock handling.
Database ConcurrencyInnoDBMySQL
0 likes · 11 min read

This article explains how MySQL's SELECT ... FOR UPDATE statement acquires either row-level or table-level locks depending on the presence of indexes or primary keys, and demonstrates the behavior with multiple transaction examples and a sample table definition.
MySQLRow LockSELECT FOR UPDATE
0 likes · 4 min read

This article explains how MySQL table‑level locks and metadata locks (MDL) can block both reads and writes, describes the two lock implementations, their types, the interaction with LOCK TABLES and FLUSH TABLES, and provides practical queries for diagnosing lock‑related blocking issues.
LocksMetadata LockMySQL
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


ITPUBOct 25, 2023 · Databases
This article systematically investigates how MySQL's SELECT FOR UPDATE behaves under different versions (5.7 and 8.0) and isolation levels (RR and RC), covering twenty scenarios that vary by index type and query range, and summarizes whether the statement acquires row‑level, gap, or table‑level locks.
MySQLRow LockSELECT FOR UPDATE
0 likes · 21 min read

This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.
Database PerformanceLock MechanismMyISAM
0 likes · 7 min read

This article explains how MySQL's SELECT … FOR UPDATE adds a pessimistic lock, and demonstrates that using an indexed column or primary key results in a row lock, while lacking an index causes a table lock, with step‑by‑step verification using multiple transaction examples.
MySQLRow LockSELECT FOR UPDATE
0 likes · 4 min read

This article explains how a plain SELECT does not lock data, while SELECT … FOR UPDATE adds a pessimistic lock whose scope—row or table—depends on whether the query uses an indexed or primary‑key column, and demonstrates the behavior with SQL examples and transaction scenarios.
MySQLRow LockSELECT FOR UPDATE
0 likes · 4 min read

This article explains how the SELECT ... FOR UPDATE statement in MySQL adds a pessimistic lock, describes when it results in a row lock versus a table lock based on index usage, provides verification SQL, example scenarios, and practical tips for transaction handling.
MySQLRow LockSELECT FOR UPDATE
0 likes · 5 min read

This article explains how MySQL's SELECT ... FOR UPDATE statement adds a pessimistic lock, detailing when it results in row-level locking versus table-level locking based on the presence of indexes or primary keys, and demonstrates the behavior with multiple practical examples.
MySQLRow LockSELECT FOR UPDATE
0 likes · 5 min read

This article explains how the MySQL SELECT … FOR UPDATE statement adds a pessimistic lock, why the lock type (row or table) depends on the presence of an index or primary key, and demonstrates the behavior with practical examples and SQL scripts.
IndexMySQLRow Lock
0 likes · 5 min read

This article explains how MySQL's SELECT ... FOR UPDATE statement adds pessimistic locks, detailing when it results in row‑level locks versus table‑level locks based on the presence of indexes or primary keys, and provides verification examples with code and transaction scenarios.
MySQLRow LockSELECT FOR UPDATE
0 likes · 4 min read

The article explains why an INSERT INTO SELECT statement can lock an entire InnoDB table in MySQL, demonstrates the lock behavior with transaction examples, and offers two solutions—adding an index on the filtered column and adjusting the transaction isolation level—to avoid table‑wide blocking.
INSERT SELECTIndexInnoDB
0 likes · 13 min read

This article explains how using MySQL's INSERT INTO … SELECT without proper indexing can cause full‑table scans and locks, illustrates the problem with a real‑world order‑migration incident, and provides a concrete solution by adding an index to the source column.
Database MigrationINSERT INTO SELECTIndexing
0 likes · 8 min read

This article analyzes a real‑world incident where using INSERT INTO SELECT to migrate large order data caused full‑table scans and locking in MySQL, explains the underlying lock behavior, and presents a solution that adds an index and forces its use to avoid the problem.
Database MigrationINSERT INTO SELECTIndex
0 likes · 6 min read

An engineer’s mishap with a massive ‘INSERT INTO … SELECT’ migration exposed how full‑table scans lock MySQL tables, causing payment failures, and shows that adding an index on the filter column prevents the lock and ensures safe, efficient data transfer.
Database MigrationINSERT INTO SELECTIndex
0 likes · 7 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 explains MySQL's lock mechanisms, covering table‑level, row‑level, and page locks, the differences between pessimistic and optimistic locking, shared locks, and provides guidance on when to choose table locks versus row locks in various application scenarios.
Database ConcurrencyLocksMySQL
0 likes · 7 min read
