4

I do not understand what exactly happen behind the scenes when using static class with static members in multithread environment. I have a server application where clients very often read-write to SQL db. For that, I have created static class with static members SQLConnection,SLQDataReader etc. But as I understood, static class is just one, shared between threads, so how it deals with multiple requests at the same time? Would not be better just have non static class used for that? Thanks!

Improve this question

2 Answers 2

Reset to default
4

I'm going to diverge from the other answers and say that it is a very bad idea to have a SqlConnection and especially SqlDataReader as global state. It is a huge, huge design smell. A few questions to consider:

  • Will the connection always remain open? If so - are you aware that this wastes resources on the database server, and may even cause you to exceed the number of licensed connections?

  • If the connection will not remain open - what steps will you be taking to prevent the scenario where one thread closes the connection while another is using? In fact your code does not necessarily even need to be multi-threaded for this to happen; in order to prevent it, every database operation must first save the original connection state, open the connection if closed, then restore the original state after finishing.

  • Are you serializing access to the SqlConnection? If so, will you lock it for the entire duration of a database operation, which would severely impact overall app performance by causing a lot of unnecessary wait times?

  • If you are not serializing access, what happens when one operation tries to execute some unrelated operation while another is in the middle of an important transaction?

  • If making a "global" SqlDataReader, can you be absolutely sure that only one thread at a time will ever use it, and that thread will never need to execute another query before it is finished with the first result set? Even if SqlDataReader were thread-safe (it isn't), thread-safety rules are out the window if you actually destroy the object in question and replace it with a new one.

A SqlConnection should really be treated as a unit of work - you create it when needed, and dispose of it when finished. A SqlDataReader is not even a unit of work - it is a throwaway object for which there is absolutely no reason to keep at a global level. Even if you implement a singleton as others have apparently suggested, you are setting yourself up for a huge amount of trouble later.

Update: If these "members" are actually factory methods then that is different. The way the question was worded implied that a single connection was actually being shared. If you are simply providing static methods to create connections/queries then you can disregard this.

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

The only safe way to share a SqlConnection in a multi-threaded environment is to ensure that only one thread is using it at a time, which kind of defeats the purpose of multi-threading. You really don't need to share a single SqlConnection object in order to share a physical connection, though; ADO.NET takes care of that for you behind the scenes with connection pooling, as long as you don't disable it.
Just create SqlConnection instances as needed, connection pooling will minimize the actual number of connections and open/closes. The hard work has already been done for you, you just have to use it.
No, each unit of work should use its own SqlConnection. And SqlReader has trivial overhead - you should use a new SqlReader for every query (and dispose it properly when done).
Is this an ASP.NET or other web application? Or RPC/remoting? Because if it's ASP.NET, the "Client" would be in the session state, and storing a SqlConnection there is a very bad idea. In a server application, you need to be creating SqlConnection instances when they are needed, not leaving them hanging around just in case you need them later.
To the best of my knowledge there's no such thing as a "WinForm server app." Does this software really run on a server or does it just connect to one? It changes the question significantly if it's the latter...
|
1

You should be fine as long as you're only using static methods and no static fields. Once you decide to use your first static field, you'll have to worry about thread safety.

Improve this answer

4 Comments

Thanks. Could you describe me please how it works in my current solution? I do not understand how it handles more requests if there is only 1 instance
There are many ways for a static method to create thread-safety or other concurrency problems. What about Application.DoEvents? This answer isn't true irrespective of context - it depends entirely what is in those methods.
Those methods are for reading and writing into DB, all of them take a string as parameter and employ SQLCommand.
Static SqlConnection is a horrible idea, like Aaron describes in his anwser. I do agree that methods are OK, and fields require special attention. This means that as long as the static methods don't use some kind of shared state, you're safe

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.