When it comes to MySQL affairs, I believe that students who know about MySQL will be able to talk about a few words, whether it is job interview, or daily development, MySQL affairs are closely related to us.

ACID (Atomicity, Consistency, Isolation, persistence) covers everything about transactions, so they need to know not only what ACID is, but also the implementation behind it. No matter in daily development or job interview, can have no disadvantage.

The last article with the interviewer about half an hour of MySQL transaction isolation, from the basic concept in-depth to the implementation of the main focus on “isolation”, from the basic concept, to the implementation of isolation, finally with a practical case to be integrated through. This article will introduce atomicity, consistency, and persistence implementations, which may be unfamiliar to most people because they are not familiar with everyday business development, but will give you a better understanding of MySQL.

1. Basic concepts

  • Atomicity.

The entire transaction is an indivisible minimum unit. If any statement in the transaction fails to execute, all statements that have been successfully executed will be rolled back, and the entire database state will be restored to the state before the transaction.

  • Consistency.

Transactions move the database from one state to the next consistent state. The database integrity constraints are not broken before and after a transaction. (The acid of a transaction is not completely orthogonal. In particular, consistency may be related to atomicity and isolation, as we will see later.)

  • Persistence.

Once committed, transactions are permanent and cannot be lost due to failures such as downtime (external forces such as disk damage are not guaranteed). Persistence ensures High Reliability, not Hign Availability, of a database. High availability is not guaranteed by transactions.

2. Implementation of persistence

MySQL’s innoDB storage engine uses Redo logs to ensure transaction persistence.

When a transaction is submitted, all transaction logs must be written into a log file for persistence. This mechanism is commonly known as Write Ahead Log (WAL). WAL is a key technology to ensure persistence and plays an important role in HBase. This capability ensures that committed transactions will not be lost in the event of power failure or outage. This capability is called crash-safe.

Let’s take a closer look at the redo log mechanism to give you a better understanding.

The Redo log consists of a Redo log buffer, a volatile cache, and a Redo log file, a persistent file.

Take an example of a transaction:

  • Step 1: begin;
  • Step 2: Insert into T1… r
  • Step 3: Insert into T2…
  • Step 4: commit;

The writing process of this transaction is actually unwrapped as follows:

The concept of InnoDB buffer pool is not explained in this article, but can be expanded later.

Focus on splitting redo log writes into two steps, prepare and commit, before the transaction commits.

Why two-phase commit?

In fact, two-phase commit is a common mechanism for distributed systems. MySQL uses two-phase commit to ensure transaction persistence. Redo log and Bingo have a common data field called XID. During crash recovery, Redo log is scanned sequentially.

  • If the system crashes before writing to the redo log, the database scans the redo log in sequence. If the redo log is parepare but not commit, the database uses the XID to find the corresponding transaction in the redo log. So the transaction just rolls back.
  • If the database crashes before the transaction is committed, the database scans the redo log and commits the database to a prepared and commit redo log.

This transaction inserts records into the redo log buffer before writing the logs to the redo log file at commit time. (Of course, this is not absolute, because the redo log buffer may be forced to flush to the redo log for other reasons).

In order to ensure that every log is written to the log file, InnoDB storage engine needs to call fsync once every time the redo log buffer is written to the redo log file to ensure that it is written to disk.

For redo log persistence, you can see the following figure.

1) Redo log buffer is written in blue.

2) Write redo log file, but fsync has not been done yet. It is in yellow and in the system cache.

3) Call fsync to actually write to disk.

To control the redo log write policy, InnoDB provides the innodb_flush_log_at_trx_commit parameter, which has three possible values:

  • If the value is set to 0, only the redo log is left in the redo log buffer for each transaction committed.
  • If the value is set to 1, the redo log is persisted to disk during each transaction.
  • If set to 2, only the redo log is written to the page cache each time a transaction commits.

Binlog writes include bingo cache and bingo file as redo logs do. Binlog is a server file, not a storage engine file. Includes the log buffer, file system Page cache, and hard disk.

The timing of writing to the page cache and fsync to disk is controlled by the sync_binlog parameter:

  • If sync_binlog=0, each committed transaction is written to the page cache of the file system instead of fsync.
  • If sync_binlog=1, fsync will be performed every time a transaction is committed.
  • When sync_binlog=N(N>1), it indicates that each committed transaction is written to the page cache of the file system, but fsync is performed after N transactions are accumulated. (If the host restarts abnormally, binlog logs of the latest N transactions will be lost.)

Sync_binlog and innodb_flush_log_at_trx_COMMIT are both set to 1. In other words, a transaction must wait twice for a redo log and a binlog before it is fully committed.

In particular, there is a distinction between redo logs and binlogs. This is the difference between the two types of journals that are often asked in an interview.

Note the following points:

  • They’re generated in different places.

Redo logs are generated by InnoDB’s storage engine, and binlogs are implemented by the database server layer. In other words, if you use MySQL and use another storage engine, you may not have redo logs, but you still have binlogs.

  • Logs are recorded in different forms.

The binlog is a logical log that records SQL statements, and the redo log is a physical log that records changes made to each data page.

  • Logs are written to disks at different times.

A binlog only writes once after a transaction commits, and for an item, there is only one record in the binlog. Redo logs are written concurrently, not sequentially, throughout a transaction.

  • The storage methods are different.

Redo logs are written in a redo log cycle. Binlog can be appended. “Appending” means that the binlog file will be switched to the next one after being written to a certain size without overwriting the previous log.

3. Implementation of atomicity

Undo log ensures atomicity of transactions.

InnoDB generates undo logs as well as redo logs during database changes. InnoDB implements rollback by using undo log: When a transaction changes the database, InnoDB generates the corresponding Undo log. If the transaction fails and needs to be rolled back, the data is rolled back to where it was before the modification using the information in the Undo log.

Some people think that undo log is the reverse of redo log, but it is not true. Both log files can be considered as data recovery operations. The redo log restores the data page changes caused by transactions, while the Undo log restores the data records to a specific version.

So the redo log is a physical log (changes to data pages), and the undo log is a logical log (data logging).

Undo log but also another important role, it is used in MVCC, for version control, which is the foundation of realization of transaction isolation, when users read a row, if the record has been occupied by other transactions, then the current affairs before it can be read by undo line version information, used to implement a read lock, Snapshot read. In my last article, I talked to the interviewer about transaction isolation for half an hour, from the basic concept to the implementation.

4. Implementation of consistency

As explained at the beginning of the definition, the ACID properties of a transaction are not completely orthogonal, especially for consistency, and we can think of atomicity, persistence, and isolation as all for consistency.

Of course, consistency here refers to transaction consistency at the database level.

If you do an operation at the application level that deducts money from the sender and does not add money to the receiver, then this inconsistency has nothing to do with the inconsistency of the transaction and requires the developer to ensure the consistency of the business logic.


This article is very difficult to write, some knowledge may not be particularly commonly used, do not say there may be doubts, but it is easy to get into details, and repeatedly revised several times, hoping to logical coherence, simple, put an end to smelly and long.

See the end, the original is not easy, point a concern, point a like it ~

Reorganize the knowledge fragments to construct the Java knowledge graph:
Github.com/saigu/JavaK…(Easy access to historical articles)