How MySQL Guarantees ACID: Deep Dive into Undo, Redo, and Binlog

MySQL is a widely used open‑source relational database whose reliability depends on a layered architecture and robust logging mechanisms that enforce ACID properties.

MySQL Architecture Layers

Connection Layer : authenticates client connections and manages a connection pool for reuse.

Service Layer : parses, optimizes, and executes SQL statements; implements built‑in functions and (pre‑MySQL 8.0) query cache.

Storage Engine Layer : provides pluggable storage engines that interact directly with data files and logs.

File System Layer : stores log files, data files, and auxiliary MySQL programs.

Server Service Layer Functions

Query Parsing and Optimization

Syntax Parsing : converts client SQL into an internal structure and validates syntax.

Query Rewrite : transforms queries into semantically equivalent, more efficient forms.

Query Optimization : the optimizer selects the best execution plan based on statistics, join order, and index usage.

Query Cache

MySQL can cache the results of identical queries, but the cache is usually disabled in production because it requires the exact same SQL text, schema, protocol version, and character set. The query cache was removed in MySQL 8.0.

SQL Execution

After parsing and optimization, the service layer hands the execution plan to the storage engine, which performs the actual data operations.

Core Components of the Service Layer

Parser

Lexical Analysis : tokenizes the SQL statement into identifiers, literals, operators, etc.

Syntax Analysis : builds a parse tree or abstract syntax tree (AST) and validates grammatical correctness.

Semantic Analysis : checks object existence, permissions, and type compatibility.

Optimizer

Logical Optimization

Eliminate redundant sub‑queries.

Rewrite OR conditions to UNION.

Merge multiple queries.

Remove unnecessary ORDER BY or DISTINCT.

Physical Optimization

Choose join method (Nested Loop, Hash, Sort‑Merge).

Select appropriate indexes.

Determine optimal sorting strategy.

The optimizer uses a cost‑based model to evaluate each plan and picks the one with the lowest estimated resource consumption.

Executor

Permission Check

Before execution, the executor verifies that the user has the required privileges.

Execution Plan Execution

Table scan or index scan based on predicates.

Join operations according to the chosen join algorithm.

ORDER BY, GROUP BY, and aggregation processing.

Return result set or apply data modifications.

Transaction Management

For INSERT/UPDATE/DELETE statements, the executor coordinates with Undo Log, Redo Log, and Binlog to ensure atomicity, consistency, isolation, and durability.

MySQL Query Execution Process

Client connects via the connector, authenticates, and establishes a session.

Query cache check (disabled in MySQL 8.0+).

Parser converts SQL to an internal representation; the pre‑processor validates the parse tree.

Optimizer generates multiple execution plans and selects the cheapest one.

Executor runs the chosen plan, invoking storage‑engine APIs.

If the query result was cached, it is returned directly; otherwise the result is sent to the client.

Update Statement Execution Process

Connector establishes a session and authenticates the user.

Parser performs lexical, syntax, and semantic analysis.

Optimizer creates the optimal execution plan.

Executor fetches the target row via the primary‑key index (from Buffer Pool or disk).

Undo Log records the original row version for possible rollback.

Executor updates the row, marks the page as dirty, and writes the new version to the Redo Log Buffer.

Binlog cache records the update operation; on commit the Binlog is flushed to disk.

Transaction is committed; Redo Log is marked as committed, and dirty pages are eventually flushed (double‑write buffer ensures durability).

Undo Log, Redo Log, and Binlog – Enabling ACID

ACID Overview

Atomicity : all operations in a transaction succeed or none do.

Consistency : transactions move the database from one valid state to another.

Isolation : concurrent transactions do not interfere with each other.

Durability : once committed, changes survive crashes.

Undo Log (Rollback Log)

Undo Log stores the original row version before modification, enabling rollback and supporting MVCC. Each entry records the inverse operation (e.g., INSERT → DELETE). The logs form a version chain linked by trx_id and roll_pointer. Undo pages reside in the Buffer Pool for fast access and are reclaimed by the purge thread after all snapshot reads finish.

Redo Log (Write‑Ahead Log)

Redo Log records the new values of modified rows, ensuring durability. Changes are first written to the Redo Log Buffer, then flushed to the Redo Log file on transaction commit.

Redo Log Execution Process

Original data is read into the Buffer Pool and marked as a dirty page.

Modified values are appended to the Redo Log Buffer.

On commit, the buffer is flushed to the Redo Log file (append‑only).

Dirty pages are later flushed back to the data files.

If a crash occurs before commit, the transaction is rolled back using Undo Log; if it occurs after commit, Redo Log restores the changes.

Redo Log File Write Mechanism

Redo Log files are fixed‑size circular buffers. Two pointers manage the process:

write pos : current write location.

checkpoint : earliest location that can be overwritten after its data has been persisted.

Redo Log Flush Strategies (innodb_flush_log_at_trx_commit)

0 : Flushes the log buffer every second; highest performance, but up to 1 second of data may be lost on crash.

1 (default): Flushes to disk on every commit; strongest durability, lowest performance.

2 : Writes to OS cache on commit, fsync performed by the OS; a balance between safety and speed.

Binlog (Binary Log)

Binlog records every statement that changes database state, enabling point‑in‑time recovery and primary‑to‑replica replication.

Binlog Flush Timing

During a transaction, changes are first written to a per‑thread Binlog Cache. On commit, the entire cache is flushed atomically to the Binlog file. The sync_binlog variable controls how often the Binlog is synchronized to disk: 0: Write only; OS decides when to flush. 1: Write and immediate fsync (strongest durability). N>1: Write each commit, fsync after N commits (trade‑off).

Summary

The three MySQL logging mechanisms—Undo Log, Redo Log, and Binlog—work together to guarantee the ACID properties of transactions. Understanding their internal workflows, configuration parameters, and interaction points enables developers and DBAs to tune performance, ensure data safety, and design robust, high‑availability database architectures.