Transaction problem

[Marina]

I have some code that runs some updates/inserts inside a transaction. I
have logged the following message in my error log:

This SqlTransaction has completed; it is no longer usable. at
System.Data.SqlClient.SqlTransaction.Rollback()

The line number points to the 'trans.Rollback();' line.

So what this is saying - is that the transaction was committed - and then
there was an attempt to roll it back. However, as you see , trans.Commit is
the last line of code in the try block. So there is nothing else to have
caused an error. So it must have something in the Commit?

All the updates/inserts went through successfully, however. Even the message
says that the transaction has completed. So if the transaction was
committed - why was there an error that caused the catch to execute?

I haven't been able to reproduce this - but it happening once is a concern.
Can anyone shed light on this?

The code is below...


try{
conn.Open();
trans = conn.BeginTransaction();
command.Transaction = trans;
command.ExecuteNonQuery();
trans.Commit();
}
catch(Exception ex)
{

if (trans != null)
trans.Rollback();

}
finally
{
conn.Close();
}

 

[David Browne]

I have some code that runs some updates/inserts inside a transaction. I
have logged the following message in my error log:

This SqlTransaction has completed; it is no longer usable. at
System.Data.SqlClient.SqlTransaction.Rollback()

The line number points to the 'trans.Rollback();' line.

So what this is saying - is that the transaction was committed - and then
there was an attempt to roll it back. However, as you see , trans.Commit is
the last line of code in the try block. So there is nothing else to have
caused an error. So it must have something in the Commit?

All the updates/inserts went through successfully, however. Even the message
says that the transaction has completed. So if the transaction was
committed - why was there an error that caused the catch to execute?

I haven't been able to reproduce this - but it happening once is a concern.
Can anyone shed light on this?

The code is below...


try{
conn.Open();
trans = conn.BeginTransaction();
command.Transaction = trans;
command.ExecuteNonQuery();
trans.Commit();
}
catch(Exception ex)
{

if (trans != null)
trans.Rollback();

}
finally
{
conn.Close();
}

So what's the command do?
What exception did you catch?

Sounds like command.ExecuteNonQuery() threw an exception, and the command
commited or rolled back the transaction with a "COMMIT" or ROLLBACK in TSQL.
Or the error might have been so severe that the transaction was
automatically rolled back.

David

 

[Angel Saenz-Badillos[MS]]

Marina,
You are completelly correct in that this is a bug with the client side
transaction api,
http://support.microsoft.com/default.aspx?scid=kb;en-us;309335

The problem is that when you "purposefully cause an error" you are raising
such a high error that the entire transaction is rolled back and disposed by
the server.

 

[Marina]

My whole point was, that the transaction succeeded.

I checked the data in the database - and it was all there.

Additionally, the sql in the command only had insert and update statements -
there was no commit or rollback code. This was being done in the C# code.
If there was an error with the SQL - then the rollback in the code should
have taken care of rolling it back.

 

[Angel Saenz-Badillos[MS]]

Ah! I completelly missed that sorry about that.

Can you post the exception message and trace you are getting? When you call
commit we execute the commit transaction call and then we try to clean the
transaction object, if this fails you would get an exception rethrown after
the commit had actually happened. It sounds pretty thin to me though....
please also post what version of the urt you are using.

Thanks,