1

Given the following example:

$mysqli->query("SELECT * FROM `table`");
$mysqli->query("INSERT INTO `table` SET `x` = 'y' WHERE `x` = 2");

I have no guarantee that all queries are necessarily executed subsequently. Another process may intercept and a deadlock may occur.

Does MySQL guarantee that they are executed subsequently without any other process making a query in between?

Please do not suggest transactions / locks. This question is regarding multi_query and its specifics.

Example Code in PHP:

$query = "SELECT * FROM `table`;";
$query .= "INSERT INTO `table` SET `x` = 'y' WHERE `x` = 2;";
$mysqli->multi_query($query);
Arne TR
  • 46
  • 4
  • 1
    Have a look at transactions, they might help you out – Nico Haase Dec 09 '17 at 08:38
  • If your INSERT is just a case of SELECTing data and then adding it, look into INSERT... SELECT statements which combine the two. Not always possible but are a useful tool if relevant. – Nigel Ren Dec 09 '17 at 08:49
  • *Another process may intercept and a deadlock may occur.* How exactly would another process interfere once you have obtained your table-level write lock, via the first query? – Michael - sqlbot Dec 09 '17 at 20:46
  • I modified the question. I know how to solve the problem with transactions and / or locks. However, my question is specifically on the handling of mysql regarding if these queries are subsequent or not. I also removed the lock query to not cause confusion. – Arne TR Dec 10 '17 at 09:42

1 Answers1

1

Of course no. It would make a hell of any site using multi query, if it would make all the other world to wait.

What you are looking for is locks and possibly transactions

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Maybe my question was too specific. I know how to solve the problem with transactions and / or locks. However, my question is specifically on the handling of mysql regarding if these queries are subsequent or not. Do you have any source for your answer? – Arne TR Dec 10 '17 at 09:40
  • Come on, dude. To make sure that queries are subsequent, you have to lock tables. Do **you** have any source for your fantasies? Is there a single sentence in the man telling you that multi query puts a lock on all the tables it is running against? – Your Common Sense Dec 10 '17 at 10:19
  • I am not talking about locks. Queries being subsequent and locking a table are two totally different things. When you lock a table with a write lock you have the guarantee, that all other connections trying to do a write will be on hold. However, other connections may well issue selects on different tables (Source: tried myself). However, what I am looking for is a way to send an arbitrary amount of queries to the server of which the server guarantees me, that no other connection may put any query in between. Maybe multi_query is the answer, but I cannot find any reliable documentation on this – Arne TR Dec 11 '17 at 11:12
  • 3
    To achieve such a weird behavior you are talking about you have to put a write lock on all tables. Where did you get such a fantasy that multi_query performs one is a real mystery to me. To be honest, I am strongly convinced that you just confused yourself and your problem has a much simpler solution – Your Common Sense Dec 11 '17 at 11:38