SEO

Hello,

I _thought_ I knew how InnoDB worked, but due to a recent mysql doc
change, I am no longer sure--the change made the dox significantly
less clear, and potentially code-breaking.

Please can someone tell me the real behavior of InnoDB in the
following case, and ideally clarify the dox too...

The question comes up in the first paragraph of 13.5.10.4. Consistent
Non-Locking Read:

http://dev.mysql.com/doc/refman/5.1/...tent-read.html

"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. The exception to this rule is that the query sees the
changes made by earlier statements within the same transaction. 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."

The unclear wording is "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"

What does the author mean by "any" rows? Do you mean that when you do
a SELECT, you may get back a result for your modified row, AND you may
ALSO get back a result for an older version of the SAME row? This is
very very important as it affects how we can use non-locking read at
the lowest level of our code.

You may wonder why I suspect this case...it's all because of what the
text USED to say:

(change made by user paul on 2007-08-23 16:38:39 +0200 (Thu, 23 Aug 2007)
http://lists.mysql.com/commits/32967?f=plain )
- see the latest version of the updated rows, while it sees the
- old version of other 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.
+ 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.

the old wording "the old versions of OTHER rows" was crystal clear:
you will only see one copy of your new row, and it will be your new
copy. the new wording is unclear.

but the new wording makes me wonder if InnoDB could return multiple
copies of the rows I have modified.

Can someone clarify the actual InnoDB behavior?

Could someone suggest a clearer wording for the dox that
1) expresses the actual InnoDB behavior
2) covers whatever case Paul was trying to cover when he made that change?

Thanks for your time! Hopefully we can clarify this for all mysql users!

On May 9, 2008, at 6:58 AM, Chris Pirazzi wrote:

> Hello,
>
> I _thought_ I knew how InnoDB worked, but due to a recent mysql doc
> change, I am no longer sure--the change made the dox significantly
> less clear, and potentially code-breaking.
>
> Please can someone tell me the real behavior of InnoDB in the
> following case, and ideally clarify the dox too...
>
> The question comes up in the first paragraph of 13.5.10.4. Consistent
> Non-Locking Read:
>
> http://dev.mysql.com/doc/refman/5.1/...tent-read.html
>
> "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. The exception to this rule is that the query sees the
> changes made by earlier statements within the same transaction. 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."
>
> The unclear wording is "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"
>
> What does the author mean by "any" rows? Do you mean that when you do
> a SELECT, you may get back a result for your modified row, AND you may
> ALSO get back a result for an older version of the SAME row? This is
> very very important as it affects how we can use non-locking read at
> the lowest level of our code.
>
> You may wonder why I suspect this case...it's all because of what the
> text USED to say:
>
> (change made by user paul on 2007-08-23 16:38:39 +0200 (Thu, 23 Aug
> 2007)
> http://lists.mysql.com/commits/32967?f=plain )
> - see the latest version of the updated rows, while it sees the
> - old version of other 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.
> + 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.
>
> the old wording "the old versions of OTHER rows" was crystal clear:
> you will only see one copy of your new row, and it will be your new
> copy. the new wording is unclear.
>
> but the new wording makes me wonder if InnoDB could return multiple
> copies of the rows I have modified.
>
> Can someone clarify the actual InnoDB behavior?
>
> Could someone suggest a clearer wording for the dox that
> 1) expresses the actual InnoDB behavior
> 2) covers whatever case Paul was trying to cover when he made that
> change?
>
> Thanks for your time! Hopefully we can clarify this for all mysql
> users!

The background for this change is Bug#30184:

http://bugs.mysql.com/bug.php?id=30184

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com