This article explains how InnoDB simulates deadlocks, how the ib_srv_lock_to background thread checks for deadlocks, how it builds lock‑wait snapshots, constructs lock‑wait graphs, calculates transaction weights, and finally resolves deadlocks, illustrated with detailed SQL examples and code snippets.
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.
This article analyzes why two identical DELETE statements on a MySQL table with a unique index can deadlock, explains InnoDB lock types, walks through the execution flow, reproduces the issue with SQL and Java code, and discusses mitigation strategies.
This article explains the inner workings of InnoDB’s lock‑wait timeout thread, detailing how it scans waiting slots, identifies timed‑out transactions, processes timeout logic, and notifies the affected transactions, while also covering related data structures and configuration variables.
This article explains how MySQL Router 8.2 enables read/write splitting for an InnoDB ReplicaSet, showing configuration steps, routing behavior, sample code, and the resulting performance and management benefits without requiring application changes.
This article explains how two identical DELETE statements on a table with a unique index can cause a deadlock in MySQL 5.7, reviews InnoDB lock types, reproduces the issue with SQL and Java code, and discusses mitigation strategies and behavior changes in MySQL 8.
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.
This article explains how InnoDB handles lock waiting by allocating lock structures, registering waiting slots, recording start times, and notifying transactions, outlining each step of the queue‑registration‑notification workflow in MySQL 8.0.32.
This article investigates whether MySQL re‑executes an UPDATE statement that sets a column to its current value, comparing behavior under binlog_format=ROW and binlog_format=STATEMENT with binlog_row_image=FULL, and presents test steps, results, and conclusions.
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.
This article explains how InnoDB implements row locking by dividing the process into fast and slow paths, describing the hash‑based lock table, token acquisition, lock‑structure creation, bitmap handling, and the conditions that trigger slow‑lock logic.
This article examines the practical limits of MySQL single‑table row counts, explains how InnoDB stores data in 16 KB pages, analyzes B+‑tree index structures, and provides formulas and experiments that justify the commonly cited 20 million‑row guideline.
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.
This article explains InnoDB lock concepts, differentiates read and write locks, discusses table‑level versus row‑level locking, and examines how MySQL 5.6 and 8.0 handle adding columns without fully locking tables, highlighting online DDL, atomic DDL, and performance considerations.
This article explains how InnoDB determines whether a transaction already holds a table lock, acquires a mutex token, checks for blocking locks, and creates or reuses lock structures, detailing the complete table‑lock acquisition workflow in MySQL 8.0.32.
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.
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.
This article explains the different types of InnoDB row locks—including shared, exclusive, gap, next‑key, and insert‑intention locks—by showing preparation steps, SQL examples, lock‑mode details, and how they interact under repeatable‑read isolation in MySQL 8.0.32.
This article presents a MySQL "table is full" error case, explains how the AI‑powered assistant ChatDBA analyzes possible causes such as insufficient temporary tablespace, heavy temporary table creation, and disk limits, and provides step‑by‑step configuration adjustments to resolve the issue.
This article explains the architecture of MySQL's InnoDB storage engine locks, covering server‑level metadata locks, table‑level shared and exclusive locks, intent locks, and the three modes of AUTO‑INC locks, with practical SQL examples and performance‑schema queries.
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.
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.
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.
The article explains the release of MySQL 8.4 as the first LTS version, compares LTS and Innovation release tracks, and details the 20 InnoDB system variables whose default values have been changed to better suit modern production environments, including performance‑related settings and hardware‑aware defaults.
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.
This article explains where MySQL stores auto‑increment values, why gaps appear after failed inserts, duplicate‑key errors, rollbacks or batch inserts, and how different innodb_autoinc_lock_mode settings and MySQL 8.0 improvements can mitigate the issue.
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.
This article explains how MySQL 8.0.32 rolls back a transaction by first attempting a binlog rollback (which does nothing for a plain ROLLBACK), then performing InnoDB undo‑log processing, committing the internal InnoDB changes, and finally clearing temporary binlog data from the transaction cache.
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.
This article explains how MySQL 8.0.32 InnoDB rolls back to a specific savepoint by locating the savepoint in the thread's m_savepoints list, discarding relevant binlog entries, applying undo logs in reverse order, and finally removing any later savepoints, with full code examples.
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.
This article explains how InnoDB implements savepoints in MySQL 8.0.32, covering undo log sequencing, the savepoint structures in the server, binlog, and InnoDB layers, the process of locating and deleting duplicate savepoints, and the three-step procedure for creating a new savepoint.
This article investigates a MySQL 8.0.27 replica that repeatedly hangs during show replica status, analyzes worker thread states, InnoDB status, checkpoint and I/O load, identifies an oversized Redo Log usage as the root cause, and provides configuration and upgrade suggestions to resolve the issue.
The article explains MySQL 8.0's INSTANT DDL algorithm, its default behavior, limitations such as the 64‑change cap, and provides practical recommendations for always specifying the algorithm, monitoring instant changes via INFORMATION_SCHEMA, and safely rebuilding tables when needed.
This article explains how MySQL auto‑increment primary keys work, the practical limits of various internal IDs such as auto‑increment, InnoDB row_id, Xid, trx_id, and thread_id, and compares them with external solutions like Redis‑based auto‑increment keys, providing guidance on choosing the right approach for different scenarios.
This article explains the internal steps InnoDB performs when committing a transaction, covering undo segment caching, generation of the transaction commit number, state changes of insert and update undo segments, history‑list insertion, and the final reinitialisation of the transaction object.
This comprehensive guide explains why MySQL needs InnoDB locking, details the seven lock types, shows how a single SQL statement acquires locks under different isolation levels, demonstrates how to view lock information, and walks through practical deadlock analysis examples.
This article explains how MySQL 8.0.32 processes the flush, sync, and commit subphases during a two‑phase commit, detailing the roles of leader and follower threads, log handling, system variables, and the conditions that trigger binlog rotation and persistence.
This article explains why MySQL uses locks, categorizes lock types from global to row level, details their implementation and commands, and shows how intention, metadata, and auto‑increment locks work while offering strategies to prevent deadlocks and lock contention.
This article explains MySQL 8.0.32 InnoDB's two‑phase commit process, detailing the flush, sync, and commit sub‑stages, the concept of group commit, and how queues and mutexes coordinate transaction logging to reduce redundant disk writes.
The article demonstrates that adding an explicit primary key to a MySQL InnoDB table can dramatically reduce its on‑disk size by eliminating the hidden clustered index, and explains how invisible primary keys can be used when schema changes are not possible.
This article examines the technical reasons why MySQL single tables struggle with massive data volumes, debunks the index‑depth myth, explains InnoDB's SMO concurrency bottlenecks, and shows how B‑Link Tree indexes and heap‑organized tables in GaussDB provide superior performance for large‑scale workloads.
This article explains how InnoDB stores data pages and builds B+‑tree indexes, illustrates the structure of clustered, secondary and composite indexes, and enumerates typical scenarios—such as left‑most prefix violations, LIKE patterns, and function use—that cause index loss and full‑table scans.
This article explains how MySQL 8.0.32 InnoDB generates binlog events, the role of binlog cache (stmt_cache and trx_cache), the two‑level cache mechanism, and the detailed process of writing events to the cache and flushing them to disk.
This article explains the space requirements of MySQL Online DDL operations, analyzes temporary log, sort, and intermediate files, provides step‑by‑step test preparation, demonstrates failure and recovery scenarios, and summarizes best practices for avoiding space‑related errors.
This article explains MySQL deadlocks, describing how they occur in InnoDB due to resource contention, outlines common causes such as race conditions, poor indexing, long transactions, lock upgrades, and provides practical prevention strategies including deadlock detection, short transactions, query optimization, lock timeouts, monitoring tools, lock ordering, and appropriate lock levels.
This guide explains what MySQL deadlocks are, shows how to use InnoDB status and performance‑schema queries to locate them, demonstrates a reproducible deadlock scenario, and provides practical prevention techniques such as reducing transaction size, quick commits, proper indexing, and consistent access order.
The article demonstrates, through a concrete MySQL example, how adding a seemingly small secondary index can unexpectedly inflate a table’s on‑disk size by up to 80%, explains the underlying InnoDB storage mechanics, and offers practical schema‑design recommendations to mitigate such growth.
This article explains the various read and write anomalies that can occur during concurrent database transactions—such as dirty reads, nonrepeatable reads, phantom reads, lost updates, dirty writes, and write skew—and outlines isolation levels and practical techniques like MVCC, atomic updates, explicit locking, and compare‑and‑set to prevent them.
This article explains how MySQL InnoDB handles update, delete, and select statements within read and read‑only transactions, detailing when a read transaction becomes read‑write, the allocation of transaction IDs, rollback segments, and the role of lock types for ordinary and temporary tables.
This article examines how changing the InnoDB parameters innodb_flush_method and innodb_use_fdatasync—especially using O_DIRECT—affects MySQL's interaction with Linux syscalls, demonstrates verification with strace and /proc, and shows performance implications of these low‑level I/O choices.
This article consolidates theory and real‑world examples to show how MySQL’s B+‑tree structure, page layout, and row format determine the maximum number of records a single table can store, providing step‑by‑step calculations for both int and bigint primary keys.
This article explains how InnoDB determines transaction types based on the first SQL statement, describes the internal state changes when a transaction starts, and details the identification, ID allocation, and handling of read, read‑only, read‑write, and internal transactions in MySQL 8.0.32.
This article explains why slow SQL queries occur, how MySQL indexes dramatically improve query speed, and details the underlying data structures—binary tree, hash, B‑Tree, B+Tree—as well as storage engine differences and composite index rules for optimal performance.
This article explains how InnoDB in MySQL 8.0.32 distinguishes user and internal transactions, details the allocation of transaction objects from transaction pools, describes initialization steps, and shows how transaction objects are linked into appropriate transaction lists.
The article explains why counting rows in large MySQL tables—especially with InnoDB—can become slow, compares COUNT() performance across storage engines and query forms, and offers optimization tactics such as using metadata counts, EXPLAIN estimates, a dedicated counting table, or periodic batch processing for accurate or approximate results.
This article examines the many syntactic forms of MySQL's BEGIN statement, explains which variations are valid or cause syntax errors, and details how BEGIN commits any existing transaction and prepares a new one without immediately starting it, all based on MySQL 8.0.32 InnoDB source code.
This article explains what MySQL general tablespaces are, outlines their main features and benefits, provides step‑by‑step commands for creating, managing, and assigning tables to them, and shows monitoring queries and practical usage scenarios for improved storage efficiency and performance.
This article explains how InnoDB in MySQL 8.0.32 manages transaction objects through a transaction pool and a single pool manager, detailing pool creation, initialization of a limited number of objects, and the on‑demand expansion strategy to improve performance under high concurrency.
This article explains the complete execution process of a MySQL UPDATE statement, covering InnoDB page structures, tablespaces, buffer pool caching, redo/undo logs, binlog, and the two‑phase commit mechanism, helping readers understand how MySQL ensures data consistency and durability.
This article analyzes the theoretical and practical limits of a single MySQL InnoDB table by examining B+‑tree depth, page layout, row format, and index pointer size, then calculates minimum and maximum record counts for int and bigint primary keys and validates the results with real‑world table schemas.
This article explains the various lock types in MySQL—including global, table, row, metadata, and intent locks—how they work under InnoDB, demonstrates lock acquisition with SQL examples, and provides practical guidance for detecting and resolving deadlocks in production environments.
The article investigates whether the long‑standing rule of avoiding MySQL tables larger than 20 million rows still holds on modern SSD‑backed instances by creating tables of varying sizes, measuring query latency under different InnoDB buffer pool configurations, and analyzing the impact of caching and index usage.
This article explains the various lock types in MySQL's InnoDB engine, how they are applied to different SQL statements, and the impact on concurrency, isolation levels, and transaction behavior.
This article explains MySQL InnoDB's lock mechanisms and index structures, covering primary and secondary indexes, record, gap, and next‑key locks, and how they affect query performance and transaction isolation in high‑concurrency environments.
This article explains MySQL's fault‑tolerance mechanisms, covering data persistence, storage engines, the binary, redo, and undo logs, WAL, two‑phase commit, and step‑by‑step recovery procedures for accidental data loss or crashes.
This article explains MySQL InnoDB locking mechanisms, detailing the twelve lock types, their granularity, compatibility rules, how to view lock information, and how different SQL statements acquire locks under various isolation levels, with practical test cases and code examples.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—by creating a simple table, inserting test data, and running step‑by‑step SQL scripts that illustrate the behavior of each level, including phenomena such as dirty reads, non‑repeatable reads, phantom reads, and locking.
This article explains MySQL transaction fundamentals, the necessity of ACID properties, details the four isolation levels and their impact on phenomena like dirty reads, non‑repeatable reads, and phantom reads, and provides practical SQL examples and strategies for ensuring data consistency in high‑concurrency environments.
This article explains how the update_time column in the information_schema.tables view is populated, why its values can be unreliable, and details the mechanisms of active and passive persistence of table statistics in MySQL 8.0, including source‑code walkthroughs.
This article explains the fundamentals of MySQL locking—including explicit and implicit locks, transaction isolation levels, next‑key and gap locks, and practical session‑based examples—so developers can decide when to apply pessimistic locks and when to prefer safer alternatives.
This article explains what a MySQL deadlock is, shows a concrete example using two conflicting transactions on a simple test table, demonstrates how to detect the deadlock with InnoDB status output, and provides practical tips for preventing deadlocks in production systems.
This article examines MySQL's comprehensive lock taxonomy, starting from common row and table locks, then delving into lesser‑known mechanisms such as global locks, intention locks, auto‑increment locks, and Latch, while explaining their commands, use‑cases, and performance implications for developers and DBAs.
This article explains MySQL InnoDB's concurrency control mechanisms—including read‑write locks, transaction isolation levels, MVCC, redo/undo logs, and rollback segments—and shows how they work together to ensure data consistency while maximizing read/write performance in high‑traffic environments.
This article explains how to tune MySQL's InnoDB settings—including buffer pool size, log file configuration, I/O threads, flush behavior, and timeout values—by providing detailed explanations and exact configuration snippets to improve performance and resource usage.
The article explains MySQL’s explicit and implicit lock mechanisms—including SELECT … FOR UPDATE, metadata, row, gap, and next‑key locks—how isolation levels affect them, and offers practical guidelines such as using optimistic locking, indexing locked columns, and avoiding long transactions to prevent phantom reads, deadlocks, and performance issues.
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.
This article explains MySQL InnoDB's various buffers—including the buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their purposes, internal mechanisms such as LRU variants, configuration options, and how they improve performance and ensure data integrity.
This article explains MySQL's various lock mechanisms—including shared (S) and exclusive (X) locks, intention locks, record, gap, next‑key, and insert‑intention locks—illustrates their behavior with SQL examples, analyzes a concurrent insert deadlock scenario, and offers practical mitigation strategies.
This article explains MySQL InnoDB’s key buffering mechanisms—buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their roles, internal structures, eviction policies, and how they improve performance and data durability while handling page reads, writes, and crash recovery.
This article explains MySQL InnoDB's Multi-Version Concurrency Control (MVCC), covering its basic concepts, read/write scenarios, implicit fields, undo logs, read view mechanisms, transaction handling, and differences between RC and RR isolation levels, providing a comprehensive guide for developers.
This article provides a step‑by‑step walkthrough of MySQL’s execution pipeline—from the client connection and parsing, through optimization, execution, undo/redo logging, change buffer handling, and binlog recording—illustrated with diagrams and detailed explanations of each component.
This guide explains MySQL's lock mechanisms—including shared and exclusive locks, row‑level locking requirements, pessimistic versus optimistic strategies, and practical implementations with InnoDB, transaction handling, and JMeter concurrency testing—to prevent overselling in high‑traffic scenarios.
Starting with MySQL 8.0.30, the Generated Invisible Primary Keys (GIPKs) feature automatically adds an invisible bigint auto‑increment column named my_row_id when a table lacks an explicit primary key, simplifying schema design, easing DBA‑developer conflicts, and offering flexible handling in MGR and replication environments, while noting its limitations and usage commands.
This article analyzes the differences between MySQL's full table scan and full primary‑key scan by examining execution plans, source‑code paths, and runtime behavior, demonstrating that both ultimately read data from the primary‑key index and explaining why the optimizer distinguishes them.
This article walks through how a Java application interacts with MySQL, covering the driver, connection pooling, thread handling, query parsing, the optimizer, storage engine, buffer pool, undo/redo logs, binlog, and transaction commit, providing a complete end‑to‑end view of MySQL’s internal processing.
This article breaks down MySQL's complex locking mechanisms—global, table, and row locks—explaining how different SELECT statements, transaction isolation levels, and index types affect lock acquisition, and provides practical test scripts and visual examples to help developers master lock behavior.
This article examines how InnoDB processes UPDATE statements on a table with primary and secondary indexes, analyzes three scenarios to determine which index blocks are modified, explains the internal filtering steps, and validates the behavior by inspecting LSN changes of the affected blocks.
This article explains how MySQL 8 improves the auto‑increment counter by persisting its maximum value across server restarts, contrasting the behavior with MySQL 5.7 through detailed examples and SQL commands that demonstrate the differences in primary‑key generation.
The article examines a MySQL InnoDB deadlock that occurs when concurrent inventory‑write transactions in a pre‑release environment contend for row and gap locks, explains InnoDB lock types, isolation‑level effects, and proposes solutions such as distributed locks and isolation level adjustments.
This article explains why MySQL needs locking, details every InnoDB lock type—including shared, exclusive, intention, record, gap, next‑key, insert‑intention and auto‑increment locks—covers lock compatibility, deadlock causes and prevention, compares optimistic and pessimistic locking, and shows how SELECT FOR UPDATE behaves under different isolation levels.
This article explains MySQL's index prefix length restrictions for BLOB/TEXT columns in versions 8.0 and 5.7, demonstrates verification experiments showing the 767‑byte and 3072‑byte limits for different InnoDB row formats, and discusses the underlying design implications.
This article explains MySQL InnoDB's multi-version concurrency control (MVCC), detailing how transaction isolation levels such as read‑committed and repeatable‑read create snapshots, how undo logs and hidden columns form version chains, and walks through concrete examples to show which row values are visible to concurrent transactions.
This article examines how INSERT, REPLACE and INSERT … ON DUPLICATE KEY UPDATE statements can cause deadlocks in MySQL InnoDB under various scenarios, explains the underlying lock types such as GAP, NEXT‑KEY and INSERT‑INTENTION locks, and offers practical recommendations to avoid them.
This article explains how an UPDATE without indexed WHERE conditions can trigger full‑table next‑key locks in InnoDB, causing business downtime, and provides practical safeguards such as enabling sql_safe_updates and using FORCE INDEX to ensure safe execution.
This article explains MySQL transaction fundamentals, the ACID properties, how to start and control transactions, the problems of dirty, non‑repeatable and phantom reads, isolation level settings, MVCC mechanics, read‑view structures, and the role of undo logs in ensuring data consistency.
This article describes how to identify, analyze, and fix InnoDB page corruption in MySQL instances, covering log inspection, querying metadata tables, using innodb_force_recovery, and the inno_space tool, with example commands and best‑practice recommendations to prevent data loss.
This article explains how MySQL handles overflow of auto_increment, InnoDB row_id, Xid, trx_id, and thread_id, showing the resulting primary‑key errors, data overwrites, rare Xid collisions, a potential dirty‑read bug, and recommended safeguards such as using BIGINT.
This article explains the purpose, classification, and implementation of MySQL locks—including global, table, page, row, intention, and gap/record/next-key locks—as well as optimistic and pessimistic locking strategies, providing code examples and usage scenarios for ensuring data consistency in concurrent transactions.
This article explains why MySQL, the world’s most popular open‑source relational database, is a fundamental skill for developers, operators and testers, and outlines a two‑book learning path covering installation, SQL mastery, InnoDB internals, indexing and performance optimization.
