Allow local transaction to enlist in TransactionScope

[tylerburd]

I want to start using TransactionScope in an existing project. This project already makes heavy use of local transactions, and when I try to open a local transaction within a TransactionScope dotConnect throws an error, saying this is an invalid operation. This is contradictory to other db drivers, like Npgsql or the built in .Net drivers for SQL Server.

This definitely prevents us from implementing this new feature in existing projects. Can this be fixed?

Here is a simple example of the problem. When conn.BeginTransaction() is called an error is thrown.

Code: Select all

var conn = new PgSqlConnection("...");
using (var scope = new TransactionScope())
{
	conn.Open();

	using (var localTx = conn.BeginTransaction())
	{
		var cmd = conn.CreateCommand();
		cmd.CommandText = "INSERT INTO TestTable (1)";
		cmd.Transaction = localTx;

		localTx.Commit();
	}
	
	scope.Complete();
}

[Shalex]

We will investigate the issue and notify you about the results as soon as possible.

[Shalex]

This is a designed behaviour. dotConnect for PostgreSQL doesn't allow to work with local transaction in scope of distributed transaction because of the PostgreSQL server implementation: if the local transaction is commited/rolled back, the whole ambient distributed transaction will be completed/rolled back as well. We think this situation is incorrect, so we have disabled enlisting local transaction in TransactionScope.

You can do that with System.Data.SqlClient for SQL Server, because SQL Server handles this issue: commit/roll back of local transaction does not influence ambient transaction.

We have tried Npgsql. It seems like this driver doesn't react on using TransactionScope at all and commits the changes immediately.