Npgsql 提供程序是否支持 TransactionScope? [英] Does Npgsql provider has support for TransactionScope?

问题描述

我正在尝试将 TransactionScope 与 Npgsql 提供程序一起使用.我在一个老问题(provider for PostgreSQL in .net with支持 TransactionScope)，而 Npgsql 尚不支持它.现在，大约 5 年后，Npgsql 是否支持 TransactionScope?我自己做了一个测试，使用 Npgsql 3.0.3 并使用以下代码:

I'm trying to use a TransactionScope with the Npgsql provider. I found in an old question (provider for PostgreSQL in .net with support for TransactionScope) that Npgsql didn't supported it yet. Now, after about 5 years, does Npgsql support TransactionScope? I made a test for myself, using Npgsql 3.0.3 and using the following code:

using (var scope = new TransactionScope())
{
    using(var connection = new Npgsql.NpgsqlConnection("server=localhost;user id=*****;password=*****database=test;CommandTimeout=0"))
    {
        connection.Open();

        var command = new NpgsqlCommand(@"insert into test.table1 values ('{10,20,30}', 2);");
        command.Connection = connection;
        var result = command.ExecuteNonQuery();

        // scope.Complete();  <-- Not committed
    }
}

谁能确认 Npgsql 不支持 TransactionScope?

Anyone can confirm that Npgsql doesn't support TransactionScope?

编辑 #1在确认 Npgsql 对 TransactionScope 的支持后，我发现你需要确保在你的 PostgreSQL 配置中启用了分布式事务，检查 postgres.conf 中的 max_prepared_transactions 参数 文件(记得重启服务器).

EDIT #1 After the confirmation of the support of Npgsql to the TransactionScope, I found that you need to be sure to haev Distribuited Transaction enabled in your PostgreSQL configuration, checking the max_prepared_transactions parameter in the postgres.conf file (remember to restart your server).

编辑 #2我在我的服务器上启用了分布式事务，但现在我在使用 TransactionScope 和 Npgsql 时遇到错误.这是我的代码:

EDIT #2 I enabled the Distribuited Transaction on my server but now I got an error using the TransactionScope with Npgsql. This is my code:

using (var sourceDbConnection = new NpgsqlConnection(SourceConnectionString))
using (var destinationDbConnection = new NpgsqlConnection(DestinationConnectionString))
using (var scope = new TransactionScope())
    {
        sourceDbConnection.Open();
        destinationDbConnection.Open();

        Logger.Info("Moving data for the {0} table.", TableName.ToUpper());
        var innerStopWatch = new Stopwatch();
        innerStopWatch.Start();

        foreach (var entity in entities)
        {
            var etlEntity = new EtlInfoItem
            {
                MigratedEntityId = category.RowId,
                ProjectId = category.ProjectId,
                ExecutionDatetime = DateTime.Now
            };

            // Insert into the destination database
            var isRowMigrated = InsertEntity(entity, DestinationSchema, destinationDbConnection);

            if (isRowMigrated)
            {
                // Update the ETL migration table
                InsertCompletedEtlMigrationEntity(etlEntity, EtlSchema, sourceDbConnection);
            }
            else
            {
                // Update the ETL migration table
                InsertFailedEtlMigrationEntity(etlEntity, EtlSchema, sourceDbConnection);
            }
        }

        Logger.Info("Data moved in {0} sec.", innerStopWatch.Elapsed);

        Logger.Info("Committing transaction to the source database");
                innerStopWatch.Restart();

        scope.Complete();

        innerStopWatch.Stop();
        Logger.Info("Transaction committed in {0} sec.", innerStopWatch.Elapsed);
    }

当 TransactionScope 从作用域中退出时(退出 using 语句时)，我得到一个空引用异常，并带有以下堆栈跟踪:服务器堆栈跟踪:在 Npgsql.NpgsqlConnector.Cleanup()在 Npgsql.NpgsqlConnector.Break()在 Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)在 Npgsql.NpgsqlConnector.ReadExpectingT…………它随机发生.

When the TransactionScope exits from the scope (when exiting the using statement), I get a Null Reference Exception with the following stack trace: Server stack trace: at Npgsql.NpgsqlConnector.Cleanup() at Npgsql.NpgsqlConnector.Break() at Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) at Npgsql.NpgsqlConnector.ReadExpectingT ......... It happens randomly.

推荐答案

Npgsql 确实支持 TransactionScope，并且已经这样做了很长时间.但是，至少目前，为了让您的连接参与 TransactionScope，您必须:

Npgsql does support TransactionScope and has done so for quite a while. However, at least for the moment, in order to have your connection participate in the TransactionScope you must either:

1. 在您的连接字符串中包含 Enlist=true，或
2. 调用 NpgsqlConnection.EnlistTransaction

看看 Npga> 围绕这一些例子.

Take a look at the Npgsql unit tests around this for some examples.

这篇关于Npgsql 提供程序是否支持 TransactionScope?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！