MySQL Query Execution, Logs, and Transaction Isolation Explained (Part 1)

1. How a SQL query is executed

MySQL consists of a Server layer and a pluggable storage‑engine layer. The Server layer includes the connector, query cache, parser, optimizer, executor and built‑in functions (date, math, encryption, etc.). The storage‑engine layer handles data storage; InnoDB has been the default engine since MySQL 5.5.5.

Connector

The connector establishes client connections, authenticates users, and loads their privileges. Once authenticated, the connector caches the privilege data for the lifetime of the connection, so later privilege changes do not affect the active session.

Query cache (removed in MySQL 8.0)

The query cache stored the result set of SELECT statements as a key‑value pair in memory. A cache hit returned the result immediately; a miss caused normal execution. Because any table update invalidates all related cache entries, the cache is rarely useful in write‑heavy workloads, and MySQL 8.0 removed it.

Parser

If the cache is missed, the parser analyzes the SQL. It performs lexical analysis (tokenizing keywords, tables, columns, conditions) followed by syntax analysis (checking MySQL grammar). Most syntax errors are caught at this stage.

Optimizer

The optimizer chooses an execution plan it deems optimal—e.g., which index to use or the join order for multiple tables. After this step the concrete plan is fixed.

Executor

Before execution the executor re‑checks the user’s privileges on the target tables. It then invokes the storage‑engine interface to perform the operation. Permission checks occur after optimization because some objects (e.g., triggers) are only known during execution.

2. How a SQL UPDATE statement is executed

Update statements follow the same pipeline but also involve transaction logging: the redo log (InnoDB‑specific) and the binlog (server‑level).

Redo log (WAL)

InnoDB writes changes first to a redo log (the “paper” in a hotel ledger analogy) before modifying the data pages. The redo log is a circular set of fixed‑size files (e.g., four 1 GB files). Write position advances as entries are added; a checkpoint marks the oldest log that can be reclaimed. This two‑phase write (prepare → commit) guarantees crash‑safe recovery.

Binlog

The server writes a logical binlog that records the original SQL statement (e.g., “update T set c=c+1 where ID=2”). Unlike the physical redo log, the binlog is append‑only and can be used for replication and point‑in‑time recovery.

Key differences:

Redo log is InnoDB‑only; binlog is available to all engines.

Redo log records physical page changes; binlog records logical SQL.

Redo log is circular and overwrites old data; binlog grows by appending new files.

Update execution steps

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

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 already in memory it is returned directly; otherwise it is read from disk.

The executor increments column c and asks the engine to write the new row.

The engine updates the row in memory and writes an entry to the redo log in the prepare state.

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

The engine commits the redo log entry (changing it to commit), completing the transaction.

This sequence implements a two‑phase commit. Writing redo log first then binlog, or the reverse, can cause inconsistencies after a crash, which is why the two‑phase protocol is required.

Parameters innodb_flush_log_at_trx_commit=1 and sync_binlog=1 force each redo‑log and binlog write to be flushed to disk, ensuring durability.

3. Transaction concurrency issues and isolation levels

Transactions must satisfy ACID:

Atomicity : all operations succeed or none do.

Consistency : data remains valid before and after.

Isolation : concurrent transactions do not interfere.

Durability : committed changes survive crashes.

Concurrency anomalies

Dirty read – a transaction reads uncommitted changes from another transaction.

Lost update – two transactions modify the same row; the later commit overwrites the earlier one.

Non‑repeatable read – a row read twice in the same transaction returns different values because another transaction modified it.

Phantom read – new rows appear (or disappear) in the result set of a repeated query.

Isolation levels (SQL standard)

READ‑UNCOMMITTED – allows all anomalies.

READ‑COMMITTED – prevents dirty reads but allows non‑repeatable and phantom reads.

REPEATABLE‑READ – prevents dirty and non‑repeatable reads; phantom reads may still occur unless next‑key locks are used.

SERIALIZABLE – highest level; eliminates all three anomalies by executing transactions sequentially.

InnoDB’s default is REPEATABLE‑READ, while Oracle and SQL Server default to READ‑COMMITTED. The isolation level can be queried with SELECT @@transaction_isolation; (MySQL 8.0) or SELECT @@tx_isolation; (earlier versions).

Implementation details

InnoDB uses MVCC: each row version is stored with a rollback record, and a view is created at transaction start (for REPEATABLE‑READ) or at each statement (for READ‑COMMITTED). Long‑running transactions keep old versions alive, consuming storage and lock resources, so they should be avoided.