I know that this question is a little bit provocative, but if you are
interested in philosophy of ADO.NET and reliability at all, this post is right
for you.
In last few weeks, I found some very strange behavior in one ASP.NET 2.0
application. Long time everything was working, but as we started to perform some
load tests we found some very strange behavior in the application. After many
of team members couldn’t solve the problem (described below), I’ve got the task
to do more intensive investigation. Here is the result.
Imagine there is a database with two tables Table1 and Table2. In this example
both table have same columns column_1 and column_2 of the integer type. Note that
behavior described here is fully independent on the table schema. I used this
schema for the sake of simplicity. Assume the tables have data populated as
shown:
Table1:
111 – 112
121 – 122
131 – 132
. . .
1N1 – 1N2
Table2:
211 – 212
221 – 222
231 – 232
. . .
2M1 – 2M2
With this data it is possible to extrapolate the table and the row from each
value. For example the value 271 is the value from Table1, Row 7 and column1. Now
let’s take a look at following example, which is consisted of three code-snippets
(each in one method Main, Test1 and Test2). The first method Main() starts two
threads. In the first thread is executing method Test1 and in the second Thread
is executing method Test2. The Main() ends up when both started threads are
finished.
|
static void Main(string[] args)
{
new
Thread(delegate()
{
Test1();
}).Start();
new
Thread(delegate()
{
Test2();
}).Start();
}
|
Following implements the method Test1. This method successively reads the data
from the table 1. Additionally, it checks if the value of the first column read
from the first row is greater than 199. If you take a look on the content of
table Table1 this will never be the case. Let’s call this condition C1.
|
public static
void Test1()
{
for (int i = 0; i
< 10000; i++)
{
Thread.Sleep(50);
DbConnection
con = m_Factory.CreateConnection();
con.ConnectionString = cConnStr;
DbCommand
cmd = m_Factory.CreateCommand();
cmd.Connection =
con;
cmd.CommandText = "select * from Table1";
con.Open();
using
(DbDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if
(reader.Read())
{
Console.WriteLine(String.Format("Table1:
Field1: {0},
Field2: {1}",
reader[0], reader[1]));
if (reader.GetInt32(0) >= 200)
Console.WriteLine("Error");
}
}
}
}
|
Now let’s take a look on the method Test2, which is started in the second thread.
Comparing to method Test1, this one is almost identical. The only differences
are marked as red-bold. This method reads the data from the Table2 and checks
if the value of the first row and first column is less than 200. However we
know that this will also never be the case, because this value is always set on
211. Let’s call this condition C2.
|
public static
void Test2()
{
for (int i = 0; i
< 10000; i++)
{
Thread.Sleep(50);
DbConnection
con = m_Factory.CreateConnection();
con.ConnectionString = cConnStr;
DbCommand
cmd = m_Factory.CreateCommand();
cmd.Connection =
con;
cmd.CommandText = "select *
from Table2";
con.Open();
using
(DbDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if
(reader.Read())
{
Console.WriteLine(String.Format("Table1:
Field1: {0},
Field2: {1}",
reader[0], reader[1]));
if (reader.GetInt32(0) < 200)
Console.WriteLine("Error");
}
}
}
}
|
Now, one philosophical question: “If one of two conditions C1 and C2 is satisfied,
is the ADO.NET 2.0 reliable?” Theoretically, I would vote for answer NO. Out
there, there might be some people who think different. However, practically, I
think this should NEVER happen.
In the next step, I extend the example with some additional
threads. First I slightly changed the method Main(). In this case method Main()
starts two additional threads, which execute the new method Test3 (each of them).
This method creates the connection to
database, creates the command, which just insert one row in the table called
Table3. Additionally, method Test3 is started in two different threads, which obviously
share the same command and connection. Be aware that this is not the best
programming pattern, because you will easily run into multithreading problems,
depending on implementation of the method Test3.
|
static void Main(string[] args)
{
new
Thread(delegate()
{
Test1();
}).Start();
new
Thread(delegate()
{
Test2();
}).Start();
DbCommand
cmd = m_Factory.CreateCommand();
cmd.CommandText =
"insert
into Table3 (column_1, column_2) values (0, 1);";
DbConnection
con = m_Factory.CreateConnection();
con.ConnectionString = cConnStr;
cmd.Connection = con;
con.Open();
new
Thread(delegate(object cmdInstance)
{
Test3(cmdInstance as DbCommand);
}).Start(cmd);
new
Thread(delegate(object cmdInstance)
{
Test3(cmdInstance as DbCommand);
}).Start(cmd);
}
|
Finally, the method Test3() executes the command and
sometimes just closes the connection. This is of course the bug, because the another
thread which also runs the same Method
shares this connection. However, practically, this can happen implicitly, when
you have classes which wrap up dealing with connections. In such cases this
problem would not be obvious as in this example. This code snippet is just “huge”
simplification of the real code, which I had to analyze.
|
public static void Test3(DbCommand cmd)
{
int
n = 0;
while (true)
{
try
{
cmd.ExecuteNonQuery();
if (++n == 10)
{
n = 0;
cmd.Connection.Close();
}
Thread.Sleep(100);
}
catch(Exception
ex)
{
cmd.Connection.Close();
cmd.Connection.Open();
Console.WriteLine(ex.Message);
}
}
}
|
As you see, the method Test3 sometimes closes the
connection. This will force another thread, which also executes the method
Test3 to fail on the line cmd.ExecuteNonQuery(). In this case the exception will
be thrown and the connection just re-opened. I know, it is not good design, but
implicitly could happen.
You will probably not believe me, that when this happen, at least one of conditions
C1 or C2 is satisfied. Personally, I would believe this too. Because of that I
wrote example, so you can do it by yourself.
Note: If the Visual
Studio hangs with 100%-CPU start the example again or start it without
debugger. This is an issue of VS and there is nothing to do with this
example.
I would be very glad to see you comment about this dangerous behavior.
[Copyright 2007, DAENET GmbH -
www.daenet.eu]
Posted
Jul 18 2007, 12:29 PM
by
Damir Dobric