CODEWITHSUNDEEP
sql.netmultithreading
Anders H
Anders H

Reputation: 411

Using the same open SQL connection from different threads

I have a server-system, in which I have a pool of open SQL-connections. Requests are coming in into the server-system in different threads. Is there any problems having each request-thread to pick an open SQL-connection from the pool, use it and then return it - still open - to the pool again? Compressed: Is it allowed to use an open SQL-connection from different threads, or is the opening of an SQL connection in some way bound to the thread that has opened it?

Upvotes: 2

Views: 1906

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

Don't share SqlConnection objects across scopes, period. This produces massive headaches, not the least of which is taking care of throwing it away and creating a new one when an error occurs (which renders the connection unusable). This is not even specific to multiple threads; trying to hang on to a connection even if only one thread ever uses it is bad enough.

Create a new SqlConnection instance for every query you do, and dispose it as soon as you're done. An SqlConnection does not represent a physical connection to the server, it's only a handle to a connection from the connection pool (unless you're silly enough to turn off connection pooling -- it's on by default). Creating and opening one is a very cheap operation if there is already an open physical connection available (and the pool tries to ensure there always is one).

Normally, you don't need to concern yourself with connection pooling since it "just works", but if you want to know more (or need to know more, for tuning purposes) see "SQL Server Connection Pooling" in the MSDN.

Upvotes: 3

Related Questions