0

I have a MySql query that uses a session user variable:

SET @matchedId = (
    SELECT FileId FROM table1
    WHERE DataSetId = @dataSetId
    AND TimestampUtc = @timestamp
    AND ConvertedTimeUtc IS NULL ORDER BY ReferenceTimeUtc DESC LIMIT 1
    );
SELECT * FROM table1 WHERE FileId = @matchedId;
SELECT * FROM table2 WHERE FileId = @matchedId;

The tables aren't particularly relevant, other than the fact that they both have an integer FileId field (auto-increment, primary key in table 1), and table1 has an integer data set id and a few DateTime timestamps.

This query gets run within a transaction, and gets run on a lot of different threads with different values for @dataSetId and @timestamp. I occasionally see that the result of the query has a data set ID and timestamp that do not match the provided parameters. What I get matches some other request that was retrieved earlier. This is fairly rare...most of the time I get exactly what I expect even with many threads.

I am aware that session variables can have previous state from a prior call, but I've only seen that be a problem when I set the variable within a SELECT, such as 

SELECT @id := FileId FROM table1 WHERE ...

specifically when the WHERE causes nothing to match so @id doesn't get updated.

I've tested to make sure this isn't the problem here (ie, when the SELECT finds nothing, it sets @matchedId to null). And in fact the SELECT should be finding something. Running the same query in workbench gets me the right answer. Additionally I have added a SET @matchedId = 0; at the beginning of the query just to be extra sure, and it didn't change the behavior.

I've looked at the code several times and don't see any statics being used in the call stack that could cause problems.

So I'm guessing this is somehow the result of connection pooling and the connection that reproduced this problem was last used for the match I'm receiving, but I cannot figure out what mechanism could be responsible for the behavior. How can this query get a result that matches a previous request? Is there any way for me trace which pooled connections are being used for which requests? Any improvements I can make to the query?

Barmar
  • 669,327
  • 51
  • 454
  • 560
C S
  • 123
  • 1
  • 6
  • 1
    You seem like a technical kind of person. Build your rep up and stick around :p – Drew Oct 18 '16 at 05:19
  • 1
    It sounds like your MySQL connector may be using connection pools, so your query isn't always using the same session as the one that set the variable. – Barmar Oct 18 '16 at 06:12
  • `SELECT CONNECTION_ID();` will identify the connection you're using. This is a monotonic value that starts at 1 with each server restart and increments with each new connection. For the life of an individual connection = thread = session = the scope of user-defined ("session") variables, the value remains constant. This is also the `ID` in `SHOW [FULL] PROCESSLIST` and the information_schema.processlist table. – Michael - sqlbot Oct 19 '16 at 01:15

0 Answers0