Allow local transaction to enlist in TransactionScope

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
tylerburd
Posts: 3
Joined: Mon 28 Sep 2009 23:24

Allow local transaction to enlist in TransactionScope

Post by tylerburd » Thu 01 Oct 2009 05:39

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 02 Oct 2009 16:35

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

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 07 Oct 2009 12:24

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.

Post Reply