BestHub
Sign in
Tagged articles
866 articles
Page 2 of 9
Aikesheng Open Source Community
Aikesheng Open Source Community
Jan 20, 2025 · Databases

Understanding MySQL 8.0.13 sql_require_primary_key and Resolving Inconsistent Data After Adding Auto‑Increment Primary Keys

This article analyzes why MySQL 8.0.13 introduced the sql_require_primary_key parameter, demonstrates how adding auto‑increment primary keys can cause master‑slave data inconsistencies, explains the underlying InnoDB row‑id behavior, and provides a step‑by‑step solution to synchronize tables.

InnoDBauto_incrementmysql
0 likes · 7 min read
Understanding MySQL 8.0.13 sql_require_primary_key and Resolving Inconsistent Data After Adding Auto‑Increment Primary Keys
Liangxu Linux
Liangxu Linux
Jan 16, 2025 · Databases

Inside MySQL: How Buffer Pools, Indexes, and Logs Power Modern Databases

This article explains MySQL’s internal architecture, covering how data pages, B+‑tree and hash indexes, the Buffer Pool, Adaptive Hash Index, Change Buffer, Undo/Redo logs, the InnoDB storage engine, and the server layer work together to provide fast, reliable CRUD operations and support replication.

Database ArchitectureInnoDBbuffer pool
0 likes · 14 min read
Inside MySQL: How Buffer Pools, Indexes, and Logs Power Modern Databases
Aikesheng Open Source Community
Aikesheng Open Source Community
Jan 8, 2025 · Databases

How InnoDB Performs Undo Log Rollback and Record Deletion

This article explains the complete InnoDB undo‑log rollback process on MySQL 8.0, covering preparation, reading and parsing undo logs, constructing primary‑key index records, and the detailed steps for deleting secondary and primary index entries during a transaction rollback.

Database InternalsInnoDBmysql
0 likes · 12 min read
How InnoDB Performs Undo Log Rollback and Record Deletion
Senior Tony
Senior Tony
Dec 30, 2024 · Databases

Unlocking MySQL InnoDB: Deep Dive into ACID, Redo/Undo Logs, and Double Write

This article explains MySQL InnoDB's ACID properties in depth, detailing how atomicity relies on Redo and Undo logs, how consistency ensures business logic integrity, how isolation uses locks and MVCC with snapshot and current reads, and how durability is achieved through Redo logs, the innodb_flush_log_at_trx_commit setting, and the Double Write mechanism.

ACIDInnoDBIsolation
0 likes · 11 min read
Unlocking MySQL InnoDB: Deep Dive into ACID, Redo/Undo Logs, and Double Write
Aikesheng Open Source Community
Aikesheng Open Source Community
Dec 18, 2024 · Databases

How MySQL InnoDB Allocates Undo Segments

This article explains the internal process MySQL 8.0.32 InnoDB uses to allocate, create, and manage Undo segments—including cached segment retrieval, new segment creation, insertion into rollback segment lists, conflict avoidance through mutexes, and a concise step‑by‑step summary of the entire workflow.

InnoDBUNDOdatabase
0 likes · 9 min read
How MySQL InnoDB Allocates Undo Segments
Senior Tony
Senior Tony
Dec 6, 2024 · Databases

Why MySQL COUNT(*) Can Be Milliseconds Fast: Engine Tricks & Optimization Strategies

This article explains why COUNT(*) on a large InnoDB table can take seconds while MyISAM returns instantly, explores the underlying storage‑engine differences, and presents six practical techniques—including Redis counters, counting tables with transactions or triggers, parallel read threads, secondary indexes, and SHOW TABLE STATUS—to dramatically speed up row counting in MySQL.

InnoDBParallel ReadPerformance Optimization
0 likes · 9 min read
Why MySQL COUNT(*) Can Be Milliseconds Fast: Engine Tricks & Optimization Strategies
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 27, 2024 · Databases

Summary of InnoDB Lock Module Articles

This article provides a concise English recap of the 27 previously published InnoDB lock module articles, covering theoretical concepts such as table and row locks, lock waiting, deadlock handling, and practical scenarios like lock behavior during inserts and duplicate key operations.

Database InternalsInnoDBLocks
0 likes · 6 min read
Summary of InnoDB Lock Module Articles
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 13, 2024 · Databases

InnoDB Locking Analysis for INSERT … ON DUPLICATE KEY under REPEATABLE‑READ and READ‑COMMITTED

This article examines how InnoDB acquires row‑level locks during INSERT … ON DUPLICATE KEY operations under REPEATABLE‑READ and READ‑COMMITTED isolation levels, explains the lock types on primary and unique indexes, and shows the rollback and lock‑conversion process with concrete SQL examples.

Duplicate KeyInnoDBmysql
0 likes · 11 min read
InnoDB Locking Analysis for INSERT … ON DUPLICATE KEY under REPEATABLE‑READ and READ‑COMMITTED
ITPUB
ITPUB
Nov 11, 2024 · Databases

When Does MySQL Actually Update Index Blocks? A Deep Dive into InnoDB Update Mechanics

This article examines how MySQL InnoDB decides whether to modify index blocks during UPDATE statements, walks through the internal mysql_update workflow, shows debugging with GDB, explains three test scenarios, and validates the behavior by inspecting block LSNs with the innblock tool.

Database InternalsIndex UpdateInnoDB
0 likes · 18 min read
When Does MySQL Actually Update Index Blocks? A Deep Dive into InnoDB Update Mechanics
ITPUB
ITPUB
Nov 9, 2024 · Databases

Why MySQL Unique Indexes Still Allow Duplicates and How to Fix Them

This article explains why a MySQL 8 InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL or when logical deletion is used—and presents several practical strategies, including status counters, timestamps, extra IDs, hash fields, and batch‑insert techniques, to enforce true uniqueness.

Batch InsertHash FieldInnoDB
0 likes · 14 min read
Why MySQL Unique Indexes Still Allow Duplicates and How to Fix Them
JavaEdge
JavaEdge
Oct 28, 2024 · Databases

Why Do MySQL Transactions Deadlock? Reproduce and Prevent InnoDB Deadlocks

This article explains how MySQL InnoDB deadlocks occur during order‑record idempotency checks, demonstrates step‑by‑step reproduction with SQL scripts, analyzes the lock types involved, and provides practical strategies to avoid and resolve such deadlocks in production systems.

InnoDBSQLdatabase
0 likes · 13 min read
Why Do MySQL Transactions Deadlock? Reproduce and Prevent InnoDB Deadlocks
Alibaba Cloud Developer
Alibaba Cloud Developer
Oct 10, 2024 · Databases

Inside MySQL 8.0: How Table, Page, and Row Locks Manage Concurrency

This article provides a comprehensive overview of MySQL 8.0's concurrency control mechanisms, detailing table‑level MDL locks, server‑ and engine‑layer table locks, page‑level B+Tree locking, and row‑level lock types with code examples and real‑world deadlock scenarios.

Concurrency ControlDatabase InternalsInnoDB
0 likes · 28 min read
Inside MySQL 8.0: How Table, Page, and Row Locks Manage Concurrency
Aikesheng Open Source Community
Aikesheng Open Source Community
Oct 9, 2024 · Databases

Understanding InnoDB Locking: Repeatable Read vs. Read Committed Isolation Levels

This article demonstrates how InnoDB acquires different types of row and gap locks under REPEATABLE-READ and READ-COMMITTED isolation levels by creating a test table, inserting data, setting transaction isolation, executing SELECT ... FOR SHARE statements, and inspecting lock information from performance_schema.

InnoDBRead CommittedRepeatable Read
0 likes · 9 min read
Understanding InnoDB Locking: Repeatable Read vs. Read Committed Isolation Levels
Architect's Guide
Architect's Guide
Oct 4, 2024 · Databases

Using MySQL Router 8.2 for Read/Write Splitting with InnoDB ReplicaSet

The article demonstrates how MySQL Router 8.2 enables transparent read/write splitting for an InnoDB ReplicaSet, showing the architecture, configuration steps, connection behavior, and the generated routing settings that improve database performance without modifying application code.

DatabasePerformanceInnoDBReadWriteSplitting
0 likes · 4 min read
Using MySQL Router 8.2 for Read/Write Splitting with InnoDB ReplicaSet
Java Tech Enthusiast
Java Tech Enthusiast
Oct 3, 2024 · Databases

Understanding MySQL Architecture and SQL Execution Flow

The article outlines MySQL’s layered architecture—connector, parser, optimizer, executor, and binary log in the server layer and pluggable storage engines like InnoDB—then walks through the step‑by‑step processing of SELECT and UPDATE statements, highlighting permission checks, caching, plan selection, redo and binlog handling for crash‑safe durability.

Database ArchitectureInnoDBQuery Processing
0 likes · 11 min read
Understanding MySQL Architecture and SQL Execution Flow
Aikesheng Open Source Community
Aikesheng Open Source Community
Sep 24, 2024 · Databases

Why Does a SELECT on performance_schema.data_locks Hang MySQL? Deep Dive & Fix

A batch INSERT triggers a massive lock record buildup, and a concurrent SELECT on performance_schema.data_locks causes a memory allocation error that leaves trx_sys‑mutex unreleased, leading to a full MySQL hang; the article reproduces the bug, analyses the stack, explains the root cause, and shows the official fix in MySQL 8.0.37.

Bug AnalysisInnoDBPerformance Schema
0 likes · 9 min read
Why Does a SELECT on performance_schema.data_locks Hang MySQL? Deep Dive & Fix
Architect
Architect
Sep 19, 2024 · Databases

Phantom Read Verification and Analysis in InnoDB REPEATABLE‑READ Isolation

This article investigates whether the REPEATABLE‑READ isolation level in InnoDB fully prevents phantom reads by conducting a series of current‑read and snapshot‑read experiments, analyzing transaction behavior, MVCC mechanisms, and offering recommendations to avoid phantom anomalies.

InnoDBMVCCRepeatable Read
0 likes · 19 min read
Phantom Read Verification and Analysis in InnoDB REPEATABLE‑READ Isolation
ITPUB
ITPUB
Sep 8, 2024 · Databases

What’s New in MySQL 8.4? Key Changes, Compatibility, and Upgrade Guide

MySQL 8.4 becomes the new LTS release, offering detailed upgrade‑downgrade compatibility tables, dozens of InnoDB and temporary‑table parameter defaults, MGR and replication enhancements, new commands, and a list of deprecated features to help DBAs plan migrations efficiently.

8.4InnoDBMGR
0 likes · 11 min read
What’s New in MySQL 8.4? Key Changes, Compatibility, and Upgrade Guide
Sohu Tech Products
Sohu Tech Products
Sep 5, 2024 · Databases

How to Diagnose and Resolve MySQL InnoDB Deadlocks: A Step-by-Step Guide

This article walks through a real‑world MySQL InnoDB deadlock case, detailing log analysis, reproducing the issue with test data, explaining gap and insert‑intention locks, and presenting a practical solution that checks existence before updating or inserting to prevent deadlocks.

Database PerformanceInnoDBSQL
0 likes · 14 min read
How to Diagnose and Resolve MySQL InnoDB Deadlocks: A Step-by-Step Guide
JD Tech Talk
JD Tech Talk
Sep 5, 2024 · Databases

Why MySQL Auto‑Increment Primary Keys Can Become Non‑Continuous

This article explains the mechanisms behind MySQL auto‑increment primary keys, why gaps appear after failed inserts, unique‑index violations, transaction rollbacks, and batch operations, and how storage engines and innodb_autoinc_lock_mode settings influence the continuity of generated IDs.

InnoDBUnique Indexauto_increment
0 likes · 11 min read
Why MySQL Auto‑Increment Primary Keys Can Become Non‑Continuous
JD Cloud Developers
JD Cloud Developers
Sep 5, 2024 · Databases

Why Do MySQL Auto‑Increment IDs Skip? Understanding Gaps and Solutions

This article explains why MySQL auto‑increment primary keys can become non‑continuous, covering storage mechanisms, unique‑index violations, transaction rollbacks, batch inserts, and step‑size settings, and provides practical examples and diagrams to help developers diagnose and avoid ID gaps.

InnoDBUnique Indexauto_increment
0 likes · 11 min read
Why Do MySQL Auto‑Increment IDs Skip? Understanding Gaps and Solutions
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 28, 2024 · Databases

Understanding Implicit Locks in MySQL InnoDB

This article explains what implicit locks are in MySQL InnoDB, how to identify their presence on primary and secondary index records, the conditions under which they appear, and the process by which they are converted into explicit locks that can be inspected via performance_schema.

DatabaseLocksImplicitLockInnoDB
0 likes · 9 min read
Understanding Implicit Locks in MySQL InnoDB
Zhuanzhuan Tech
Zhuanzhuan Tech
Aug 27, 2024 · Databases

MySQL InnoDB Deadlock Analysis and Resolution Guide

This article presents a detailed walkthrough of a MySQL InnoDB deadlock case, covering background, log inspection, data preparation, reproduction steps, lock analysis, root‑cause explanation, and practical solutions to prevent and resolve similar deadlock issues.

InnoDBdatabasedeadlock
0 likes · 14 min read
MySQL InnoDB Deadlock Analysis and Resolution Guide
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 26, 2024 · Databases

Understanding and Managing MySQL Undo Tablespaces

This article explains the purpose, evolution, configuration parameters, and step‑by‑step maintenance procedures for MySQL Undo tablespaces, covering versions from pre‑5.6 to 8.0 and providing practical commands for viewing, creating, truncating, and dropping Undo tablespaces.

Database AdministrationInnoDBUndo Tablespace
0 likes · 11 min read
Understanding and Managing MySQL Undo Tablespaces
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 14, 2024 · Databases

Granting Table Locks and Row Locks in InnoDB

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
Granting Table Locks and Row Locks in InnoDB
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 13, 2024 · Databases

Understanding MySQL 8.0 Clone Plugin: Architecture, Implementation, and Comparison with Xtrabackup

This article explains the MySQL 8.0 Clone plugin introduced in version 8.0.17, compares it with Xtrabackup, details its five‑step cloning process, describes the code structure across SQL, plugin, and InnoDB layers, and outlines the page‑archiving subsystem used to track dirty pages during cloning.

Clone PluginDatabase ReplicationInnoDB
0 likes · 18 min read
Understanding MySQL 8.0 Clone Plugin: Architecture, Implementation, and Comparison with Xtrabackup
Java Tech Enthusiast
Java Tech Enthusiast
Aug 12, 2024 · Databases

MySQL InnoDB ReplicaSet and Read/Write Splitting with Router 8.2

The article explains how MySQL Router 8.2 automatically splits read and write traffic in an InnoDB ReplicaSet, routing reads to asynchronous replicas and writes to the primary, providing performance, scalability, and simplified management without any application‑level changes.

DatabaseOptimizationInnoDBReadWriteSplitting
0 likes · 3 min read
MySQL InnoDB ReplicaSet and Read/Write Splitting with Router 8.2
Lobster Programming
Lobster Programming
Aug 12, 2024 · Databases

Understanding MySQL Buffer Pool: Architecture, Caching, and Performance

This article explains how MySQL's Buffer Pool caches data in memory, its default size and configuration, the internal structure of instances, chunks, control blocks and pages, and how it manages data loading, dirty pages, and flushing to disk for optimal query performance.

Database CachingInnoDBPerformance Optimization
0 likes · 7 min read
Understanding MySQL Buffer Pool: Architecture, Caching, and Performance
vivo Internet Technology
vivo Internet Technology
Aug 7, 2024 · Databases

MySQL 5.7 DDL vs GH-OST: A Comprehensive Comparison of Online Schema Change Tools

While MySQL 5.7 native DDL methods (copy, inplace rebuild, index build, metadata‑only) vary in speed, space use, lock impact, and binlog output, GH‑OST consistently offers the fastest execution, lowest lock blocking, real‑time replication, albeit at roughly double storage cost, making it ideal for risk‑averse online schema changes.

DDLInnoDBOnline DDL
0 likes · 11 min read
MySQL 5.7 DDL vs GH-OST: A Comprehensive Comparison of Online Schema Change Tools
Java Tech Enthusiast
Java Tech Enthusiast
Aug 6, 2024 · Databases

MySQL Storage Engines: Overview, Differences, and Interview Guidance

MySQL’s pluggable storage‑engine architecture lets each table use a specific engine, with InnoDB (the default in MySQL 8.x) offering row‑level locking, ACID transactions, foreign‑key support, crash recovery, MVCC and a buffer pool, while MyISAM provides only table‑level locking, no transactions, and is suited mainly for read‑intensive workloads lacking recovery requirements.

InnoDBMyISAMStorage Engine
0 likes · 9 min read
MySQL Storage Engines: Overview, Differences, and Interview Guidance
Top Architect
Top Architect
Jul 22, 2024 · Databases

MySQL Router 8.2 Read/Write Splitting with InnoDB ReplicaSet

This article demonstrates how MySQL Router 8.2 can perform automatic read/write splitting using an InnoDB ReplicaSet, shows the required configuration, explains the behavior of read and write connections, and concludes with the benefits of the feature while also containing promotional material for unrelated services.

InnoDBReplicaSetRouter
0 likes · 7 min read
MySQL Router 8.2 Read/Write Splitting with InnoDB ReplicaSet
ITPUB
ITPUB
Jul 16, 2024 · Databases

Why MySQL 8.0.38 Crashes with Over 10,000 Tables and How to Fix It

Upgrading to MySQL 8.0.38 (or later) causes crashes when an instance contains more than ten thousand tables, even with validate_tablespace_paths disabled, but using a shared or general tablespace configuration can reliably avoid the failure.

CrashInnoDBTablespace
0 likes · 4 min read
Why MySQL 8.0.38 Crashes with Over 10,000 Tables and How to Fix It
Zhuanzhuan Tech
Zhuanzhuan Tech
Jul 3, 2024 · Databases

Analysis of MySQL DELETE Deadlock and Locking Mechanisms

This article examines why identical DELETE statements on a MySQL table with a unique index can cause deadlocks, reviews InnoDB lock types, reproduces the issue with SQL and Java code, and discusses mitigation strategies such as version upgrades, isolation level changes, and distributed locks.

DELETEInnoDBdatabase
0 likes · 14 min read
Analysis of MySQL DELETE Deadlock and Locking Mechanisms
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 26, 2024 · Databases

Slow Locking Logic in MySQL InnoDB

This article explains the slow‑path row‑locking algorithm used by InnoDB in MySQL 8.0.32, detailing how the engine checks whether a transaction already holds a lock, determines if it must wait, reuses existing lock structures, and allocates new ones when necessary.

Concurrency ControlDatabase InternalsInnoDB
0 likes · 15 min read
Slow Locking Logic in MySQL InnoDB
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
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 5, 2024 · Databases

Understanding the InnoDB Lock Module Structure

This article explains the structure and components of InnoDB's lock module, including the global lock_sys object, its hash tables, latch mechanisms, waiting thread slots, and related attributes, with detailed code examples and diagrams to illustrate how row and table locks are managed.

Database InternalsInnoDBconcurrency
0 likes · 15 min read
Understanding the InnoDB Lock Module Structure
Aikesheng Open Source Community
Aikesheng Open Source Community
May 29, 2024 · Databases

Analysis of InnoDB Table and Row Lock Structures in MySQL 8.0.32

This article examines the shared and distinct fields of InnoDB's table‑lock and row‑lock structures in MySQL 8.0.32, explains the layout of the lock_t, lock_table_t and lock_rec_t structs, decodes the type_mode bit‑field, and discusses how InnoDB merges compatible row‑locks using a bitmap memory area.

Database InternalsInnoDBconcurrency
0 likes · 13 min read
Analysis of InnoDB Table and Row Lock Structures in MySQL 8.0.32
Liangxu Linux
Liangxu Linux
May 13, 2024 · Databases

Master MySQL Transaction Isolation: From Dirty Reads to Serializable

This article explains MySQL's transaction concepts, the four isolation levels defined by the SQL standard, how each level prevents dirty reads, non‑repeatable reads, and phantom reads, and provides practical commands and example experiments to configure and observe their behavior in InnoDB.

InnoDBMVCCSQL
0 likes · 19 min read
Master MySQL Transaction Isolation: From Dirty Reads to Serializable
Cognitive Technology Team
Cognitive Technology Team
May 8, 2024 · Databases

Introduction to InnoDB Transaction Lock System in MySQL

This article provides a comprehensive overview of InnoDB's transaction lock subsystem in MySQL, explaining row‑level and table‑level lock types, their internal implementations, lock‑compatibility rules, practical SQL examples, and common deadlock scenarios with detailed code illustrations.

InnoDBRow-Level LockingTable-level Locking
0 likes · 29 min read
Introduction to InnoDB Transaction Lock System in MySQL
ITPUB
ITPUB
May 6, 2024 · Databases

How MySQL’s Write‑Ahead Log Protects Data During Power Outages

This article explains MySQL InnoDB’s write‑ahead logging, detailing the roles of Buffer Pool, Redo and Undo logs, checkpoint mechanisms, and how they ensure data consistency and atomicity when a sudden power loss occurs.

CheckpointDatabase RecoveryInnoDB
0 likes · 12 min read
How MySQL’s Write‑Ahead Log Protects Data During Power Outages
21CTO
21CTO
Apr 30, 2024 · Databases

What’s New in MySQL 8.4? Key Feature Changes and Default Settings Explained

This article summarizes the major changes introduced in MySQL 8.4, including updated InnoDB parameters, revised default values, new cloning plugin rules, Windows SASL‑based LDAP authentication, and adjustments to replication settings, helping developers and DBAs optimize their database configurations.

8.4Database ConfigurationInnoDB
0 likes · 8 min read
What’s New in MySQL 8.4? Key Feature Changes and Default Settings Explained
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 24, 2024 · Databases

Summary of the MySQL Transaction Module Series

This article reviews the 14-part series on MySQL's transaction module, covering initialization, transaction types, two‑phase commit, binlog handling, savepoints, and full rollback, and provides links to each detailed post, including discussions on BEGIN statements, read‑only transactions, and the internal mechanisms of InnoDB.

BinlogInnoDBSavepoint
0 likes · 5 min read
Summary of the MySQL Transaction Module Series
Architecture & Thinking
Architecture & Thinking
Apr 16, 2024 · Databases

Master MySQL InnoDB Locks: How SELECT…FOR UPDATE Behaves

This article explains the default repeatable‑read isolation level in InnoDB, details the three main lock types—record, gap, and next‑key—and shows how SELECT … FOR UPDATE applies different locking rules depending on primary key, unique, ordinary, range, or table‑scan queries, with practical SQL examples.

Database ConcurrencyInnoDBSELECT FOR UPDATE
0 likes · 10 min read
Master MySQL InnoDB Locks: How SELECT…FOR UPDATE Behaves
Liangxu Linux
Liangxu Linux
Apr 4, 2024 · Databases

Why MySQL Tables Stall After 20 Million Rows and How B‑Link Trees Fix It

The article examines why MySQL performance drops sharply when a single table exceeds tens of millions of rows, debunks the index‑depth myth, explains SMO concurrency limits in InnoDB, and shows how B‑Link Tree indexes and heap‑organized tables can overcome these bottlenecks.

B-Link TreeB-TreeDatabase Performance
0 likes · 10 min read
Why MySQL Tables Stall After 20 Million Rows and How B‑Link Trees Fix It