InnoDB: will uncommitted transaction produce redo log?

Question

Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.

start transaction;

update users set uuid = UUID() from user where id = 1
update users set uuid = UUID() from user where id = 2
update users set uuid = UUID() from user where id = 3
...
...
update users set uuid = UUID() from user where id = 1,000,000
// not yet commit

If a transaction is going to update 1 million records, which takes 100 seconds. During the period of execution, will this uncommitted transaction produce redo log?

Answer 1

innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.

Answer 2

Based on Jeremy Cole's presentation InnoDB: A journey to the core III in Percona Live MySQL Conference 2015.

When the transaction is first started:

1. A transaction ID (TRX_ID) is assigned and may be written to the highest transaction ID field in the TRX_SYS page. A record of the TRX_SYS page modification is redo logged if the field
2. A read view is created based on the assigned TRX_ID.

Record modification

Each time the UPDATE modifies a record:

1. Undo log space is allocated.
2. Previous values from record are copied to undo log.
3. Record of undo log modifications are written to redo log.
4. Page is modified in buffer pool; rollback pointer is pointed to previous version written in undo log.
5. Record of page modifications are written to redo log.
6. Page is marked as “dirty” (needs to be flushed to disk). Therefore the answer is yes.

Transaction commit

When the transaction is committed (implicitly or explicitly):

1. Undo log page state is set to “purge” (meaning it can be cleaned up when it’s no longer needed).
2. Record of undo log modifications are written to redo log.
3. Redo log buffer is flushed to disk (depending on the setting of innodb_flush_log_at_trx_commit).

Conclusion

Will uncommitted transaction produce redo log?

Yes.

Reference

1. https://www.percona.com/live/mysql-conference-2015
2. https://docplayer.net/62963586-Innodb-a-journey-to-the-core-ii-jeremy-cole-and-davi-arnaut.html

Answer 3

The log_buffer is a finite size. When it overflows, stuff will be written to the logfile. Regardless of the value of innodb_flatc, the transaction will eventually be flushed to disk.