Menu
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
449
The queries run in parallel, as far as possible. The database uses different locks for read and write, on rows, blocks or whole tables, depending on what you do. If one query only reads from a table, another query can also read from the same table at the same time.
No, each query will require its own session. To execute in parallel, each query must be conducted in its own session.
Parallel query is a method used to increase the execution speed of SQL queries by creating multiple query processes that divide the workload of a SQL statement and executing it in parallel or at the same time.
What is parallel processing? The parallel processing aims to separate big tasks into more than one small task, and these small tasks will be completed by the discrete threads. In this approach, more than one task will be performed in unit time; thus, the response time will be reduced dramatically.
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.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
