TransactionScope not working anymore...

TransactionScope not working anymore...

Postby Feneck91 » Mon 08 Feb 2016 08:46

I'm using TransactionScope for several months with EntityFramework Code First / devart Oracle and PostgreSQL.

All working fine but last week, I have tryed to make a Rollback transaction : it failed, the data are written into database.

The code is like :
Code: Select all
            using (var scope = new System.Transactions.TransactionScope())
            {
                var codeVersion = new EntityCodesVersion();
                codeVersion.DateImport = DateTime.Now;
                codeVersion.Version = "8.0";

                using (DatabaseManagerDbContext context = ADatabaseManagerFactory.Instance.CreateDatabaseManagerDbContext())
                {
                    context.CodesVersions.Add(codeVersion);
                    bool bOKSave = context.SaveChanges() > 0;
                }
                scope.Complete();
            }

Explanation :
  1. Editing postgresql.conf and activate option max_prepared_transactions to 5
  2. If I put PgSqlConnectionStringBuilder.Enlist = false :
    When context.SaveChanges() is run, the EntityCodesVersion record is written into database. It should not be written into database because It is done into a TransactionScope scope !
    So, the scope.Complete() done nothing more because the record is already written into database.
  3. If I put PgSqlConnectionStringBuilder.Enlist = true : Two things :
    1. When open trigger on table notifications, I have exception :
      Exception (TransactionAbordedException) The transaction has been interrupted.
      1.inner Exception : cannot execute PREPARE on transaction that have execute LISTEN, UNLISTEN or NOTIFY.
    2. When SaveChange is executed : the record is not written into database (normal way), when scope.Complete is executed, nothing is done (not normal, it must be written into database). When goes out of the transaction, an exception is raised : TransactionAbordedException with same error.
I have tried to make Connection.EnlistTransaction(System.Transactions.Transaction.Current); but same problems.

Blocked by this problem for 2 days, no way to make transaction work fine !
Because I'm using CodeFirst, I use several connection at the same time (always one new connection on each call of database providers when adding / removing / update record from/to the database.).

I read this topics but it don't help me :
http://stackoverflow.com/questions/28191333/transactionscope-error-in-ambient-transaction-does-not-rollback-the-transaction
http://forums.devart.com/viewtopic.php?f=3&t=31196
http://stackoverflow.com/questions/934316/is-there-a-way-to-use-transactionscope-with-an-existing-connection

All worked fine for Oracle database...

Any idea ?

PS : Version = 7.4.543.0 / DbMonitor = 3.0.4
Database : PostgreSQL 9.4 + posgis.
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: TransactionScope not working anymore...

Postby Shalex » Tue 09 Feb 2016 16:01

Feneck91 wrote:2. If I put PgSqlConnectionStringBuilder.Enlist = false :
When context.SaveChanges() is run, the EntityCodesVersion record is written into database. It should not be written into database because It is done into a TransactionScope scope !
So, the scope.Complete() done nothing more because the record is already written into database.
This is a designed behaviour, because the context connection is not automatically enlisted in a current transaction in this case.

Feneck91 wrote:3. If I put PgSqlConnectionStringBuilder.Enlist = true : Two things :
  1. When open trigger on table notifications, I have exception :
    Exception (TransactionAbordedException) The transaction has been interrupted.
    1.inner Exception : cannot execute PREPARE on transaction that have execute LISTEN, UNLISTEN or NOTIFY.
  2. When SaveChange is executed : the record is not written into database (normal way), when scope.Complete is executed, nothing is done (not normal, it must be written into database). When goes out of the transaction, an exception is raised : TransactionAbordedException with same error.
I have tried to make Connection.EnlistTransaction(System.Transactions.Transaction.Current); but same problems.
Please send us a small test project with the corresponding DDL/DML script so that we can reproduce the issue in our environment.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: TransactionScope not working anymore...

Postby Feneck91 » Fri 12 Feb 2016 10:25

I found why this is not working !
  • I use triggers to be notified when the database is changed. The triggers use NOTIFY command to notify changes.
  • I use TransactionScope to make transactions.

But : to make it work, I must :
  • PgSqlConnectionStringBuilder.Enlist = true; to make auto add connections to current trnsaction
  • And, the error : PgSqlConnectionStringBuilder.TransactionScopeLocal = true; to say to PostgreSQL that don't use PREPARE TRANSACTION but just do a simple transaction.

After, all is working well.


transaction
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52


Return to dotConnect for PostgreSQL