[Enhancement]: .NET TransactionScope

[spencercw]

Contact Details

@spencercw

What this feature/enhancement tries to solve?

In our application we make use of TransactionScope to have our SQL commands implicitly join the ambient transaction; however, this results in a RESETCONSKIPTRAN is not supported error when the transaction is disposed. Explicit transactions work ok, but would require us to update every query in the application to manually specify the transaction, which obviously is undesirable.

Example code below. .NET Core 3.1. System.Data.SqlClient 4.8.3.

using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using IsolationLevel = System.Transactions.IsolationLevel;

namespace TransactionTest
{
    class Program
    {
        static void Main()
        {
            // This is ok
            RunWithExplicitTransaction();

            // This does not work
            // Unhandled exception. System.Transactions.TransactionInDoubtException: The transaction is in doubt.
            //  ---> System.Data.SqlClient.SqlException (0x80131904): RESETCONSKIPTRAN is not supported
            RunWithTransactionScope();
        }

        private static void RunWithExplicitTransaction()
        {
            using (var connection = NewConnection())
            {
                connection.Open();
                using var transaction = connection.BeginTransaction();
                var cmd = connection.CreateCommand();
                cmd.Transaction = transaction;
                cmd.CommandText = "select 1";
                cmd.ExecuteReader().Dispose();
                transaction.Commit();
            }
        }

        private static void RunWithTransactionScope()
        {
            using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions
            {
                IsolationLevel = IsolationLevel.ReadCommitted
            }))
            using (var connection = NewConnection())
            {
                connection.Open();
                var cmd = connection.CreateCommand();
                cmd.CommandText = "select 1";
                cmd.ExecuteReader().Dispose();
                transaction.Complete();
            }
        }

        private static IDbConnection NewConnection()
        {
            return new SqlConnection("Server=127.0.0.1;Database=test;User Id=babelfish;Password=babelfish;");
        }
    }
}

Thanks.

If want to provide us a more details about how to implement.

No response

Version

babelfishpg_tds

Relevant documentation

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[kuntalghosh]

We've mapped RESETCONNECTION to PG's DISCARD ALL feature - https://www.postgresql.org/docs/devel/sql-discard.html. But, with RESETCONNECTIONSKIPTRAN, one can reset the connection but do not modify the transaction state. Since PG doesn't have such implementation, we've not implemented the same yet. But, it'll be a good enhancement.

[alexey-tyulkin]

npgsql supports local and 2 phase commit transactions in TransactionScope https://github.com/npgsql/npgsql/blob/30ba2ddd614972c72d8d6997c8fc38ee7c4ea517/src/Npgsql/VolatileResourceManager.cs#L149

Can you fix TransactionScope because it's common pattern in enterprise c# legacy application?