1

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?

  • What index(es) are there? – Rick James Dec 27 '18 at 16:04
1

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

  • 1
    Could you please provide the documentation or reference? – Ryan Lv Dec 21 '18 at 6:51
0

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.

  • Hi Rick, thanks for you reply. I updated my question to make it more clean. – Ryan Lv Dec 21 '18 at 6:42
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.