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! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]