QueryAsync sometimes returns data for a different request than the current one

[spyro89]

I have an application in ASP.NET MVC + ASP.NET API (.NET 9, OS Windows Server 2022, MS SQL 2022), where I use the Dapper library (2.1.66) to call stored procedures (other queries are handled by EF 9.0.4).

Recently, I noticed that with a high volume of requests to call the same procedure but with different parameters, sometimes I receive data from the result of a different request's execution. It seems that both the SQL and C# code are correct. Additionally, I have been monitoring the database queries and intentionally added an OUTPUT parameter (FakeParameter) with a random GUID to make sure this is not a SQL code issue. Importantly, this error occurs occasionally for different input parameters, with no apparent pattern, except that both requests need to come almost simultaneously. In such cases, Request 1 will receive its correct data in response, but Request 2 will receive the same data as Request 1 instead of its own separate data.

Below are code examples:

List<SP_Result> data = null;
var args = new DynamicParameters(new
{
AccountId = model.AccountId,
SortField = model.OrderColumnNo,
SortDirection = model.OrderDirection,
});
args.Add("RecordsTotal", dbType: DbType.Int32, direction: ParameterDirection.Output);
args.Add("FakeParam", dbType: DbType.Guid, direction: ParameterDirection.Output);

using (var con = new SqlConnection(DBContext.Database.GetConnectionString()))
{
data = (await con.QueryAsync<SP_Result>("SPName",
args, commandType: CommandType.StoredProcedure)).ToList();
}

I am certain (through logging data to the logs) that the GUID value in FakeParam for Request 2 matches what was returned by the stored procedure call for the same request, but after calling ToList, the data returned is from Request 1.

[mgravell]

This is curious. The only times I've seen this, it has been due to incorrect overlapped operation on a connection involving multiple threads accessing the same connection instance, due to either an unawaited "async" call, or explicit parallelism. Or in more broken cases: a flat-out connection field that is simply shared between requests (think "static", singleton, etc).

Dapper doesn't have any shared data state (caveat: if has a strategy cache for the reflection pieces, but that doesn't include any record data - it is pure schema / type metadata). It doesn't have any data cache, command reuse (well, AOT does have optional command reuse, but you don't seem to be using that), etc.

Are you absolutely 100% treble-checked sure you're not accessing the same connection instance concurrently?

Also: are you sure that SPName isn't doing something silly internally, for example a global temp table, shared staging table, or similar?

[spyro89]

Yes, I am 100% sure that the connection object is created anew each time using a using statement, exactly as shown in the example I provided in the first post.

The stored procedure uses only a table variable – DECLARE @results TABLE.

The application where this issue was noticed has been running for quite some time, and the problem started appearing around the time the project's .NET version was updated to version 9. So it’s possible that the issue is not with Dapper itself, but rather with something in .NET (e.g., ADO.NET).

I wanted to compare this problem by using the latest EF method for calling stored procedures with result mapping:
SqlQuery<T>(FormattableString sql)
However, it turns out that proper handling of OUTPUT parameters does not work correctly with it.

By analyzing the calls using SQL Profiler, I am absolutely certain that the stored procedure executions for both requests are correct and that the OUTPUT parameter values in the code are also correct after the calls. The only issue is that the returned and mapped data for request 2 are actually the results of request 1.

There is also no possibility of an issue in the stored procedure or SQL code itself because the most important input parameter – AccountId – fully separates the possible set of return values.

And it’s worth noting that the issue is not consistently reproducible – it may occur once for a specific parameter set, and the next time the same parameter set will work correctly.

[mgravell]

I would assume it is more related to concurrency against the underlying connection layer, rather than specific inputs. I can think of absolutely no way Dapper can contribute to this, except for invalid concurrent use against the same connection. That can't be the case with your new connection usage, so that leaves just the underlying connections.

I'm intrigued, but without meaning to deflect, I would agree that the most likely cause here is the ADO.NET provider. Have you changed between System.Data and Microsoft.Data, for example?

[spyro89]

Yes, I’ve tried using both System.Data and Microsoft.Data, but unfortunately the same issue occurred in both cases. I also tried downgrading the versions of Dapper and Microsoft.Data, but that didn’t make any difference either. Unfortunately, I think I’ll have to try downgrading the .NET version, as that might be the only way to clearly determine whether the issue started when moving to .NET 9.

[mgravell]

I am genuinely intrigued here, and would love to stay "in the loop" - obviously reports of data oddness concern me greatly. If a reliable repro (even if it takes an hour or twelve to repro) can be found, I may be able to help funnel it to appropriate folks, assuming it isn't a hitherto unfound Dapper snafu.