Friday, July 20, 2012

Transaction got aborted ,WHY?

Transaction Exception: The operation is not valid for the state of the transaction.


There may be different reasons for transaction exception in distributed transaction when we are using TransactionScope in our code :-

1. Transaction may get timed out and can cause this type of exception.So, when facing this type of exception in our code we should first check whether the transaction can be completed in specified time or not. If not then we should increase the transaction time .
Options to increase the transaction time out can be easily find out by going to msdn sites.

2. Majority of time we got this type of exception when we are using TransactionScope and inside that TransactionScope ,we are opening some sql connection and executing some sql query.
 using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        using(SQLServer Sql = new SQLServer(this.m_connstring))
        {
            //code for sql query .
        }
    }

There may be couple of reason to get this type of error in such situations:-
  • Whenever we are calling some sql procedure and in sql procedure we might be using transaction due to which when any exception occurs in procedure then transaction gets rolled back and when we try to do other stuff inside the transaction Scope then we will get this error as transaction is already rolled back and we are trying to do operation using that invalid transaction.
We might not be able to find out the cause of this error on code side when exception occurs in database side whenever we use sqlreader in our code in try ,catch block (and in catch block we are eating exception).
So, beware and try to lookout for SQL reader in code when you face any such exception and then check stored procedure whether its throwing any exception or not.

You might also look for transaction log,which system  generates for any distributed transaction.Since its not in scope of this post ,I am not talking about that in this post.

I hope this post can be helpful to you guys when you face this type of transaction exception.