Re: pls help clarify dox: InnoDB Consistent Non-Locking Read behavior

[Paul DuBois]

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/en/innodb-consistent-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