Why is failed transaction not rolling back with TransactionScope?

Question

What I'm trying to achieve is: I'm calling stored procedures from 2 different databases. SP's have a simple insert entry in a table. There is no problem if both the transactions are successful, But when I tried to throw an exception in the 2nd DB SP the first one doesn't rollback. What am I doing wrong here?

C# Code:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
    // Update DB1
    using (SqlConnection con = new SqlConnection(connectionString1))
    {
        con.Open();
        SqlCommand command1 = new SqlCommand("sp_1", con);
        command1.ExecuteNonQuery();

        // Update DB2 
        using (SqlConnection con2 = new SqlConnection(connectionString2))
        {
            con2.Open();
            SqlCommand command2 = new SqlCommand("sp_2", con2);
            command2.ExecuteNonQuery();
        }
    }
    scope.Complete();
}

DB1 SP :

BEGIN
    INSERT INTO TABLE_X1 VALUES(...)
END

DB2 SP :

BEGIN
    THROW 51000, 'The record does not exist.', 1; 

    INSERT INTO TABLE_X2 VALUES(...)
END

Transactions are defined by the connection. When you include multiple connections into a transaction, you are including multiple database connections, as long as the connection strings are different. When multiple connections are employed, the transaction will escalate to a distributed transaction in which case you need to make sure your server supports distributed transactions. — Ross Bush, Aug 09 '20 at 03:35

score 1 · Answer 1 · answered Aug 09 '20 at 06:18

You need to use TransactionScopeOption.Required option to enable transaction.

If both databases are on the same SQL server, then use the same connection to avoid triggering distributed transactions (if the connection string is exactly the same though, this should not happen).

TransactionScope: Avoiding Distributed Transactions

If you would to utilize transaction and if the databases are on different servers (or different instances on the same server), then there is no way to avoid the distributed transactions. In that case, enable MSDTC, see details here

https://www.dbrnd.com/2016/11/sql-server-how-to-configure-and-enable-msdtc-microsoft-distributed-transaction-coordinator/

score 0 · Answer 2 · answered Aug 09 '20 at 03:34

By specifying TransactionScopeOption.Suppress as a constructor argument to the TransactionScope, you are opting to not have any transaction.

If the scope is instantiated with Suppress, it never takes part in a transaction, regardless of whether an ambient transaction is present. A scope instantiated with this value always have [sic] null as its ambient transaction.

https://learn.microsoft.com/en-us/dotnet/framework/data/transactions/implementing-an-implicit-transaction-using-transaction-scope

Presumably this option is intended for times when you know that someone else may have started a transaction before calling into your method, but you know that what you do in your method needs to be done outside of a transaction.

Since it sounds like you want to make sure the transaction rolls back, you probably want to use Required (the default, if you omit the argument), or possibly RequiresNew.

If I try with Required or RequiresNew in the argument, I get an exception saying 'This platform does not support distributed transactions'. — Code Wines, Aug 09 '20 at 04:02
check it here how you can deal with or avoid distributed transactrions https://stackoverflow.com/questions/3187632/transactionscope-avoiding-distributed-transactions — K4M, Aug 09 '20 at 06:05

Why is failed transaction not rolling back with TransactionScope?

2 Answers2