When using InnoDB and MySQL, you can avoid row-level locking when selecting data, thus avoiding a potential performance problem. This is done via a "consistent read". From the latest MySQL docs on consistent reads:
A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by those transactions that committed before that point of time, and no changes made by later or uncommitted transactions.
So far, so good. You get a snapshot of data to read and nothing will be updating it underneath you.
The exception to this rule is that the query sees the changes made by earlier statements within the same transaction.
That makes sense. If I change data in a transaction, a subsequent select in the same transaction had better see my changes.
Here's where I get nervous (emphasis mine):
Note that the exception to the rule causes the following anomaly: if you update some rows in a table, a SELECT will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the same table, the anomaly means that you may see the table in a state that never existed in the database.
What? I've read that several times and I still don't get it. What does "older versions of any rows" mean? And what does "may see
This can't be right. Can someone please explain what I'm missing?