A transaction involving a single InnoDB INSERT statement commits. Which list of locations is in the sequence of disk writes for this transaction?

Question

Options

• Abinary log, redo log, doublewrite buffer, and tablespace
• Bredo log, binary log, doublewrite buffer, and tablespace
• Cdoublewrite buffer, redo log, tablespace, and binary log
• Dredo log, doublewrite buffer, tablespace, and binary log

• A
  94% (51)
• B
  2% (1)
• C
  4% (2)

Explanation

When InnoDB commits a transaction with binary logging enabled, MySQL uses a two-phase commit (2PC) protocol: the binary log is written and flushed first (this is what officially "commits" the transaction from MySQL's perspective), then the redo log commit record is written to confirm InnoDB's side of 2PC. Only after the transaction is durably committed do dirty data pages flush through the doublewrite buffer (a crash-safety mechanism that writes pages twice to prevent torn-page corruption) and then to the actual tablespace file.

• B is wrong because it places the redo log before the binary log - this reverses the 2PC ordering; the redo log prepare record is written earlier, but the commit record comes after the binary log write.
• C is wrong because the doublewrite buffer is a late-stage page-flush mechanism, not an early commit step - it would never precede the redo log.
• D is wrong because placing the binary log last contradicts 2PC entirely; the binary log write is what signals the commit, not a trailing operation.

Memory tip: Think "B-R-D-T" = "Before Releasing Data, Think" - Binary log, Redo log, Doublewrite buffer, Tablespace - and remember the binary log always leads because it's the 2PC coordinator between MySQL and InnoDB.

No community discussion yet for this question.