| Bug #82112 | Consistent reads | ||
|---|---|---|---|
| Submitted: | 5 Jul 2016 11:36 | Modified: | 8 Sep 2016 6:38 |
| Reporter: | Daniel Krysiak | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[5 Jul 2016 11:36]
Daniel Krysiak
[5 Jul 2016 13:13]
MySQL Verification Team
Hi, I'm not sure if I understand your test case but READ COMMITTED has nothing to do with repeatable read, it's a different isolation level, one not supported by cluster (as properly documented on the same url you put in your report). So the fact "that NDB storage engine does not support a consistent read" is clear from the same page you put there: https://dev.mysql.com/doc/mysql-cluster-excerpt/5.6/en/mysql-cluster-limitations-transacti... [quote] Transaction isolation level. The NDBCLUSTER storage engine supports [b]only the READ COMMITTED[/b] transaction isolation level. (InnoDB, for example, supports READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE.) See Section 7.3.4, “MySQL Cluster Backup Troubleshooting”, for information on how this can affect backing up and restoring Cluster databases.) [/quote] so as you see we say we support for ndbcluster only read committed and not other isolation levels like for e.g. innodb that support what you want - REPEATABLE READ. URL's of relevance http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_autocommit https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html#isolevel_... https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html#isolevel_... kind regards Bogdan Kecman
[5 Jul 2016 13:23]
Daniel Krysiak
Hi, I'm not writing about repeatable read, nor isolation between transactions. To give another example. In NDB when committing an update on 1000 rows to change values from 0 to 1, it's possible that 'SELECT count(*) FROM table WHERE value = 1' is gonna return something in between 0 and 1000. This means SELECT sees a partial change of the commit. NDB allows to select an inconsistent state of the database. This is not an issue in InnoDB, even on read-commited isolation level.
[5 Jul 2016 14:33]
MySQL Verification Team
> possible that 'SELECT count(*) FROM table WHERE value = 1' > is gonna return something in between 0 and 1000. > This means SELECT sees a partial change of the commit. Sorry not what I understood you wrote. That's a bug then. What version of MCCGE are you using? Best regards Bogdan Kecman
[5 Jul 2016 15:08]
Daniel Krysiak
Hi, Sorry, I know it's outdated, and I most likely won't be able to test on something newer: "mysql-5.6.24 ndb-7.4.6". I'm attaching a Python script I used to confirm this limitation/bug. Regards
[5 Jul 2016 17:20]
MySQL Verification Team
Hi, This looks like a bug, not like wrong documentation :( Setting this to verified and moving forward to devs. The test you provide show that it's actually "read uncommitted". kind regards Bogdan Kecman p.s. confirmed with 7.4.11 [root@localhost ~]# ./keetah.py --host=127.0.0.1 -u test -p test -d test OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK WRONG! 36938 WRONG! 65072 WRONG! 65072 WRONG! 65072 WRONG! 57969 OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK OK WRONG! 65072 WRONG! 65072 WRONG! 65072 WRONG! 65072 WRONG! 65072 WRONG! 23946 OK OK OK OK OK OK OK OK OK OK OK OK OK OK ^CExiting.... Exception KeyError: KeyError(139842514278144,) in <module 'threading' from '/usr/lib64/python2.7/threading.pyc'> ignored Exiting.... Exiting.... [root@localhost ~]#
[22 Jul 2016 9:54]
Jon Stephens
Wrong category, should be Server:Cluster.
[26 Jul 2016 7:41]
Jon Stephens
There is a is known issue involved here which we will clarify in the documentation.
[26 Jul 2016 9:45]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.
[26 Jul 2016 14:45]
Daniel Krysiak
Hi,
Thanks for a very complete documentation update.
"You should keep in mind that NDB implements READ COMMITTED on a per-row basis" is exactly what I expected and it's a very important note, but...
"To ensure that a given transaction reads only before or after values, you can impose row locks using SELECT ... LOCK IN SHARE MODE."
I updated my test case by adding "LOCK IN SHARE MODE" in the SELECT statement, and it still doesn't allow to get a consistent read. It makes everything very slow and it makes anomalies happen less often, but still, if you wait long enough NDB will return wrong results. I have run it for around 2h, out of 8099 selects 2 returned bad results.
However, what's even more interesting to me, is that even if I impose FOR UPDATE lock before running the update like this:
cur = con.cursor()
cur.execute('SELECT id FROM test WHERE value = "5" and id <= 100000 FOR UPDATE')
cur.execute("UPDATE test SET value = %s WHERE id <= 100000" % (5+(counter%2)))
con.commit()
cur.close()
it still doesn't help.
Looks to me like commit starts by releasing the locks, instead of doing this at the end.
BTW. I'm finally testing on 7.4.11.
[30 Aug 2016 23:03]
MySQL Verification Team
Hi, > Looks to me like commit starts by releasing the locks, > instead of doing this at the end. Seems so. Looking trough the code and discussing further with ndbcluster devs (mostly Frazer), the main issue here is that "transaction" for ndbcluster and "transaction" for mysqld are not a same thing. As for "LOCK IN SHARE MODE", while making everything way slower, I haven't manage to reproduce the problem, left the thing to run overnight.. In any case I doubt LOCK IN SHARE MODE is a solution, I'd rather suggest you go around that limitation and organize things differently
