This article explains why MySQL 5.7 community edition may block online DDL operations when leftover temporary table files remain, analyzes the root cause, and provides step‑by‑step commands and alternative solutions to safely remove the orphaned tables.
This article explains MySQL InnoDB's dual memory and on‑disk architecture, detailing the various tablespace types (system, file‑per‑table, general, undo, temporary), their internal structures (segments, extents, pages), configuration tips, and how tables, row formats, primary keys, auto‑increment lock modes, and B+Tree indexes are organized and managed.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—through step‑by‑step examples, demonstrating their effects on dirty reads, non‑repeatable reads, phantom reads, and performance considerations, helping developers choose the appropriate level.
This article explores the inner workings of InnoDB's architecture—including the Buffer Pool, Change Buffer, Adaptive Hash Index, and Log Buffer—detailing their designs, configuration parameters, performance impacts, and best‑practice recommendations for high‑throughput MySQL deployments.
This article explains MySQL’s locking mechanisms, categorizing locks by performance, operation type, data granularity, and finer‑grained levels such as gap and next‑key locks, and offers guidance on selecting the appropriate lock strategy for reliable concurrent database operations.
This article explains how MySQL InnoDB transforms a simple INSERT statement into a complex on‑disk record by detailing the COMPACT row format, record header, variable‑length field list, NULL bitmap, and overflow handling, helping developers optimize storage and performance.
This article explains MySQL InnoDB locking mechanisms—including row, record, gap, next‑key, and intention locks—illustrates each type with SQL statements and performance_schema data, and shows how they interact during concurrent transactions.
This article explains MySQL's index limits for InnoDB and MyISAM engines, detailing the maximum number of indexes per table, the maximum columns per index, and provides practical recommendations on how many indexes a table should have to balance performance and maintenance.
This guide walks you through locating MySQL deadlock logs, analyzing their contents to pinpoint transaction timing, order, and lock details, and identifying the root cause of the deadlock, including special locking scenarios that can trigger deadlocks.
The article explains why scanning a 200 GB InnoDB table on a MySQL server with only 100 GB of memory does not cause an out‑of‑memory crash, detailing MySQL's row‑by‑row net buffer transmission, InnoDB buffer‑pool behavior, and the improved LRU algorithm that limits memory impact.
This guide walks you through locating the deadlock log, analyzing its contents to determine the time, order, and involved SQL statements, and pinpointing the root cause of MySQL InnoDB deadlocks, including special locking scenarios and how to interpret lock structures.
A full‑table scan of a 200 GB InnoDB table on a MySQL server with 100 GB RAM does not exhaust server memory because MySQL streams rows to the client, uses a fixed net_buffer, and InnoDB’s optimized LRU algorithm limits buffer‑pool pressure, ensuring stable performance.
This article explains how to find the MySQL deadlock log, parse its contents to determine the time, order, and affected rows, identify the lock types and root cause, and provides extended examples of special locking scenarios, all illustrated with real‑world SQL and code snippets.
This article explains how to reproduce a MySQL InnoDB lock‑wait scenario, use system tables such as information_schema.processlist, innodb_trx, sys.innodb_lock_waits and performance_schema to locate the blocking transaction, and finally resolve the issue by killing the offending session, while also providing quick‑check queries and parameter tuning advice.
This article explains how MySQL's count() works across storage engines, why InnoDB scans rows and can time out on massive tables, and presents practical alternatives such as using EXPLAIN rows, a dedicated count table, batch processing, or binlog‑to‑Hive for efficient row‑count estimation.
This article explains InnoDB clustered and secondary indexes, their physical B‑tree storage, page‑size and fill‑factor settings, the three‑phase sorted index build process, and the design, tables, cache, document‑ID handling, transaction semantics, and monitoring of InnoDB full‑text indexes.
This article explains how MySQL redo logs and binlog logs work together to ensure data durability, consistency, and high availability, covering InnoDB crash‑recovery, flush policies, log file groups, binlog formats, two‑phase commit, and the complete recovery workflow.
This article walks through the investigation of MySQL bug #115352, describing how an assertion failure in InnoDB’s dictionary cache caused a server crash during a killed ALTER TABLE on a partitioned table, the debugging steps taken, and the eventual fix applied in MySQL 8.0.40.
This article experimentally compares MySQL auto_increment, UUID, and random Snowflake keys by measuring insert and query speeds, analyzing InnoDB index behavior, and discussing the trade‑offs of each primary‑key strategy, ultimately showing why auto_increment generally outperforms UUID in large‑scale workloads.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types—including primary, ordinary, composite, full‑text, clustered and non‑clustered—and compares B‑Tree with B+Tree structures to help you design faster, more efficient queries.
This article provides a comprehensive index of a MySQL internals series covering the transaction, lock, and undo modules, detailing each sub‑topic such as transaction initialization, two‑phase commit, various lock types, deadlock handling, and undo log management across numerous linked articles.
The article explains how InnoDB handles transaction commit by modifying Insert and Update Undo segment states, generating a transaction commit number, adding rollback segments to the purge queue, and finally cleaning or caching Undo segments based on their usage and state.
This guide explains MySQL 8's main directory layout, the locations of command binaries and configuration files, how databases and system schemas are represented on the file system, and the differences between InnoDB and MyISAM storage engines, including tablespace and file naming conventions.
This article explains how to read and parse the Undo log generated by a DELETE operation in MySQL 8.0.32 InnoDB, covering preparation, log extraction, detailed field parsing, locating the primary index record, and the step‑by‑step rollback of secondary and primary index records.
This article explains how MySQL 8.0 InnoDB generates and stores Delete Undo logs, detailing the log structure, field meanings, type_flag and info_bits decoding, address composition via DB_ROLL_PTR, and provides concrete SQL examples and shell calculations to illustrate the process.
This article explains MySQL's storage engine architecture, compares built‑in engines such as InnoDB and MyISAM, details their file system interactions, table‑space types, and transaction features—including ACID properties, isolation levels, locking mechanisms, and practical migration steps for production environments.
This article experimentally compares the performance of various MySQL COUNT queries on an InnoDB table with one million rows, explains the underlying execution plans using EXPLAIN, analyzes primary‑key versus secondary‑index behavior, and contrasts InnoDB’s fast count implementation.
This article explains how MySQL 8.0.32's InnoDB engine generates, reads, and parses Undo logs for an UPDATE operation, and details the step‑by‑step process of locating the primary record, constructing a rollback record, and restoring both secondary and primary index entries.
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.
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.
This article explains how MySQL 8.0.32 InnoDB generates and stores Update undo logs, detailing the log format, the meaning of each field, how to interpret the log address, and provides concrete SQL examples and shell calculations for practical insight.
This article walks through a real MySQL deadlock case, explaining the background, reproducing the issue, analyzing lock information from InnoDB status, and presenting practical steps to identify root causes and fix the deadlock safely.
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.
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.
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.
This article explains how MySQL 8.0.32 InnoDB generates and stores Insert Undo logs, detailing their binary format, field meanings, example values, and how the hidden DB_ROLL_PTR field encodes the log address, with accompanying SQL and shell examples.
This article explains how MySQL stores and manages data by introducing the InnoDB storage engine, its buffer pool, data pages, B+Tree and adaptive hash indexes, change buffer, undo/redo logs, binlog, and the server layer that parses, optimizes, and executes SQL statements.
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.
The article explains why MySQL 8 replaces fragmented, non‑transactional metadata files with a unified, InnoDB‑backed transactional data dictionary, details the information_schema views that expose it, and enumerates the resulting benefits such as atomicity, consistency, crash recovery, performance gains, simplified administration, reduced lock contention, smoother upgrades, improved DDL handling, and enhanced security.
This article explains MySQL InnoDB's multi-version concurrency control (MVCC), covering its basic concepts, hidden columns, isolation levels, the creation and evaluation of Read Views, and includes detailed source code examples and step‑by‑step experiments to illustrate transaction visibility.
This article examines MySQL 8.0.32 InnoDB's rollback segment memory layout and details the algorithms used to allocate rollback segments for user temporary tables and regular tables, including the role of system variables, counters, and the distribution matrix across undo tablespaces.
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.
This article explains the four‑layer logical structure of InnoDB Undo logs—Undo pages, Undo segments, rollback segments, and Undo tablespaces—detailing their roles, management, related system variables, and how they affect transaction rollback and storage in MySQL 8.0.32.
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.
This article reviews the long‑standing performance problems of TRUNCATE and DROP TABLE in MySQL, tracing their origins through official manuals and historical bugs, summarizing the optimizations introduced in MySQL 5.5.23, 5.7, 8.0, and 8.4, and offering practical guidance for mitigating remaining latency.
This article examines how InnoDB acquires various row, gap, and next‑key locks during INSERT … ON DUPLICATE KEY UPDATE statements under REPEATABLE‑READ and READ‑COMMITTED isolation levels, illustrating the lock behavior with detailed SQL examples and performance‑schema queries.
This article investigates how many records a plain SELECT * query can return in MySQL 8 by examining max_allowed_packet, packet definitions, single‑row size limits, column count restrictions, InnoDB row formats, and practical experiments that reveal the real constraints on query result size.
This article explains InnoDB lock types, their characteristics, and how MySQL 5.6 and 8.0 handle adding a column without fully locking the table, detailing online DDL, atomic DDL, and performance considerations for large tables.
This article explains MySQL transaction fundamentals, the ACID properties, MVCC implementation, isolation levels, snapshot versus current reads, and the various lock types and modes, and provides practical examples of how different SQL statements acquire locks under InnoDB.
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.
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.
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.
This article explains how MySQL 8.0.32 InnoDB handles INSERT … ON DUPLICATE KEY UPDATE under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, lock acquisition process, and why the same exclusive record lock is obtained in both cases.
This article reproduces a MySQL 8.0.32 InnoDB deadlock using two concurrent DELETE statements on the same secondary index, explains the lock types and lock‑wait graph, and details why the deadlock occurs and how it is resolved.
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.
This article details a MySQL 8.0.35 crash caused by an InnoDB assertion that a VARCHAR length exceeds 65,535 bytes, explains the error‑log and core‑dump analysis, shows how to reproduce the bug, and notes its resolution in MySQL 8.0.35.
This article investigates how many rows should be inserted per batch in MySQL, analyzes the limits of SQL statement size, buffer pool, transaction overhead, and indexes, and presents empirical tests that reveal the most efficient batch size for different data volumes.
The article explains MySQL InnoDB’s multi‑thread architecture—including the Master, IO, Purge, and Page Cleaner threads—detailing each thread’s responsibilities, configurable parameters, and how they cooperate to achieve high‑performance, concurrent data reads and writes while maintaining consistency and crash recovery.
This article demonstrates how InnoDB acquires and releases row‑level locks for a sample table under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the SQL statements used, the lock types observed in the performance_schema, and the reasons behind the observed locking patterns.
This article demonstrates how InnoDB handles row, next‑key, and gap locks for a SELECT … FOR SHARE query under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, SQL examples, lock inspection queries, and a concise summary of the differences.
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.
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.
COUNT(*), COUNT(1) and COUNT(column) may look similar, but they differ in how MySQL processes them, the rows they count, and performance implications, especially with NULL values and index usage; this article explains each form, cites official InnoDB behavior, and guides proper usage.
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.
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.
This article explains MySQL storage engine concepts, compares InnoDB, MyISAM, and MEMORY, describes how data is stored in shared tablespace versus file‑per‑table modes, and provides configuration commands for switching modes, changing default engines, and inspecting or altering a table’s engine.
The article explains MySQL’s Multi-Range Read (MRR) optimization, detailing how it scans secondary indexes, buffers and sorts primary keys, leverages disk pre‑read and cache locality to convert random I/O into sequential I/O, and shows configuration, monitoring methods, and a concrete orders‑table example.
This tutorial demonstrates, using MySQL 8.0.32 InnoDB source code, how REPEATABLE-READ and READ-COMMITTED isolation levels affect the locking behavior of SELECT ... FOR SHARE statements on primary‑key rows, including preparation, execution, and detailed lock analysis.
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.
This article explains the fundamental concepts of MySQL index structures, detailing how clustered (primary) indexes store data physically in order, how non‑clustered (secondary) indexes reference primary keys, and compares their performance implications and practical usage scenarios.
This article explains how InnoDB’s buffer pool works, covering its purpose, configuration parameters, internal structures such as free, flush, and LRU lists, multi‑instance handling, adaptive hash indexing, and strategies for page eviction and performance optimization.
This article explains the core components of MySQL's InnoDB storage engine—including undo log, redo log, buffer pool, change buffer, and binlog—detailing their roles in data writing, crash recovery, MVCC, and master‑slave synchronization, and describing related flushing strategies and LRU management.
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.
This article examines how MySQL 8.0.32 InnoDB handles locking when inserting a row that violates a unique index under the READ‑COMMITTED isolation level, detailing the preparation steps, observed lock types, the underlying mechanism, and the final cleanup actions.
This article explains MySQL index classifications, the inner workings of B+‑tree and hash indexes, the differences between clustered and secondary indexes, and advanced concepts such as covering indexes, index push‑down, index merge, cost‑based index selection, and common pitfalls that cause index inefficiency.
This article examines how InnoDB handles row locking when an INSERT triggers a primary‑key index conflict, detailing the lock types on primary and unique indexes, the implicit‑to‑explicit lock conversion, and the role of the supremum record in the locking process.
The article describes a MySQL 8.0.18 backup failure caused by an undo log remaining in cleanup state for over ten hours, details the error logs, shows how the ChatDBA AI tool analyses and resolves the issue, compares it with ChatGPT‑4o, and provides related activity previews.
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.
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.
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.
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.
This article examines how InnoDB acquires shared record locks during a duplicate‑primary‑key INSERT, explains the underlying lock‑checking mechanism, demonstrates lock behavior with concurrent DELETE and INSERT transactions, and provides SQL examples and performance_schema queries to illustrate the process.
This article examines whether the REPEATABLE‑READ isolation level in InnoDB fully prevents phantom reads by conducting systematic current‑read and snapshot‑read experiments, analyzing lock mechanisms, MVCC behavior, and presenting detailed SQL scripts, results, and recommendations for avoiding phantom reads.
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.
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.
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.
This article provides a step‑by‑step analysis of MySQL 8.0.32 InnoDB deadlock logs, covering preparation, log extraction, auxiliary information, transaction details, row‑lock structures, and the final summary, with full SQL examples and code snippets for deep understanding.
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.
This article explains MySQL InnoDB’s linear and random prefetch mechanisms, the role of the innodb_read_ahead_threshold and innodb_random_read_ahead variables, and how the LRU buffer‑pool list manages hot and cold pages to improve overall query efficiency.
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.
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.
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.
A mock interview walks through MySQL transaction fundamentals, ACID principles, MVCC mechanics, undo log structures, and the differences between Read Committed and Repeatable Read isolation levels, illustrating each concept with code snippets and diagrams.
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.
This article explains the various moments when InnoDB table and row locks are released during a transaction's lifecycle, covering mismatched WHERE conditions, the two‑phase commit prepare stage, AUTO‑INC locks, and differences across isolation levels in MySQL 8.0.32.
This article explains the limitations of MySQL's built‑in query cache, how the InnoDB buffer pool works and can be tuned, and introduces ReadySet as an external caching middleware to improve query performance after a major product upgrade.
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.
This article explains how InnoDB resolves a deadlock by ordering the involved transactions, selecting a victim based on priority, rollback cost and table modifications, updating transaction weights, optionally logging the deadlock details, and finally waking the victim transaction to roll back.
This article explains how MySQL's InnoDB deadlock detection thread works, when it checks for deadlocks based on the innodb_deadlock_detect variable, how it discovers lock‑wait cycles, performs a secondary verification using snapshot and slot information, and finally summarizes the overall process.
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.
