2

Here is multi query running from Node.js

SET @var = 123;
INSERT INTO `table` VALUES ('test', @var)
  ON DUPLICATE KEY UPDATE 
    column = @var := IF(column < @var, @var, column + @var);
SELECT @var column;

Are there any caveats on concurrent queries?

If share the same connection (session) for several threads?

Animir
  • 1,121
  • 10
  • 23

1 Answers1

1

Never share a MySQL connection over several threads. You will cause race conditions. Each thread could (and inevitably does) receive results for queries started on another thread, and this happens unpredictably. You end up with each thread receiving an unintelligible subset of the packets it wants, combined with extra packets it doesn't want.

Also, you mention multi-query but you should not use this either. Execute one statement per call. There's no advantage to using multi-query, if your network is anything close to reasonable performance.

You can be assured that setting or reading user-defined variables like you show in your example is reliable on separate calls to query(), as long as you do it on the same connection.

Regarding node.js, it only allows one statement per request by default. You can enable multipleStatements (see node-mysql multiple statement in one query) if you want.

The alternative is to run statements one at a time, and run them in serial. You can ensure the next statement waits for the prior one to finish by running it in the callback function for the prior statement. An example is shown in the OP's question in node-mysql multiple statement in one query.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks. I'll be more specific. I launch this query from node.js, where callbacks with results are asynchronous. It means, if I split it up to 3 queries, node.js may execute `SET @var = 123;` several times with different values and this will result to unexpected behaviour. (I think) – Animir Jun 27 '18 at 15:43
  • That's why you need to use a separate MySQL connection per thread. You're creating your own stateful protocol on top of MySQL's own stateful protocol. – Bill Karwin Jun 27 '18 at 15:49
  • Thank you, I've asked another question specific to Node.js to understand it more https://stackoverflow.com/questions/51067850/node-js-mysql-connection-queries-order-and-event-loop – Animir Jun 27 '18 at 16:53