MySQL has the ability to declare user variables. In some other DBMS there are analogues to this. Question: if I make two sql read requests, is a write operation possible between them? Example:

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

The example is taken from https://dev.mysql.com/doc/refman/5.7/en/example-user-variables.html . In particular, I’m wondering if the table can, for example, delete the maximum value after the first, but before the second query, so the second query will return an incorrect result.

  • autoincrement is independent, innodb and lock myisam transactions do not care how much ... so if you are about writing records, it will change according to any readings between inserts - Jenyokcoder
  • 2
    Even one select is not atomic - Sergey
  • @Sergey, thanks - Mikhail Ionkin

2 answers 2

Inside a transaction with a SERIALIZABLE isolation SERIALIZABLE — no, otherwise — yes. REPEATABLE READ will not save here from the so-called. phantom read - it will not allow other transactions to update already existing records, but it will not save them from adding new ones.

  • If not difficult, give an example or a link to the information. - Mikhail Ionkin
  • one
    @MikhailIonkin en.wikipedia. , perhaps, someone else will write off - etki
  • The link is good, and it is for her that I put the question security. - Mikhail Ionkin
  • What is a защита вопроса ? - vp_arth
  • @vp_arth, meant the adoption of an answer, or the decision of a question, - by analogy with the “diploma defense”, when the respondent “defends” his solution to the problem. - Mikhail Ionkin

And what prevents to write such a query:

 SELECT * FROM shop WHERE price=MIN(price) OR price=MAX(price);
  • In this case, nothing. But in more complex examples I would like to use the declaration of variables. This would reduce the code and make it, as it seems to me, more understandable. - Mikhail Ionkin
  • if a request is not announced as the beginning of a transaction, changes may occur between them - Sergej Streck
  • Can I have more? In particular, to give an example when two requests are not declared as the beginning of a transaction, and the opposite example: when two requests are declared as the beginning of a transaction. - Mikhail Ionkin
  • Do you send commands to the server manually or from program code? - Sergej Streck
  • 2
    It does not interfere with (except for the absence of a keyboard), but MySQL will prevent it from executing ... you cannot use group functions in the phrase where - Mike