Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

In our daily work or interview, we often talk about how data can be lost even if INNODB transactions are committed, so why is this?

Sync_binlog = 1 && innodb_flush_log_at_trx_commit = 1. Today we’re going to talk about why the two-in-one principle keeps data from being lost.

  • Take a look at the redo log flush steps:

    1. When a transaction commits, data is first written to the Log Buffer, which calls MySQL’s own WriteRedoLog.
    2. Then, when MySQL makes a system call to write, the logs in the Log Buffer are written to the OS Cache.
    3. Note that when the MySQL system calls write, the file is considered written. If not flushed, it is up to the operating system to decide when to flush.
    4. Finally, the operating system flushes the data from the OS Cache to disk.

  • Also, take a look at the steps for binlog to flush to disk:

    1. During transaction execution, logs are first written to the binlog cache.
    2. Transaction commit, writing data from the binlog cache to the operating system cache;
    3. Finally, the operating system decides when to flush the data to disk;

  • MySQL two-phase commit

    • First prepare;
    • Writing binlog;
    • Writing redo log;
    • Commit;

  • Why is the data lost?

    • Binlog considers writing to the file system cache a success; (Personal understanding)
    • The redo log considers writing to the log Buffer a success; (Personal understanding)
    • So as long as there is no write disk will lose data;

  • How to ensure not to lose?

    • Write cache, and then flush to disk, data can be guaranteed not to lose;
    • Double a strategy is required to fall disk will succeed;

  • Sync_binlog parameter configuration:

    • 0: Each commit transaction is only written to the file system cache, and data is not persisted to the disk, which is fast.
    • 1: Each commit transaction is persisted to disk;
    • N(N>1) Data is written to the file system cache for each committed transaction, but data is flushed to disk after N transactions are accumulated

  • Innodb_flush_log_at_trx_commit parameter configuration:

    • 0: Data in the Log Buffer is written to the file system cache in batches and flushed to disk every second. (May lose 1 second of data)
    • 1: Data in the Log Buffer is written to the file system cache and flushed to disk each time a transaction commits. (default MySQL configuration to ensure ACID properties of transactions)
    • 2: Every time a transaction commits, the data in the Log Buffer is written to the file system cache. Every 1 second, MySQL actively flusits the data in the file system cache to disk in batches.

Therefore, we can see that the two-in-one principle ensures that data is not lost because it is very important that data is flushed to disk every time a transaction commits.