Two-phase transaction weirdness

Two-phase transaction weirdness

Postby tomasz » Fri 18 Jan 2013 11:41

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!
tomasz
 
Posts: 1
Joined: Fri 18 Jan 2013 11:04

Re: Two-phase transaction weirdness

Postby Pinturiccio » Wed 23 Jan 2013 13:41

We are investigating the issue and will notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Two-phase transaction weirdness

Postby Pinturiccio » Thu 24 Jan 2013 14:42

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.

tomasz wrote: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);
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Two-phase transaction weirdness

Postby quooston » Fri 06 Feb 2015 02:31

So what on earth is coordinating the distributed transaction? Your provider?
quooston
 
Posts: 5
Joined: Tue 05 Mar 2013 00:19

Re: Two-phase transaction weirdness

Postby Pinturiccio » Mon 09 Feb 2015 16:18

Distributed transactions are coordinated by Microsoft Distributed Transaction Coordinator (MSDTC).
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL