Transactions and Threading

[lil fos]

Hi,

I've written some code that uses a transaction to call a stored
procedure. It is multi-threaded in two ways. First, I create a new
thread to call the method in question. In addition, the whole class
is called by BizTalk every time an EDI file comes into our system, so
this DLL could be fired up several times a second during peak volume.
Our problem manifests when we drop a few files into BizTalk all at
once, which calls my code a few times all at once.

I tried commenting out the thread-spawning code to simplify things,
but I still can't control when files enter our system. Even with my
threading removed, I'm still getting the error, "SqlConnection does
not support parallel transactions" when the code calls
connection.BeginTransaction() on about 1 out of every 4 or 5 files
processed. I also see a few "This SqlTransaction has completed; it is
no longer usable" on trans.Rollback(), and "Invalid operation. The
connection is closed." on connection.BeginTransaction().

Before I removed the threading, I also got errors like "ExecuteReader
requires an open and available Connection. The connection's current
state is Closed," "Object reference not set to an instance of an
object," and "BeginTransaction requires an open and available
Connection. The connection's current state is Open."

The interesting thing about that ExecuteReader error is that my code
is not using ExecuteReader, but some code that executes after my
thread is spawned does use it. I guess that's why it doesn't show up
when there is no multithreading. This does not explain, however, why
my class traps the exception about ExecuteReader and writes it to the
event log. I would think that the other code would either handle or
throw this exception.

Anyway, here's my code (below). Most of the database stuff came
straight from MSDN or something. The function at the top is mine.
The code comes from multiple files and classes, but this shouldn't
really hinder your interpretation.

Can anyone tell what is wrong? I'm hoping that I can tweak some
threading or connection parameters and get it to work as it was
intended.

Thanks for your time!

myClass myObj = new MyClass();
myObj.AddRecord(); // added when next two lines were commented out

//System.Threading.Thread thfoo = new System.Threading.Thread(new
System.Threading.ThreadStart(myObj.AddRecord));
//thfoo.Start();

private void AddRecord()
{
string strSPName = "foo";

arParms[0] = new SqlParameter("@foo1", SqlDbType.VarChar, 25 );
arParms[0].Value = "hello";
...
arParms[7] = new SqlParameter("@foo2", SqlDbType.VarChar, 255);
arParms[7].Value = "world";


using (SqlConnection conn = DBConnection.GetConnection())
{
SqlTransaction trans = conn.BeginTransaction();

try
{
// Call ExecuteNonQuery static method of SqlHelper class
// We pass in command type, stored procedure name, and an array
of SqlParameter objects
SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure,
strSPName, arParms);
trans.Commit();
}
catch (Exception ex)
{
// roll back transaction
trans.Rollback();
}
finally
{
conn.Close();
}
}
}

public static SqlConnection GetConnection()
{
if (myConnection == null || myConnection.State ==
ConnectionState.Closed)
{
new DBConnection();
}
return myConnection;
}

private DBConnection()
{
myConnection = new SqlConnection(strDBConnectionString);
myConnection.Open();
myConnection.StateChange += new System.Data.StateChangeEventHandler
(myConnection_StateChange);
myConnection.InfoMessage += new
SqlInfoMessageEventHandler(myConnection_InfoMessage);
}

public static int ExecuteNonQuery(SqlTransaction transaction,
CommandType commandType, string commandText, params SqlParameter[]
commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction,
commandType, commandText, commandParameters);

//finally, execute the command.
int retval = cmd.ExecuteNonQuery();

// detach the SqlParameters from the command object, so they can be
reused
cmd.Parameters.Clear();
return retval;
}

private static void PrepareCommand(SqlCommand command, SqlConnection
connection, SqlTransaction transaction, CommandType commandType,
string commandText, SqlParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}

//associate the connection with the command
command.Connection = connection;

//set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;

//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}

//set the command type
command.CommandType = commandType;

//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}

return;
}

private static void AttachParameters(SqlCommand command,
SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value ==
null))
{
p.Value = DBNull.Value;
}

command.Parameters.Add(p);
}
}

 

[David Browne]

Hi,

I've written some code that uses a transaction to call a stored
procedure. It is multi-threaded in two ways. First, I create a new
thread to call the method in question. In addition, the whole class
is called by BizTalk every time an EDI file comes into our system, so
this DLL could be fired up several times a second during peak volume.
Our problem manifests when we drop a few files into BizTalk all at
once, which calls my code a few times all at once.

I tried commenting out the thread-spawning code to simplify things,
but I still can't control when files enter our system. Even with my
threading removed, I'm still getting the error, "SqlConnection does
not support parallel transactions" when the code calls
connection.BeginTransaction() on about 1 out of every 4 or 5 files
processed.

OK I think your code simply is not thread-safe. 2 threads cannot share a
SqlConnection.

Your problem is here:

public static SqlConnection GetConnection()
{
if (myConnection == null || myConnection.State ==
ConnectionState.Closed)
{
new DBConnection();
}
return myConnection;
}

If two threads call GetConnection right after each other, they will get the
_same_ connection. This can't work. Each thread needs its own connection.

Just change GetConnection to :

public static SqlConnection GetConnection()
{
SqlConnection con = new SqlConnection(. . .);
con.Open();
return con;
}

Then each thread will get its own connection.

David

 

[lil fos]

cool...that is the kind of small tweak I was hoping for.

Since, as you can see, I tend to let other people write the nitty
gritty database access code, I don't fully understand connection
pooling. Shouldn't ADO or SQL Server or something automatically
manage the connection objects for me by issuing new connections when
one is needed or recycling old ones when they are abandoned?

Thanks again.

 

[Scott Allen]

Yes - ADO.NET will take care of connection pooling for you. The key is
to call the Close method of the connection as early as possible to
return the connection to the free pool to reuse.

Take a look at "Connection Pooling for the .NET Framework Data
Provider for SQL Server" for details:
http://msdn.microsoft.com/library/d...nectionpoolingforsqlservernetdataprovider.asp