TransactionAbortedException Occurs Randomly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 23 Jul 2008 20:41 GMT

Somewhere around the 50th time the method shown below is called a
TransactionAbortedException is thrown.
The inner exception is "The requested operation cannot be completed because
the connection has been broken".
Does anyone know what would cause this to happen?

protected void Insert()
{
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.RequiresNew))
{
using (SqlConnection connA = new SqlConnection(connStringA))
{
using (SqlConnection connB = new SqlConnection(connStringB))
{
connA.Open();
connB.Open();

// Call table adapters Update
taTableA.Update(myDataTableA);
taTableB.Update(myDataTableB);
taTableC.Update(myDataTableC);

scope.Complete();

connB.Close();
connA.Close();
}
}
}
}

Reply to this Message

Andrew J. Kelly - 23 Jul 2008 22:18 GMT

It appears as if you are starting a new transaction with each loop. Why are
you doing that? That would nest all the transactions and eventually I would
think you would hit a limit although I don't know what that is.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Somewhere around the 50th time the method shown below is called a
> TransactionAbortedException is thrown.
[quoted text clipped - 28 lines]
> }
> }

Reply to this Message

Scott - 24 Jul 2008 13:24 GMT

I don't understand what you mean. What loop are you reffering to?
There is no loop in the code snippet that nests transactions.
scope.Complete() is called followed by exiting the "using" statement for the
TransactionScope which then exits the method.
The method Insert() is not called in a tight loop either.

> It appears as if you are starting a new transaction with each loop. Why are
> you doing that? That would nest all the transactions and eventually I would
> think you would hit a limit although I don't know what that is.

Reply to this Message

Andrew J. Kelly - 24 Jul 2008 15:13 GMT

I was assuming you were calling the INSERT() method 50 times, is that not
the case? I was also assuming the TransactionScope scope = new line of code
started an Explicit transaction. But what I didn't catch the first time is
that you also have this at the end of the method scope.Complete();. I
don't know what that does but now I again assume it issues a COMMIT TRAN. If
these assumptions are correct each call should begin a tran, Issue the
updates and then commit the tran. Normally that would be fine and there
should be no issues. But if you issued a Begin tran before you called the
first Insert() all of those would be wrapped in one big nested transaction
with none of the inner ones actually committing until the outmost one is
committed. If you have Implicit Transactions turned on for that connection
you might see this behavior. I would run a trace at the statement level to
see what is going on. Also if you select @@TRANCOUNT in your Insert() method
you can see if the level is where you expect it to be. One other note is
that since you appear to be updating thru two connections at the same time
you are most likely also wrapped in a distributed transaction as well.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

>I don't understand what you mean. What loop are you reffering to?
> There is no loop in the code snippet that nests transactions.
[quoted text clipped - 8 lines]
>> would
>> think you would hit a limit although I don't know what that is.

Reply to this Message

Scott - 24 Jul 2008 19:50 GMT

There is no begin transaction call pior to calling the Insert() method.
This code was written in C# using SMO (SQL Manager Objects).
The transaction begins when the first line is called:
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.RequiresNew))

I did neglect to mention that the Insert method uses the C# key word "lock"
at the beginning of the method which is used to ensure that the block of code
runs to completion without interruption by other threads.

> I was assuming you were calling the INSERT() method 50 times, is that not
> the case? I was also assuming the TransactionScope scope = new line of code
[quoted text clipped - 12 lines]
> that since you appear to be updating thru two connections at the same time
> you are most likely also wrapped in a distributed transaction as well.

Reply to this Message

Andrew J. Kelly - 24 Jul 2008 20:03 GMT

I would still verify that SMO or the driver is not doing an Implicit Begin.
You won't see the BEGIN TRAN in the code or the trace but you may see the
SET IMPLICIT TRANS ON command when it connects if you are tracing the
correct events. You can also verify the @@TRANCOUNT levels at the various
states to make sure as well.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> There is no begin transaction call pior to calling the Insert() method.
> This code was written in C# using SMO (SQL Manager Objects).
[quoted text clipped - 32 lines]
>> time
>> you are most likely also wrapped in a distributed transaction as well.

Reply to this Message

Scott - 24 Jul 2008 13:36 GMT

Note: Added a second call to a method which cascades the transaction.
Not sure if this would be a problem but thought it was worth mentioning.

protected void Insert()
{
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.RequiresNew))
{
using (SqlConnection connA = new SqlConnection(connStringA))
{
using (SqlConnection connB = new SqlConnection(connStringB))
{
connA.Open();
connB.Open();

// Call table adapters Update
taTableA.Update(myDataTableA);
taTableB.Update(myDataTableB);
taTableC.Update(myDataTableC);

SaveSecond(connection); // Cascades transaction

scope.Complete();

connB.Close();
connA.Close();
}
}
}
}

public bool SaveSecond(SqlConnection connection)
{
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.Required))
{
tableAdapterA.Connection = connection;
tableAdapterB.Connection = connection;
tableAdapterC.Connection = connection;

tableAdapterA.Update(myDataTableA);
tableAdapterB.Update(myDataTableB);
tableAdapterC.Update(myDataTableC);

scope.Complete();
}
}

Reply to this Message