TransactionScope: nested transactions with different database connections (SQL Server & Postgresql)

Question

I am writing an SDK method with transaction using NpgsqlConnection for others to use.

When they were calling my method, they used SqlConnection with another transaction to wrap their DB stuff and my SDK's DB stuff.

If I set my SDK method without a transaction, the outer code was fine and my SDK method could be rolled back. (Which was odd too. Still figuring out why.)

If I set my SDK method with a transaction though, the outer code crashed with a TransactionAbortedException:

System.Transactions.TransactionAbortedException : The transaction has aborted.
---- Npgsql.PostgresException : 55000: prepared transactions are disabled

Currently we're using enlist=false at the SDK's connection string to prevent the inner transaction from joining the outer one but I'd like to know the reason behind this behavior.

Here's the code I'm reproducing the problem with:

using (var scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
    IsolationLevel = IsolationLevel.ReadCommitted,
},
TransactionScopeAsyncFlowOption.Enabled))
{
    await using (var conn = new SqlConnection(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0"))
    using (var cmd = new SqlCommand("insert into [Test].[dbo].[Test] (Id, \"Name\") values (1, 'A')", conn))
    {
        await conn.OpenAsync();
        var result = await cmd.ExecuteNonQueryAsync();

        await SdkMethodToDoStuffWithNpgsql(1);

        scope.Complete();
    }
}

I had SdkMethodToDoStuffWithNpgsql() to mock a method in a repository with Postgres context injected.

public async Task SdkMethodToDoStuffWithNpgsql(long id)
{
    var sqlScript = @"UPDATE test SET is_removal = TRUE WHERE is_removal = FALSE AND id = @id;
                    INSERT INTO log(id, data) SELECT id, data FROM log WHERE id = @id";

    using (var scope = new TransactionScope(
        TransactionScopeOption.RequiresNew,
        new TransactionOptions
        {
            IsolationLevel = IsolationLevel.ReadCommitted,
        },
        TransactionScopeAsyncFlowOption.Enabled))
    {
        await using (var conn = new NpgsqlConnection(this._context.ConnectionString))
        {
            await conn.OpenAsync();
            using (var cmd = new NpgsqlCommand(sqlScript, conn))
            {

                cmd.Parameters.Add(new NpgsqlParameter("id", NpgsqlDbType.Bigint) { Value = id });
                await cmd.PrepareAsync();
                var result = await cmd.ExecuteNonQueryAsync();

                if (result != 2)
                {
                    throw new InvalidOperationException("failed");
                }

                scope.Complete();
            }
        }
    }
}

Answer 1

The above is the expected behavior - enlisting two connections in the same TransactionScope triggers a "distributed transaction"; this is known in PostgreSQL terminology as a "prepared transaction", and you must enable it in the configuration (this is the cause of the error you're seeing above). If the intention is to have two separate transactions (one for SQL Server, one for PostgreSQL) which commit separately, then opting out of enlisting is the right thing to do. You should also be able to use TransactopScopeOption.Suppress.

Note that distributed transactions aren't currently supported in .NET Core, only in .NET Framework (see this issue). So unless you're on .NET Framework, this won't work even if you enable prepared transactions in PostgreSQL.