Does SqlConnection processes queries in parallel?

Question

If I open a SqlConnection to a SQL Server, and then issue multiple queries from multiple background threads, all using that one connection - will those queries be executed sequentially (don't care about the order)?

Specifically, if at the beginning of one query I change isolation level and then restore it at the end of that query - is there a chance that this isolation level may apply to other queries?

I think not, but want to confirm.

SQL Server 2008 R2

And I am talking about System.Data.SqlClient.SqlConnection

Mince

SqlConnection is not thread-safe. You should not use it on multiple thread. You should create one connection per thread instead. – Floydflss

The only safe way to use a SqlConnection object on multiple threads is to not use it at the same time on more than 1. And yes, any state or configuration you change on one thread will "leak over" to any following threads in this manner. – Floydflss

@LasseV.Karlsen how about using async/await? I think THX-1138 is referring to the possibility of multiple queries running concurrently under the same connection. – Tolmann

I don't know how async/await would change my answer except to say that if you're really asking what happens if I execute multiple asynchronous queries on the same connection on the same thread then I don't know. – Floydflss

Answer 1

Loaded question, a definitive answer is impossible because as @LasseV.Karlsen has stated SqlConnection is not thread safe so behavior is going to be unpredictable. I attempted similar scenarios in the past and failed. Here is what I think will happen with the parameters in your question.

Does SqlConnection processes queries in parallel?

No, it does not know how because it wasn't designed for this task. Though the fact that it's possible to build a process to use it in this manner is tempting.

will those queries be executed sequentially

Yes. The queries will be executed by the SQL engine in the order received. Though your connection object will probably not know which thread to pass results back to and you'll get the dreaded 'object reference error'.

is there a chance that this isolation level may apply to other queries

Yes. If you change the isolation level of the transaction object assigned to your SqlConnection and then one of your threads attempts to use that connection it will have that isolation level by default. The timing of when a secondary thread will do this is out of your control at this point. You can assign a transaction per command (and thereby attain a unique isolation level as desired) but you'll still have the issue of the connection not being thread safe.

Quinta