TransactionScope and Prepared Transactions

[mpearse]

Hi there,

We have noted some interesting behaviour using TransactionScope that'd we'd like to clarify.

Basically we are seeing every transaction being committed as a prepared transaction even if all the activity within TransactionScope was on a single connection, even if just a single write for example.

As in:

Code: Select all

2014-12-09 15:38:25 EST LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED
2014-12-09 15:38:25 EST LOG:  execute PRSTMT19624403211663369718: UPDATE namespacename.table
	   SET column = $1
	 WHERE id = $2
2014-12-09 15:38:25 EST DETAIL:  parameters: $1 = 'f', $2 = '3bcf0062-eadb-11e3-9ef5-f7d01a999061'
2014-12-09 15:38:25 EST LOG:  statement: PREPARE TRANSACTION '1555c823-daa3-42b5-b5e2-42f06aa5e3e2'
2014-12-09 15:38:25 EST LOG:  statement: COMMIT PREPARED '1555c823-daa3-42b5-b5e2-42f06aa5e3e2'

We have enlist at the default of true. Transactions seem not to work with it false?

Additionally, rarely (but of high impact when it does happen), we have seen a prepared transactions being created but not committed, leaving a table locked until we manually release the prepared transaction. Not really sure how this could be happening given the PREPARE TRANSACTION and COMMIT PREPARED all spit out at once.
Basically we then start piling up connections til the entire db becomes locked.

I'm aware that Transaction Scope Local=true; will turn off prepared transactions with TransactionScope, but we do have a few instances in the future that we will want them. It seems odd that TransactionScope is so coupled to prepared transactions. Is this behaviour expected?

Also any ideas how we would potentially end up with an orphaned prepared transaction given the above simple update?

Thanks.

[Shalex]

Basically we are seeing every transaction being committed as a prepared transaction even if all the activity within TransactionScope was on a single connection, even if just a single write for example.

Promotable Transaction Scenario is not supported in dotConnect for PostgreSQL. We will investigate the question and notify you about the result.

We have enlist at the default of true. Transactions seem not to work with it false?

If the "Enlist=false;" connection string option is used, the connection is not automatically enlisted in the current transaction context.

Additionally, rarely (but of high impact when it does happen), we have seen a prepared transactions being created but not committed, leaving a table locked until we manually release the prepared transaction. Not really sure how this could be happening given the PREPARE TRANSACTION and COMMIT PREPARED all spit out at once.

Take into account the following points:
a) the PgSqlConnection object is not thread safe. You should avoid using the same PgSqlConnection in several threads at the same time
b) implement a try..catch..finally logic to put transaction.Rollback() and conn.Close in finally block
If this doesn't help, please localize the issue and send us a small test project with the corresponding DDL/DML script for reproducing.