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?
1
-
2Are you reinventing the wheel? Connection Pooling Also look at the Thread Safety remark on the SqlConnection doc page.Steve– Steve2014-12-11 14:38:54 +00:00Commented Dec 11, 2014 at 14:38
Add a comment
|
1 Answer
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.
2 Comments
Anders H
Thank you. Yes, my question was about reusing SQL connection across threads, and not about connection pooling. Microsofts documentation is not very clear on this. So I will redesign, so the connections are not used across different threads
Jeroen Mostert
@AndersHyldahl: the documentation always has boilerplate to point out that instances are not thread safe (the classes for which instances are thread safe are a small minority) but there is almost never documentation on whether instances have affinity for a thread, because this is an even smaller minority. You can assume, if the docs are not more specific, that you can use an instance from any thread, as long as no more than one thread at a time is using it. In the case of
SqlConnection, the point is moot since you there's no compelling reason to share instances.