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.
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.
