NoOova: Why should I? I do.


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 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:

Heximer: NoOova: It is however explained at 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.

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