Hi,
I have a project that I need to use background worker thread to update database in one transaction. But when I abort the worker thread at the middle of transaction, the State of SqlConnection is closed in catch block. Because the connection state is closed, the Rollback in fact did not really rollback the transaction. I checked Activity Monitor in Sql Server 2005, the connection was still there and was in transaction state. So, if I rerun the update again, I got the dead lock. I am thinking it is a bug in ADO.NET or in Sql Server 2005. I try to use SqlConnection.ClearAllPools but still cannot kill that connection in Activity Monitor. Is there any way to work around this
Below is a sample code that will cause this problme.
private void button1_Click(object sender, EventArgs e)
{
worker =
new Thread(new ThreadStart(this.Start));worker.IsBackground =
true;worker.Start();
}
private void ExecuteCmd(string cmdText){
using (SqlCommand cmd = cnn.CreateCommand()){
cmd.Connection = cnn;
cmd.Transaction = tx;
cmd.CommandText = cmdText;
cmd.CommandType =
CommandType.Text;cmd.CommandTimeout = 15;
try{
cmd.ExecuteNonQuery();
}
catch (ThreadAbortException){
ConnectionState state = cnn.State; if (tx != null){
tx.Rollback();
tx =
null;}
throw;}
}
}
private void Start(){
// using (TransactionScope scope = new TransactionScope())
{
try{
cnn =
new SqlConnection("connection string");cnn.Open();
toContinue =
true;
tx = cnn.BeginTransaction(
"hello"); Thread.SetData(Thread.GetNamedDataSlot("tx"), tx); while (toContinue){
ExecuteCmd(
"Update A_TABLE set counter=counter+1 where test_id=1");}
//scope.Complete();tx.Commit();
}
catch (Exception){
if (tx != null){
tx.Rollback();
}
}
finally{
worker =
null;}
}
}
private void button2_Click(object sender, EventArgs e){
worker.Abort();
}
private void button3_Click(object sender, EventArgs e){
toContinue =
false;}