The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table:

We have a relatively large table which we periodically scan reading several fields including a field named, say, LastUpdate. During the scan we update previously scanned rows. Updates are batched and performed in a background thread - using a different connection. It is important to note that we update rows that have already been read.

Three questions:

  1. Will InnoDB will save previous versions of the updated rows since the SELECT is still in progress?
  2. Would using READ-UNCOMMITTED for the SELECT help?
  3. How can I confirm that InnoDB saves or does not save previous versions of the modified rows in its redo-log.
up vote 5 down vote accepted
  1. InnoDB always saves the previous version of the row. Everything is copy on write therefore every update is a select -> copy -> write. For how long it is being kept is dependent on

    • the open transactions (obviously as long as you have an open transaction that needs to be able to see an old version it cannot be removed)
    • how busy your server is (purge lag)

    Purge behaviour had been improved a lot in the recent version. Look at dev.mysql.com for more info if you're interested.

  2. I'm not sure what you mean by help. You read first and update next so I don't see too much benefit. You most likely don't want to go below READ-COMMITTED isolation level. That will already eliminate a lot of locking issue (gap locks and next-key locking).

  3. InnoDB redo log contains every change that happens in the innodb tablespace to be able to recover in case of a system failure. The format is: page pointer offset and the changed bytes. So to answer the explicit use case if the previous version is written to the undo space (which it is) then InnoDB will write that change to the log files too.

I hope this helps!

If you want to look into the details more I always recommend people to play with innodb_ruby that is a very handy tool to look into innodb files and understand the basic principles of operations.

  • So the long SELECT statement is considered one transaction and MySQL will keep the old rows in the redo log until the SELECT has finished? And if I'm modifying all the rows in a large table that means MySQL essentially needs to keep two copies of that table? (one for the ongoing SELECT and one with the new version of the data) – nimrodm Jun 14 '16 at 5:18
  • Yes, that's correct. Technically every row will have a roll pointer pointing to the previous version. – Károly Nagy Jun 14 '16 at 7:46
  • Thanks! I've accepted the answer. But one last question: Why wouldn't READ-UNCOMMITTED help? In this case there is no need to keep the older versions of the rows since the current transaction (SELECT) is allowed to see them. Can innodb_ruby show the number of active entries in the log? – nimrodm Jun 14 '16 at 8:33
  • Because you select the rows, update them then select again. The last select will have higher TRX ID than your updates. Therefore there's no select that would "hold" your data. Plus when you have a really big update as you mentioned the purge thread is going to be the bottleneck not the undo space. Regardless of isolation level the rows have to be copied due to MVCC there's no way to avoid that. READ-UNCOMMITTED has the potential to error your script (updating non-exisisting rows), cause integrity issues (changing values to rolled back ids for example). More potential issues than benefits. – Károly Nagy Jun 14 '16 at 13:01
  • Innodb_ruby has a lot of functions for undo history (record-history, undo-history-summary, undo-record-dump) which you can use for investigation. For example: you can find record offsets with index-record-offsets and use the page and offset in record-history to check how many items you have for a specific row. – Károly Nagy Jun 14 '16 at 13:08

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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