G
Guest
I have a situation where a SqlCommand incorrectly throws an
InvalidOperationException when two threads are using the same SqlCommand
instance at different times. As you can see below the application
synchronizes on the shared SqlCommand instances why thread synchronization is
not the issue.
The high level order of events when the exception occurs look like this:
1. Thread1 creates SqlConnection and transaction, sets the Transaction and
Connection property of SqlCommand1, creates a Reader using SqlCommand1,
iterates over the reader and closes the reader
2. Thread1 sets the Transaction and Connection property of SqlCommand2 and
creates a reader using SqlCommand2
3. At this point in time, Thread2 has reached the point where it has created
another SqlConnection and transaction and tries to set the Transaction
property of SqlCommand1. This is the point at which the exception is thrown.
The reason seems to be that the Transaction currently associated with cmd1
is being used with another SqlCommand (cmd2) for which a reader has been
opened (on the first thread).
This seems like a bug at worst or an undocumented feature at best. To get
around the problem, simply add the following two lines after the first
'reader.Close();'.
cmd.Transaction = null;
cmd.Connection = null;
It seems as if the set operation of the SqlCommand.Transaction property does
the following:
'Give me the the current Transaction's Connection's Reader and check if it
is busy. If it is, throw the InvalidOperationException'.
The problem is that the 'Current Transaction's Connection's Reader' was
created from another command (cmd2 in this case) why this logic seems flawed.
It seems this either is a bug or if not, the documentation should be updated
to say to explicitly set the Transaction and Connection properties of the
command to null after the Reader is closed...
To run the application you can use any SqlServer database (specify the
ConnectionString property in app.config) and change the SQL in the
BugReproducer constructor to any valid sql statement. The application goes to
sleep for three seconds after creating the second Reader as to force the
exception situation to occur.
using System;
using System.Threading;
using System.Data;
using System.Data.SqlClient;
namespace SqlCommandBug
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
new BugReproducer().Reproduce();
}
public class BugReproducer {
public static SqlCommand cmd = new SqlCommand();
public static SqlCommand cmd2 = new SqlCommand();
public BugReproducer() {
cmd.CommandText = "Select * from Parameter";
cmd2.CommandText = "Select * from Application_Curve";
}
public void Reproduce() {
for (int i = 0; i < 2; i++) {
new Thread(new ThreadStart(this.DoIt)).Start();
}
}
public void DoIt() {
SqlConnection conn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
conn.Open();
SqlTransaction t1 = conn.BeginTransaction();
lock (cmd) {
cmd.Transaction = t1;
cmd.Connection = t1.Connection;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read());
reader.Close();
cmd.Transaction = null;
cmd.Connection = null;
}
lock (cmd2) {
cmd2.Transaction = t1;
cmd2.Connection = t1.Connection;
IDataReader reader2 = cmd2.ExecuteReader();
Thread.Sleep(3000);
while (reader2.Read());
reader2.Close();
}
t1.Commit();
}
}
}
}
InvalidOperationException when two threads are using the same SqlCommand
instance at different times. As you can see below the application
synchronizes on the shared SqlCommand instances why thread synchronization is
not the issue.
The high level order of events when the exception occurs look like this:
1. Thread1 creates SqlConnection and transaction, sets the Transaction and
Connection property of SqlCommand1, creates a Reader using SqlCommand1,
iterates over the reader and closes the reader
2. Thread1 sets the Transaction and Connection property of SqlCommand2 and
creates a reader using SqlCommand2
3. At this point in time, Thread2 has reached the point where it has created
another SqlConnection and transaction and tries to set the Transaction
property of SqlCommand1. This is the point at which the exception is thrown.
The reason seems to be that the Transaction currently associated with cmd1
is being used with another SqlCommand (cmd2) for which a reader has been
opened (on the first thread).
This seems like a bug at worst or an undocumented feature at best. To get
around the problem, simply add the following two lines after the first
'reader.Close();'.
cmd.Transaction = null;
cmd.Connection = null;
It seems as if the set operation of the SqlCommand.Transaction property does
the following:
'Give me the the current Transaction's Connection's Reader and check if it
is busy. If it is, throw the InvalidOperationException'.
The problem is that the 'Current Transaction's Connection's Reader' was
created from another command (cmd2 in this case) why this logic seems flawed.
It seems this either is a bug or if not, the documentation should be updated
to say to explicitly set the Transaction and Connection properties of the
command to null after the Reader is closed...
To run the application you can use any SqlServer database (specify the
ConnectionString property in app.config) and change the SQL in the
BugReproducer constructor to any valid sql statement. The application goes to
sleep for three seconds after creating the second Reader as to force the
exception situation to occur.
using System;
using System.Threading;
using System.Data;
using System.Data.SqlClient;
namespace SqlCommandBug
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
new BugReproducer().Reproduce();
}
public class BugReproducer {
public static SqlCommand cmd = new SqlCommand();
public static SqlCommand cmd2 = new SqlCommand();
public BugReproducer() {
cmd.CommandText = "Select * from Parameter";
cmd2.CommandText = "Select * from Application_Curve";
}
public void Reproduce() {
for (int i = 0; i < 2; i++) {
new Thread(new ThreadStart(this.DoIt)).Start();
}
}
public void DoIt() {
SqlConnection conn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
conn.Open();
SqlTransaction t1 = conn.BeginTransaction();
lock (cmd) {
cmd.Transaction = t1;
cmd.Connection = t1.Connection;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read());
reader.Close();
cmd.Transaction = null;
cmd.Connection = null;
}
lock (cmd2) {
cmd2.Transaction = t1;
cmd2.Connection = t1.Connection;
IDataReader reader2 = cmd2.ExecuteReader();
Thread.Sleep(3000);
while (reader2.Read());
reader2.Close();
}
t1.Commit();
}
}
}
}