Npgsql, TransactionScope and prepared transaction [closed]

Question

Can someone say can I use npgsql with TransactionScope?

Code that provided as example in manual here: http://npgsql.projects.postgresql.org/docs/manual/UserManual.html doesn't work. It's just creates two prepared transactions on server.

I have same problem as in this question: TransactionScope and Npgsql - Prepared Transaction Issue

Is there any solution for using npgsql with TransactionScope?

UPD: First of all my target: I need to use two connections in single logical transction in my software. The best solution for this is TransactionScope. Npgsql declare support of enlistment in system transaction.

Then goes my trouble: I use code from documentation, this code do next things:

1. Create transaction scope
2. Open first connection
3. Open second connection
4. Do insert into first connection
5. Do insert into second connection
6. Close second connectin
7. Close first connection
8. Call scope.Complete()
9. Dispose scope

I wait, that after 9 step data will be fully commited to db. In fact at steps 6 and 7 npgsql creates prepared transaction and at step 9 do nothing. Finaly i have two prepated transaction, that block database. And there is nobody for commit or rollback them.

Generaly if i close connection before call complete on scope (for example becouse of the exception) npgsql creates prepared transaction that block table. I think it's not rigth behavior. I wait that after scope dispose data will be fully commited or fully rollbacked. With out any prepared transaction.

You can repeat this bug with code from documentation. For reference Devart free library work correct.

Answer 1

There are three separate concepts which need to be clear before discussing an answer.

1. There are prepared statements, which are associated with a connection, and provide a "framework" statement into which new values can be plugged for each execution, without having to parse and plan the statement each time. The up side is that you save the CPU time involved in parsing and planning the statement on each execution. The down side is that the planning is done based on the best "generic" plan, which is not likely to be the best plan for all values which could be assigned. Prepared statements are created by the NpgsqlCommand.prepare() method.

2. There are transactions, which are associated with a connection and provide a context for a set of related database activities. All activity of one transaction is in one database of one server. Transactions are created by the NpgsqlConnection.BeginTransaction() method.

3. There are prepared transactions, which use two phase commit (2PC) to manage multiple transactions across multiple connections, possibly on different connections, different databases, different servers, or using entirely different server products as one logical "distributed transaction". Use of this feature should always be handled by a high-quality transaction manager product, generally one which is not part of any of the databases participating in the distributed transactions. The goal is that when all the related transactions are done with their work, the transaction manager tells each to "prepare" for commit. Each will try to do all the things which might cause it to fail before responding to the transaction manager with an indication of success or failure. If all transactions succeed in the "prepare" phase, the transaction manager will then tell each of them to commit. A prepared transaction must be able to commit at some later time when the transaction manager requests it, even if the server crashes immediately after the success of the "prepare" phase has been communicated to the transaction manager. It appears that there is support for creating your own transaction manager with the TransactionScope constructor. I strongly recommend against trying to create your own ad hoc transaction manager. A transaction manager is a complex and subtle piece of software which is very hard to get right, and can have severe negative consequences if not done right.

It is my impression that the intent in the question is to use the first two features, but not the third. If this is true, the wrong methods are being used to manage transactions.

In the referenced documentation page there are examples of using transactions and prepared statements in a reasonable way. The feature called System.Transactions, which uses the TransactionScope class does not seem to be needed for what you want to do, and seems to be what is getting you into trouble. Look at the examples which just use conn.BeginTransaction().