Understanding MySQL’s SQL Execution Flow and Transaction Mechanics

MySQL Architecture Overview

MySQL consists of a Server layer (connector, query cache, parser, optimizer, executor, built‑in functions) and a pluggable storage‑engine layer (InnoDB, MyISAM, Memory, etc.). InnoDB has been the default engine since MySQL 5.5.5.

Connector

The connector establishes client connections, authenticates users, and loads their privileges. Example command:

root@bac8f643c3e9:/# mysql -h10.10.0.18 -P3306 -uroot -p

Permissions are cached for the session, so changes to privileges take effect only after reconnection.

Query Cache (removed in MySQL 8.0)

The query cache stored SELECT statements and result sets as key‑value pairs. It was disabled in most production workloads because any table update invalidated related cache entries, leading to frequent cache flushes. MySQL 8.0 removed the feature.

Parser

If the cache is missed, the parser performs lexical analysis (tokenizing keywords, tables, columns) followed by syntax analysis (validating SQL syntax). Most parsing errors arise at this stage.

Optimizer

The optimizer selects an execution plan it deems optimal, such as which index to use or the join order for multi‑table queries.

Executor

After the plan is chosen, the executor checks user permissions again, then invokes the storage‑engine interface to retrieve or modify data. Permission checks occur after optimization because some tables (e.g., those accessed via triggers) are only known at execution time.

Update Statement Execution and Logging

UPDATE statements follow the same pipeline as SELECTs but also involve transaction logging: the InnoDB redo log (physical, write‑ahead log) and the server‑level binlog (logical).

Redo Log (WAL)

InnoDB writes changes first to the redo log (the “paper” in a hotel ledger analogy) and later flushes them to disk. The redo log is a fixed‑size circular buffer (e.g., four 1 GB files). Write position advances; when it catches up to the checkpoint, old entries are reclaimed after being persisted to data files. This design provides crash‑safe recovery.

Binlog

Redo log is engine‑specific; binlog is implemented in the Server layer and usable by all engines.

Redo log records physical page modifications; binlog records logical SQL statements.

Redo log is circular; binlog is append‑only, creating new files when size limits are reached.

Step‑by‑Step UPDATE Execution

mysql> update T set c=c+1 where ID=2;

The executor asks the engine to locate the row with ID = 2 (using the primary‑key index). If the page is in memory, it is returned; otherwise it is read from disk.

The executor modifies the column value (e.g., N → N+1) and asks the engine to write the new row.

The engine updates the row in memory and records the change in the redo log (prepare state).

The executor generates a binlog entry and writes it to disk.

The executor commits the transaction; the engine marks the redo log entry as committed, completing the update.

Two‑Phase Commit Reasoning

If redo log is written before binlog and MySQL crashes, the redo log can recover the data, but the binlog lacks the statement, causing inconsistency during replication. Conversely, writing binlog before redo log can leave an unapplied logical entry after a crash, also causing divergence. Therefore MySQL uses a two‑phase commit to ensure both logs are consistent.

Configuration Parameters

innodb_flush_log_at_trx_commit=1

forces each transaction’s redo log to be flushed to disk, guaranteeing crash‑safe durability. sync_binlog=1 forces each binlog write to be flushed, protecting the logical log.

Transaction Properties and Concurrency Issues

Transactions must satisfy ACID: Atomicity, Consistency, Isolation, Durability. Common concurrency anomalies include dirty reads, lost updates, non‑repeatable reads, and phantom reads, each described with examples.

Isolation Levels

READ‑UNCOMMITTED: Allows dirty reads, non‑repeatable reads, phantom reads.

READ‑COMMITTED: Prevents dirty reads; non‑repeatable reads and phantoms may occur.

REPEATABLE‑READ (InnoDB default): Prevents dirty and non‑repeatable reads; phantoms may still appear without locking.

SERIALIZABLE: Highest level; eliminates dirty reads, non‑repeatable reads, and phantoms by executing transactions sequentially.

Commands to view the current level: SELECT @@transaction_isolation; (MySQL 8.0) or SELECT @@tx_isolation; (earlier versions).

Implementation Details

InnoDB implements MVCC by creating a view for each transaction under REPEATABLE‑READ; the view is built at transaction start, while READ‑COMMITTED creates a view per statement. Rollback logs are kept until no active transaction needs them; long‑running transactions retain old versions, consuming storage and lock resources.