Two-phase transaction weirdness

[tomasz]

Hi,

I've been testing DevArt dotConnect for PostgreSQL and got into trouble with two-phase transactions.

After opening two connections to different PostgreSQL servers inside a TransactionScope:
1) TX is not promoted to distributed: never triggers TransactionManager.DistributedTransactionStarted event, and Transaction.Current.TransactionInformation.DistributedIdentifier remains empty
2) has no difference whether DTC is launched or not
3) if DTC is launched - its monitor never increases Completed and Failed transactions

Despite all this, the distributed transactions appear to be working: PostgreSQL log files actually contain required INSERTS and then PREPARE TRANSACTION / COMMIT PREPARED pairs. Furthermore, using the Suppress option for TransactionScope disables PREPARE TRANSACTION on servers.

It seems that dotConnect for PostgreSQL is implemented differently than SQL Server with regards to distributed transactions: no notifications about promotions reach the System.Transactions, no DTC involvement is required (?? maybe KVM is used instead? are commits really atomic?)

Is this behavior normal and expected or am I somehow using the feature wrong?

By the way, both PostgreSQL servers (9.2.2 x64) have max_prepared_transactions set to 100. Enlist=true is used in connection strings. Use of CommittableTransaction instead of TransactionScope changes nothing. dotConnect version is 6.2.132.0 Express.

The code (wrapper methods are used, but they are primitive and self-explanatory):

Code: Select all

TransactionManager.DistributedTransactionStarted += TransactionManagerOnDistributedTransactionStarted;

using (TransactionScope tc = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable, Timeout = new TimeSpan(0, 0, 1000) } ))
{
	PGSQLConnectionFactory sqlFact1 = new PGSQLConnectionFactory("127.0.0.1", 5432, "db_client", "user1", "passw1");
	DbConnection conn1 = sqlFact1.getInstance(true);
	DbCommand cmd1 = testDistributedTransactions_CreateInsertionCommand(conn1);
	
	string distTXID1 = Transaction.Current.TransactionInformation.DistributedIdentifier.ToString();

	PGSQLConnectionFactory sqlFact2 = new PGSQLConnectionFactory("192.168.1.112", 5432, "db_node", "user2", "passw2");
	DbConnection conn2 = sqlFact2.getInstance(true);
	DbCommand cmd2 = testDistributedTransactions_CreateInsertionCommand(conn2);
	
	string distTXID2 = Transaction.Current.TransactionInformation.DistributedIdentifier.ToString();

	cmd1.ExecuteNonQuery();
	cmd2.ExecuteNonQuery();

	conn1.Close();
	conn2.Close();
	
	tc.Complete();
}

Thanks for your input!

[Pinturiccio]

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

[Pinturiccio]

We don't support the TransactionManager class and notifications, as these features were introduced in .NET Framework version 4, and dotConnect for PostgreSQL is compiled for .NET Framework 2 as it supports .NET Framework 2 and 3.5.

We haven't found any information that Transaction.Current.TransactionInformation.DistributedIdentifier is somehow connected the provider's distributed transactions, and when working with the SQL Server provider, we have it equal to zero too.

Is this behavior normal and expected or am I somehow using the feature wrong?

This is a normal behavior. Distributed transaction works fine and a two-phase commit is performed on the server side. You can use the following event, that takes place when the transaction ends:

Code: Select all

Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(Current_TransactionCompleted);

[quooston]

So what on earth is coordinating the distributed transaction? Your provider?

[Pinturiccio]

Distributed transactions are coordinated by Microsoft Distributed Transaction Coordinator (MSDTC).