Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Paul DuBois (paulmysql.com)
Date: Mon May 12 2008 - 10:59:59 CDT
On May 9, 2008, at 6:58 AM, Chris Pirazzi wrote:
> 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 18.104.22.168. Consistent
> Non-Locking Read:
> "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
> 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
> 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
> - 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
> Thanks for your time! Hopefully we can clarify this for all mysql
The background for this change is Bug#30184:
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql