MySQL MVCC (InnoDB)

Question

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.

Answer 1

Ignore the issue, and write code that won't get in trouble:

BEGIN;
SELECT id ... LIMIT 20; -- get list of _possible_ candidates for action
COMMIT;
**
foreach $candidate
    BEGIN;
    SELECT ..., is_candidate WHERE id = $candidate FOR UPDATE;
    if still a candidate
        process it
        UPDATE ...;
    COMMIT;

If someone slips in, say, at **, the check later will prevent double-processing. Furthermore, the second BEGIN..COMMIT prevents anyone from slipping in.

FOR UPDATE "locks" the row; this is important.

The advantage of processing this way is that the transactions are quick, thereby minimizing impact on everything else.