CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2sqlconnection
THX-1138
THX-1138

Reputation: 21740

Does SqlConnection processes queries in parallel?

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

Upvotes: 8

Views: 1707

Answers (1)

Chris
Chris

Reputation: 1078

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.

Upvotes: 2

Related Questions