My last post about static variables caused a few comments, so I think that it needs more clarifications. The first thing is to understand what I am talking about when I'm thinking about thread safety.
First, we define the database class:
public class DataBase
{
public static SqlConnection Connection = CreateAndOpenConnection();
private static SqlConnection CreateAndOpenConnection()
{
SqlConnection sqlConnection = new SqlConnection("Data Source=localhost;Initial Catalog=test;Integrated Security=True");
sqlConnection.Open();
return sqlConnection;
}
}
Notice that the SqlConnection variable is public static, and in its documentation there is a comment saying that public static variables of this type are safe for multi threading. The documentation is even current in this instance. It is just talking about access the variable itself, not working with it.
Update: There is something wrong in my reading comprehension. Sergey posted a comment about the above sentence. I read it wrong. It is not public static memebers of this type that I define. It is public static members that this type has. Damn! Sorry about the mistake.
Here is a simple method that works with this object. Notice that there is a slight delay to simulate heavier load:
private static void GetData()
{
using (IDbCommand command = DataBase.Connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Persons";
using(IDataReader reader = command.ExecuteReader())
{
Thread.Sleep(500);
while(reader.Read())
{
Console.WriteLine(reader.GetValue(0));
}
}
}
}
Pretty standard stuff so far, isn't it? Now, let us look at our Main method:
static void Main(string[] args)
{
new Thread(GetData).Start();
new Thread(GetData).Start();
Console.ReadKey();
}
We are accessing the GetData() method from two different threads, and both threads end up using the same instance of SqlConnection.
Can you guess what the result will be?
In my tests, it consistently throws one of these exceptions:
- Invalid attempt to Read when reader is closed.
- There is already an open DataReader associated with this Command which must be closed first.
We have got ourself a threading problem...
Clarification: The issue that I am raising here is a basic one, I know. I'm trying to reach some place and I want to go all the way, so I wouldn't lose anything along the way. It should be clearer later. (Oh, and the static connection property is evil. It is just the simplest example I could think about).
+972 52-548-6969
