while using QueryFirstOrDefaultAsync without Using block ,getting wrong result in output

[sammym1]

While using QueryFirstOrDefaultAsync ,In a certain scenario where the outbound connection in Azure appservice is high , output of the query gives wrong result

Code Sample : var orderDetail = await connection.QueryFirstOrDefaultAsync(query).ConfigureAwait(true);

We are able to fix the issue after wrapping the code(connecting object) inside using block

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = await connection.QueryFirstOrDefaultAsync(query).ConfigureAwait(true); }

Not sure, how disposal of connection object helped to resolve the issue Can any one help me to find the theory behind it?

[mgravell]

Where did connection come from in the first example? Was it being shared between concurrent operations? (ADO.NET generally doesn't support that). In the second (working) example, we can see that the lifetime is scoped and the connection is isolated, but: I can't tell what the lifetime/scoping is in the non-working code.

[sammym1]

In the first example, Connection object was initialized at the beginning of the class and disposed at the end of the class.

[mgravell]

In the first example, Connection object was initialized at the beginning of the class and disposed at the end of the class.

That's fine and valid, but the rules on concurrency still apply, so it would still demand that only one operation is active on the connection at once. If you wanted to go that route, you could use a SemaphoreSlim or similar to enforce that expectation, but it would mean that other concurrent callers were delayed.

[sammym1]

In the first example, Connection object was initialized at the beginning of the class and disposed at the end of the class.

That's fine and valid, but the rules on concurrency still apply, so it would still demand that only one operation is active on the connection at once. If you wanted to go that route, you could use a SemaphoreSlim or similar to enforce that expectation, but it would mean that other concurrent callers were delayed.

Is it applicable while the call is Asynchronous?

[mgravell]

Is it applicable while the call is Asynchronous?

Absolutely. Async doesn't change anything to do with concurrency. Almost all APIs are not intended for concurrency. This means that with sync code, you are only expected to have one thread interacting with the API at once, and with async code, you are only expected to have one flow (for want of a better term) at once. This usually means "make sure you await before issuing the next call", but it extends equally to multiple concurrent call paths/threads too. There are some exceptions - sockets and some streams allow one inbound usage and one outbound usage, independently - but not more than that. Some APIs are designed for massive concurrency by arbitrary threads and call paths, but: ADO.NET is not one of those.

[sammym1]

,In a certain scenario where the outbound connection in Azure appservice is high , output of the query gives wrong result.

What condition can make the program to return a wrong data ? Can a concurrent operation with same connection object can cause this scenario?

And what could be the concurrent operations means exactly? How ,two tasks can be overlapped? Is it something wrong with the QueryFirstOrDefaultAsync API?

[mgravell]

If you make concurrent access to an API that is not designed for concurrency, then anything afterwards is undefined. It could throw an exception, it could return invalid results. It could work most of the time, and only fail occasionally. This is because you're fundamentally now in a race condition scenario, where timing of multiple external and internal competitions change behaviour.

To take a simple example: sending messages on a connection relies on knowing which reply goes with which question. If a race condition means that the write order is confused, or the order in which items are added to a queue, then: chaos. Indeed, even those data structures themselves can explode - especially when (but not limited to) internal buffers get resized to accommodate work.

Overlapping two tasks is as simple as:

1. not awaiting the completion of one operation before beginning the next, or
2. having two separate threads (perhaps separate requests, in a web-server scenario) performing operations at the same time

There are other ways, but those are the main contenders

[mgravell]

As far as I know: no, nothing is wrong with QueryFirstOrDefaultAsync, but: you still need to not call it concurrently.