No distributed transaction on net462

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
EvilShrike
Posts: 19
Joined: Mon 14 Mar 2016 17:11

No distributed transaction on net462

Post by EvilShrike » Tue 12 Dec 2017 13:29

Devart.Data.PostgreSql: 7.10.1031
Tried Postgres 9.6 and 10.0.
Test case:

Code: Select all

    	static Task Test_pgsql_distributed_transaction()
    	{
    		string conStr = "..;enlist=true;";
    		using (var tx = new TransactionScope())
    		{
    			var con1 = new Devart.Data.PostgreSql.PgSqlConnection();
    			var con2 = new Devart.Data.PostgreSql.PgSqlConnection();
    			con1.ConnectionString = conStr;
    			con1.Open();
    			con2.ConnectionString = conStr;
    			con2.Open();

    			var cmd1 = con1.CreateCommand();
    			cmd1.CommandText = "select 1";
    			var cmd2 = con2.CreateCommand();
    			cmd2.CommandText = "select 2";

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

    			Console.WriteLine($"DistributedIdentifier: {Transaction.Current.TransactionInformation.DistributedIdentifier}");
    			tx.Complete();

    			con1.Close();
    			con2.Close();
    		}
    	}
build for net462
Output: 00000000-0000-0000-0000-000000000000
Also checked via PerfMon, there is no DTC transactions.
If change "Devart.Data.PostgreSql.PgSqlConnection" to "System.Data.SqlClient.SqlConnection" then a distributed transaction will be created and commited (a guid will be output).

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

Re: No distributed transaction on net462

Post by Shalex » Thu 14 Dec 2017 19:29

dotConnect for PostgreSQL provides distributed transaction functionality using the following PostgreSQL Server feature: support for two-phase commit via prepared transactions.

System.Data.SqlClient implements distributed transactions via Distributed Transaction Coordinator.

EvilShrike
Posts: 19
Joined: Mon 14 Mar 2016 17:11

Re: No distributed transaction on net462

Post by EvilShrike » Wed 20 Dec 2017 12:08

Does it mean that Devart provider for PostgreSql doesn't support System.Transactions at all (ignoring ambient transactions TransactionScope/Transaction)?

Also it's unclear what does EnlistTransaction method do then?
It doesn't throw (it's good), but Transaction seems not to start.

Code: Select all

    	using (var tx = new TransactionScope())
    	{
    	        var con1 = new Devart.Data.PostgreSql.PgSqlConnection();
    	        var con2 = new Devart.Data.PostgreSql.PgSqlConnection();
            	con1.ConnectionString = conStrPg;
            	con2.ConnectionString = conStrPg;
            	con1.Open();
            	con2.Open();
            	con1.EnlistTransaction(Transaction.Current);
            	con2.EnlistTransaction(Transaction.Current);
            	tx.Complete();

            	con1.Close();
            	con2.Close();
    	}

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

Re: No distributed transaction on net462

Post by Shalex » Thu 21 Dec 2017 11:07

EvilShrike wrote:Does it mean that Devart provider for PostgreSql doesn't support System.Transactions at all (ignoring ambient transactions TransactionScope/Transaction)?
dotConnect for PostgreSQL supports TransactionScope.
EvilShrike wrote:Also it's unclear what does EnlistTransaction method do then?
It enlists connection in a current distributed transaction.

The difference between Devart.Data.PostgreSql and System.Data.SqlClient is only internal provider implementation. Logic of using TransactionScope in the code should be the same.

EvilShrike
Posts: 19
Joined: Mon 14 Mar 2016 17:11

Re: No distributed transaction on net462

Post by EvilShrike » Fri 22 Dec 2017 10:48

The difference between Devart.Data.PostgreSql and System.Data.SqlClient is only internal provider implementation. Logic of using TransactionScope in the code should be the same.
Ok, great.
By "dotConnect for PostgreSQL supports TransactionScope" you meant it supports distributed transactions via DTC right?

Then let me to return to the point I started the topic with - in the provided test-case distributed transaction has zero id (00000000-0000-0000-0000-000000000000) and no traces of DTC-transaction in PerfView:
Also checked via PerfMon, there is no DTC transactions.
If change "Devart.Data.PostgreSql.PgSqlConnection" to "System.Data.SqlClient.SqlConnection" then a distributed transaction will be created and commited (a guid will be output).
So if dotConnect for PostgreSQL supports DTC then we should see DTC transactions. But we don't
Do I miss something?

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

Re: No distributed transaction on net462

Post by Shalex » Wed 27 Dec 2017 20:14

A provider should avoid escalating to MSDTC if possible (because it consumes a lot of resources): https://stackoverflow.com/questions/169 ... e-machines.

dotConnect for PostgreSQL avoids escalating to MSDTC using the prepared transactions feature of PostgreSQL Server.

Post Reply