How innodb guarantee first writer firstly write redo ?

First of all, InnoDB follows the ARIES protocol, also known as write-ahead logging. It must not write out modified data pages before it has durably written the corresponding redo log records. The redo log covers not only index and BLOB pages, but also undo log pages, transaction metadata (including whether a transaction is committed), and page allocation metadata.

Your question could be answered by the 2018 presentation
Deep Dive: InnoDB Transactions and Write Paths
https://vimeo.com/258533150
https://docs.google.com/presentation/d/1bP3yh57B58yfGDd-34TZ5MeiacIdaseaTLpd_Ov5NI8/

There are more recent presentation that touch the same area, say:
https://fosdem.org/2022/schedule/event/mariadb_innodb/

Any modification of a page will be protected by a non-shared page latch that will be held for a short time. This is typically microseconds, at most milliseconds when an operation needs to wait for some other pages to be read into the buffer pool. Page latches and redo log are combined in mini-transactions.

In your example, both transactions would consist of multiple mini-transactions. At the minimum, one mini-transaction to allocate undo log and write the first undo log record covering the change, another to change the page, and finally one to mark the transaction as committed.

The mini-transaction commit a.k.a. mtr_t::commit() is serialized: an exclusive latch will be acquired to assign a log sequence number (LSN) to the mini-transaction. The LSN is the logical time of InnoDB, similar to the Oracle SCN (system change number). This and the page latches are ultimately what will serialize the two transactions.

Thanks Marko Mäkelä, that solved my question.

Hi, @Marko

I'm still confused with your term "page latches". Many material say the following:

> MySQL uses table-level locking for ISAM, MyISAM, MEMORY (HEAP), page-level locking for BDB tables, and row-level locking for InnoDB tables.

So for innodb, it doesn't use page lock, right ? Then what do you mean by "page latch" ? Is there any differences between "page lock" and "page latch" in innodb world ?

According to this(https://jira.mariadb.org/browse/MDEV-16232), I guess you mean "buf_block_t::lock" ? But buf_lock_t is not the real database page frame, it's just a wrapper of the page. So buf_block_t::lock is just ordinary memory lock which protects the struct from concurrent access, right ?

Correct. A "latch" in InnoDB is, as far as I understand, a short-term lock, like a mutex, that, indeed, protects the struct from concurrent access.