In mysql we use @ for user-defined variable, which stays alive until the connection is closed. In java, when multiple threads share one connection pool, while calling a stored procedure concurrently to get rankings:
BEGIN
SET @rank := 0;
SELECT @rank := @rank + 1 as rank FROM ...
END
If 2 threads are calling the procedure at same time, without synchronizing @rank, is it possible @rank may return unexpected result?
Is there a better way to handle this situation?
Thanks!
Whether you get "unexpected results" or not depends on what you expect, which is not clear to me from your question.
Are you trying to isolate the variable to a specific connection, or increment it as a shared counter?
The scope of a user-defined variable is limited to a single connection, and each connection will be used by a single thread at any given time, so with your example you should expect the value to be isolated within each connection. Since you are re-initializing the value to 0 in your code, you should not see any residual effects of separate processes that used the connection previously.
However, since you said you are using a stored procedure, I recommend that you declare a local variable inside the stored procedure and use that instead of a user-defined variable.
The local variable will be scoped to the stored procedure, so you don't have to worry about the current value affecting a later thread re-using the connection.
