Hello,

We are currently adding support for PostgreSQL to an Adapter project of ours, that so far used Oracle. The project uses TransactionScope instead of explicit Transactions.

With Oracle, there is only one Enlistment into the TransactionScope (SD.LLBLGen.Pro.ORMSupportClasses.TransactionResourceManager), which will result in a Single-Phase Commit.

With PostgreSQL, there are 2 Enlistments (SD.LLBLGen.Pro.ORMSupportClasses.TransactionResourceManager + Npgsql.VolatileResourceManager), which will force a 2-Phase Commit where it has to synchronize and prepare the commits between the 2 ResourceManagers. With the default PostgreSQL config this fails with the following error:

PostgresException: 55000: prepared transactions are disabled

Is this the expected behavior and we are just forced to turn on prepared transactions in our Postgres DB? Putting Enlist=false into the connection string will remove the Npgsql manager, but then Rollbacks do not work anymore.

Code to produce the 55000 error:

        static void Main(string[] args)
        {
            RuntimeConfiguration.AddConnectionString("ConnectionString.PostgreSql (Npgsql)", "Host=localhost;Username=username;Password=password;Database=database");
            RuntimeConfiguration.ConfigureDQE<PostgreSqlDQEConfiguration>(c => c.AddDbProviderFactory(typeof(NpgsqlFactory)).AddSchemaNameOverwrite("public", "..."));

            using (var scope = new TransactionScope())
            using (var adapter = new VarioSL.Entities.Adapter.PostgreSql.DatabaseSpecific.DataAccessAdapter())
            {
                var test = new LinqMetaData(adapter).Client.ToList(); // Any query will do
                scope.Complete();
            }
        }

Version Info

• PostgreSQL: Docker Tag postgres:16.3
• Npgsql: 8.0.3 (tried all major versions going back to 4)
• SD.LLBLGen.Pro.DQE.PostgreSql: 5.10.1 (also tried latest 5.11.1)
• SD.LLBLGen.Pro.ORMSupportClasses 5.10.1 (also tried latest 5.11.1)

From what I gather, it works with prepared transactions, e.g. see: https://stackoverflow.com/questions/33588232/does-npgsql-provider-has-support-for-transactionscope.

To be honest, unless you have multiple processes participating in the same transaction (so a transaction scope an easy way to manage a transaction), I really wouldn't use transactionscopes. They're slow compared to typical ADO.NET transactions.

I'm not sure what else to recommend other than perhaps ask the Npgsql team what it can be. All we do is what's required for the TransactionScope to function which is to enlist transactions via our resourcemanager, which is basically a manager that will keep track of the transactions and participating objects and make sure that when the ambient transaction commits, the participating objects (like the entities) are properly made aware of that fact as if they were participating in a normal ADO.NET transaction. So the rest is up to the ADO.NET provider.

Yes, if we turn on prepared transactions on the database level, it does work. Would it be possible to make the enlistment of the LLBLGen TransactionResourceManager configurable? As far as we can tell, for both PostgreSQL and Oracle the transaction scope is already handled by their respective clients and then we could have the Single-Phase commit for PostgreSQL without turning prepared transactions on.

Regards, David

The transactionresourcemanager doesn't 'manage' the ambient transaction, it keeps track of all entities involved in the transactions that are part of the ambient transaction, and commits them all in-memory when the ambient transaction commits. So the ado.net transaction + database part is done by the ado.net provider, the transactionresourcemanager doesn't influence that. If you look at the source code of the TransactionResourceManager, it's really small, it basically makes sure the ADO.NET system can communicate with the adapters which contain the ADO.NET transactions to notify whether the ambient transaction was committed (so all entities participating have to be notified of that fact so they can sync fk/pk fields etc.) or rolled back.

If you have an ambient System.Transaction transaction going, and you open a connection and transaction it's automatically enlisted in the System.Transaction transaction by the ado.net provider.

Hi Otis,

thanks for the explanation. David and I indeed missed the the very important fact that the TransactionResourceManager notifies the entities and other about the outcome of the transaction.

What would be very nice though, if there were some kind of hook were we could override the behavior how TransactionBase creates the TransactionResourceManager and enlists it. For our case (using Postgres) it could be an idea to use the Transaction.TransactionCompleted event instead of a resource manager. This way we'd still have the notification on the transaction outcome but do not have additional enlistments, but only have one enlistment, the NPgsqlValatileResourceManger and therefore do not need to activate prepared transactions on the PG server. For Oracle however, we then would use the current behavior.

Such an overridable hook would be nice, but I'm not sure how much effort this would be and if it would be worth the effort.

Markus@Init wrote:

thanks for the explanation. David and I indeed missed the the very important fact that the TransactionResourceManager notifies the entities and other about the outcome of the transaction.

And you really need that, so you can't avoid that piece of the puzzle.

What would be very nice though, if there were some kind of hook were we could override the behavior how TransactionBase creates the TransactionResourceManager and enlists it. For our case (using Postgres) it could be an idea to use the Transaction.TransactionCompleted event instead of a resource manager. This way we'd still have the notification on the transaction outcome but do not have additional enlistments, but only have one enlistment, the NPgsqlValatileResourceManger and therefore do not need to activate prepared transactions on the PG server. For Oracle however, we then would use the current behavior.

I don't understand what you mean. the TransactionResourceManager is just an object which makes sure when the ambient, system.transactions transaction commits, all involved objects (i.e. the entities) are committed too and thus do their sync jobs. You can't replace that with something, it's there for a reason.

See it like this:

• you start an ambient transaction, so a System.Transaction transaction, e.g. by a transactionscope. This makes Transaction.Current have an instance, where Transaction is System.Transaction.
• you then start one or more ADO.NET transactions using adapter. An adapter instance in InitClassPhase2 creates a TransactionAdapter instance which is a derived class of TransactionBase and which in its ctor pulls the Transaction.Current instance as the 'systemTransaction' instance. If there's an instance, it'll create a new TransactionResourceManager for that adapter and will enlist itself on the ambient transaction by calling the Transaction.Current.EnlistVolatile() method.
• you do your work on the adapters, they commit the ADO.NET transactions like normally. When the adapter sees it's inside a system transaction, it'll notify the transaction outcome (commit) on its transactionresourcemanager by calling SetTransactionOutcomeVote(true) on the transactionresourcemanager. Committing a transaction on an adapter will raise the TransactionCommitted event on an adapter
• you then commit the System.Transaction. This will then call Commit on all the enlisted transactionresourcemanager instances (which implements IEnlistmentNotification) which in turn will call the NotifyCommit method on the TransactionBase contained in the transactionresourcemanager. This will do the notification of the elements participating in the ADO.NET transaction it represents. It will then raise the TransactionCommitted event which will make the adapter raise the TransactionCommitted event.

So you get an extra event at the end for the System.Transaction event.

I honestly have no idea what it is you want. The Npgsql issue is to my knowledge unrelated to this as it's the ADO.NET provider's job to make sure System.Transaction transactions are properly managed and enlisted together so they commit together. Code using the ADO.NET provider (so our code) has no influence on this: there's a System.Transaction live, and any ADO.NET transaction has to participate in that. That's the job of the ADO.NET provider, there's no code on our side which has any influence on that. All we do is basically add an object to the System.Transaction object to get notifications what's done and also to notify the System.Transaction our ADO.NET transaction was committed or rolled back, so the System.Transaction can decide what the overall outcome is.

Hey Otis, thanks for detailed answer.

Now one last attempt to explain what we mean:

You're right: it's mainly a problem with the Npgsql driver. The unnecessary elevation to 2-Phase-Commit transaction which happens with the Npgsql is explained in this issue and it seems it won't be changed/fixed soon.

I only want to explain a bit what we meant so that you understand our intentions:

MsSql/Oracle implements a durable resource manager which results in using Single-Phase-Commit for non-distributed transaction (nice). But as written in the issue, Npgsql's resource manager is a volatile resource manager and since LLBLGen enlists it's own volatile resource manager, we have >1 volatile enlistments which triggers a 2-Phase-Commit (not so nice, has disadvantages).

I understand and agree to the order of events you wrote. What we do not is: we do not use adapter.StartTransaction() or adapter.Commit()/adapter.Rollback()since there is no really value for us using them inside a transaction scope. We do only call transactionScope.Complete() (and Dispose after that) when all transactional stuff is done.

I understand that the LLBLGen ResourceManager is necessary for adapter.Commit()/adapter.Rollback() to work and to vote correctly for the ambient System.Transaction's outcome. The other purpose of the LLBLGen ResourceManager is to notify all transaction participants (Entities/Auditors/...) on the transaction commit/rollback. Did I forget something here or is this correct?

However since we don't use the adapter transaction methods in context of a transaction scope and we only want the commit/rollback notification, my thoughts were that we could avoid the volatile enlistment and it would suffice to use the TransactionCompleted event (see here) for the commit/rollback notification. That way the npgsql driver would only do a single-phase commit, which would be nice.

That being said, in the end it's probably a very specific problem and and not using the Commit()/Rollback() transaction vote functionality is also very specific to our code and lastly it is probably also not really bad if npgsql does a 2PC transaction. We are still new to Postgres and have to to check all new stuff and challenges and so on.

I hope you can understand our thinking a bit better. Thank you for your time

Markus@Init wrote:

You're right: it's mainly a problem with the Npgsql driver. The unnecessary elevation to 2-Phase-Commit transaction which happens with the Npgsql is explained in this issue and it seems it won't be changed/fixed soon.

Yeah that thread basically explains what was happening in .NET 1.x when SqlClient did the same thing (always escalate the transaction to a 2pc transaction)

I only want to explain a bit what we meant so that you understand our intentions:

MsSql/Oracle implements a durable resource manager which results in using Single-Phase-Commit for non-distributed transaction (nice). But as written in the issue, Npgsql's resource manager is a volatile resource manager and since LLBLGen enlists it's own volatile resource manager, we have >1 volatile enlistments which triggers a 2-Phase-Commit (not so nice, has disadvantages).

No, this is absolutely false. We don't enlist a resource manager at all. We enlist an object to receive notifications. The object we add to the System.Transaction isn't the same as the one managing the ADO.NET transactions. vonzshik's replies aren't correct till they looked into how SqlClient works.

With e.g. SqlClient, using 1 ADO.NET transaction doesn't go through the MSDTC services, but once you start another one both will then use the MSDTC. That is managed by SqlClient's manager. Npgsql simply always uses the MSDTC service (or equivalent), when an ADO.NET transaction is started and an ambient transaction is present.

I understand and agree to the order of events you wrote. What we do not is: we do not use adapter.StartTransaction() or adapter.Commit()/adapter.Rollback()since there is no really value for us using them inside a transaction scope. We do only call transactionScope.Complete() (and Dispose after that) when all transactional stuff is done.

You have to. A System.Transaction doesn't do anything 'transactional' on its own. Our framework starts its own ADO.NET transaction if one is needed and it's not started yet. E.g. if you do a unit of work commit, a recursive save, a save of multiple entities in a collection... an ADO.NET transaction is started and committed when the work is completed.

I understand that the LLBLGen ResourceManager is necessary for adapter.Commit()/adapter.Rollback() to work and to vote correctly for the ambient System.Transaction's outcome. The other purpose of the LLBLGen ResourceManager is to notify all transaction participants (Entities/Auditors/...) on the transaction commit/rollback. Did I forget something here or is this correct?

It's not needed for commit/rollback to work, as these work without it. It's needed for communication with the ambient transaction (so to signal the transaction was successful, so the ambient transaction knows what to do in the end when all ADO.NET transactions have been completed and receive signals when the ambient transaction has been completed).

However since we don't use the adapter transaction methods in context of a transaction scope and we only want the commit/rollback notification, my thoughts were that we could avoid the volatile enlistment and it would suffice to use the TransactionCompleted event (see here) for the commit/rollback notification. That way the npgsql driver would only do a single-phase commit, which would be nice.

1) we don't do any 'volatile enlistment', the ADO.NET transaction is enlisted by Npsql as volatile. 2) you have to use it, you need the ADO.NET transaction.

That being said, in the end it's probably a very specific problem and and not using the Commit()/Rollback() transaction vote functionality is also very specific to our code and lastly it is probably also not really bad if npgsql does a 2PC transaction. We are still new to Postgres and have to to check all new stuff and challenges and so on. I hope you can understand our thinking a bit better. Thank you for your time

With requests like this, it's always a bit like: what would you change in the runtime to make this all work in all cases you will run into? You have the sourcecode of the runtime, you can make changes if you want, see what happens. But I am pretty certain there's no change you can make that will make this work, simply because it's a problem in Npgsql and you can't control how that works.

So my advice is to look at why you need the System.Transaction instead. Unless you enlist other transaction types too, it might very well be you are better off with collecting work in a Unit of Work and use an ADO.NET transaction (and let the framework take care of that, simply commit the unit of work! it knows what to do)