Understanding MySQL Binlog, Redo Log, and Two‑Phase Commit for Event‑Driven Order Processing

Introduction

MySQL Binlog records structured query language (SQL) statements that modify the database. It is a binary log that can be inspected with the mysqlbinlog command. iQIYI uses MySQL Binlog in its membership order system to implement an event‑driven architecture, simplifying design while improving availability and data consistency.

The article explains the technical principles of MySQL Binlog from a practical perspective, combines theory with practice, and points out potential design pitfalls.

Background

Binlog is crucial for MySQL master‑slave replication, cross‑database synchronization, and event‑driven business designs. However, using Binlog for event‑driven design is not as simple as it appears. The article reviews Binlog, Redo Log, and the internal data‑update process, analyzes possible business‑level issues, and suggests mitigation strategies.

Binlog‑Based Event‑Driven Order Design

The membership order system sends messages directly to a message queue (MQ) after a database update, driving subsequent business processes. The diagram (Fig.1) shows this direct‑message approach. This design requires strong consistency between database writes and message sends; otherwise, failures can cause data‑message mismatches.

Identified drawbacks include:

Business system’s direct dependency on the message middleware.

Need for reliable retry mechanisms on both sender and receiver sides.

Exponential back‑off retry intervals causing business latency.

To simplify the architecture, the system adopts an event‑table approach: the order table itself becomes an event table. By subscribing to the order table’s Binlog, order events are generated without the business system directly interacting with the message middleware.

Figure 2 illustrates the Binlog‑based order event‑driven flow.

Hidden Issues

Despite the advantages, occasional order fulfillment delays were observed. In normal flow, the fulfillment service checks the order status after receiving a payment event; if the status is “paid”, fulfillment proceeds. However, some delayed orders were found to be in “unpaid” state when queried from the primary database, even though the Binlog indicated a payment event. Concurrency issues and master‑slave lag were ruled out.

The root cause is linked to MySQL’s internal update mechanism, which is explained next.

MySQL Data‑Update Principles

The article introduces two essential logs:

Redo Log : a physical log at the InnoDB storage‑engine layer, recording page‑level changes for crash‑safe recovery and performance.

Binlog : a logical log at the MySQL server layer, recording SQL statements or row changes for replication.

A comparison table (omitted for brevity) highlights differences in type, storage format, purpose, hierarchy, and write method.

Because both logs exist, MySQL employs a two‑phase commit to keep them consistent.

Two‑Phase Commit

During transaction commit, InnoDB writes a Prepare record to the Redo Log, then the server writes the Binlog entry, and finally InnoDB writes a Commit record. If a crash occurs after the Binlog is written but before the Redo Log Commit, MySQL recovers by checking the XID in the Binlog to decide whether to commit or roll back.

MySQL Update Flow Example

The article walks through a simple UPDATE t SET n = n + 1 WHERE id = 2 statement:

The executor fetches the row with ID=2 from the storage engine.

The executor modifies the value and asks the engine to write the new row.

The engine updates the in‑memory page and records the change in the Redo Log buffer.

On commit, the Redo Log is marked Prepare and flushed to disk.

The executor generates the Binlog entry and writes it to disk.

The engine finalizes the Redo Log as Commit.

Figure 3 visualizes the interaction between the executor, InnoDB, Redo Log, and Binlog.

Problem Analysis

Because Binlog is written before the storage engine fully commits the transaction, downstream systems that consume Binlog may see events earlier than the primary database reflects the committed data. This explains why the fulfillment service sometimes reads an “unpaid” status after receiving a payment event.

Solutions

Two main approaches are proposed:

Retry : After receiving a Binlog event, retry the database query after a short delay. Simple thread‑sleep or message re‑delivery mechanisms can be used, though excessive retries may affect service stability.

Direct Binlog Consumption : Use the row‑format Binlog, which contains the full data change, as the source of truth for business processing. This reduces database load but may require significant redesign for existing systems.

Additionally, the article warns about the ABA problem (state reverting from A→B→A) and suggests using state machines to mitigate it.

Recruitment Notice

At the end of the article, iQIYI’s membership development team advertises a senior Java engineer position, inviting interested candidates to send their resumes to [email protected] .