Heximer: NoOova: Try the same with: START TRANSACTION WITH CONSISTENT SNAPSHOT
Amescua: Btw I use mysql-workbench cause that whats the teacher uses, he also uses the EER diagram to autogenerate code and nearly encourages us to do the same, which I simply find wrong.
Amescua: I do that it seems to me Im learning mysql-workbenchn and not mysql :p
Hornick: With ‘FOR UPDATE’ rows are showed
Heximer: NoOova: yes even worst
Heximer: NoOova: An to make it even worst SERIALIZABLE does not fix that phantom
Nobrega: Ofcource! It adds ‘FOR UPDATE’ to every select
Heximer: NoOova: Once again it does *not* fix the problem you discovered :
Tantum: Why mysql does not locks query with SELECT FOR UPDATE?
Sancher: If resultset is different with napshot
Heximer: NoOova: SELECT FOR UPDATE does lock rows as you can see with SHOW ENGINE INNODB STATUS
Heximer: NoOova Why mysql does not locks query with SELECT FOR UPDATE?
Clontz: And it coult not lock MVCC rows, as i apologize
Fouraker: Because of this i see not snapshot rows
Logghe: Salle, it seems, that the stuff of the ISP don’t think, that there will be a way to restore that particular database without a backup at hand. As expected.
Candee: Salle: it surprised me that REPEATABLE READ does not guarantee repeatable read.
Heximer: NoOova: I am more surprised that even SERIALIZABLE doesn’t in your case :
Heximer: NoOova: also that SELECT * FROM tbl FOR UPDATE; followed by SELECT * FROM tbl; return different result is clearly a bug and it is amazing this have been around for more than 15 years
Heximer: NoOova: You see how funny it becomes: non-locking SELECT * FROM tbl; is indeed Repeatable :
Heximer: NoOova: Now thanks to yours I have even nastier example with transaction in SERIALIZABLE mode which does not even provide repeatable reads
Kanoy: Reminds me of https://www.percona.com/blog/2015/09/17/clarification-call-maybe-mariadb-galera-cluster/ or more specifically the article it responds to
Heximer: Jkavalik: galera is different story : I don’t have any good proof that galera is ACID across entire server. It seems it violates even A not to mention others
Amill: Salle, no, the actual “problem” is InnoDB even on single instance, so seems like this, galera just adds to the confusion
Heximer: Jkavalik: InnoDB on single instance is always atomic and consistent
Dryden: Salle: also with INSERT INTO SELECT query?
Seremet: Salle, NoOova just showed the test case, Vadim T. shows similar one
Heximer: NoOova: With InnoDB yes it is
Janoski: Or Aphyr shows it, Vadim only explained it with this repeatable-reads feature
Heximer: NoOova: Now when thinking more about this I remember that was the reason for never mixing SELECT with and without FOR UPDATE in same transaction. It is not mentioned in the manual though: http://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html
Heximer: NoOova: It is however explained at http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html but you have to read betweent he lines
Heximer: NoOova: To clarify: SELECT . FROM tbl; in levels lower than SERIALIZABLE is “consistent read” in InnoDB terms. http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_consistent_read
Heximer: NoOova: SELECT . FOR UPDATE; is “locking read” and as such it always runs in READ COMMITTED level even if the transaction level is REPEATABLE READ. In other words “locking read” is non-repeatable
Heximer: NoOova: Why InnoDB allows mixing repeatable and non repeatable reads in REPEATABLE READ mode is question for InnoDB developers
Tompsett: Salle: i try same test in postgresql 9.3
Suits: There are resultsets equals.
Step: Or “only non-locking reads are repeatable”
Heximer: NoOova: Why should I? I do not care about postgresql and we are in Mias channel