ADO .NET transaction scope locks transaction even after stopped

Question

I have implemented transaction scope in a Web API application. It works fine when i have to commit all or rollback all. But sometimes while debugging, if i stop the service before transaction is completed, then the transaction is getting locked. The table is still showing as locked and that particular session remains in database even after closing the visual studio. This happens sometime and that partcular transaction is not getting rolledback. I have also set timeout for the transactionscope. Still the issue is happening. Is there anyway to abort the table operation immediately after the servce is stopped inbetween?

Answer 1

Do you need to use TransactionScope? If not, you can look into using the SqlTransaction which is tied to the connection.

Details

If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

using (SqlConnection cn = new SqlConnection("YOUR CONNECTION")) 
{
    cn.Open();
    using (SqlTransaction tr = cn.BeginTransaction()) 
    {
        // your transactional code
        tr.Commit();
    }
}