Transaction scope issues

Transaction scope issues

Postby quooston » Mon 20 Jan 2014 02:23

Hi,

I have commented on this thread: http://forums.devart.com/viewtopic.php?f=3&t=28678

There is mention of "The behaviour is changed: now a transaction rollbacks on Connection.Close() if Transaction.Commit() was not called before closing the connection" in version 7.1.45 28-Nov-13.

Does this mean that transactions in a transaction scope will not rollback until the connection is closed? This would not be expected behaviour.

I have a number of integration tests which are failing because previous transactions are not being rolled back. I see no rollback when viewing dbMmonitor. Downgrading to v6 fixes this.

I would appreciate feedback at your earliest convenience.

Thanks,
Q
quooston
 
Posts: 5
Joined: Tue 05 Mar 2013 00:19

Re: Transaction scope issues

Postby maxxxxxxxim » Mon 20 Jan 2014 07:04

Hi,

I have been having the same issue with the latest build! Our code has been written in such a way that it relies on transaction scope rolling back without us having to explicitly call myConnection.Close! Right now, we don't even have access to the DB Connection in the transaction context. We need to upgrade to v7.x due to compatibility issues with the older release with VS2013. Please provide a resolution.

Regards,

Angry Maxxxxxxxim!

Not really, you guys are cool... :)
maxxxxxxxim
 
Posts: 1
Joined: Mon 20 Jan 2014 06:58

Re: Transaction scope issues

Postby Shalex » Mon 20 Jan 2014 17:54

quooston wrote:There is mention of "The behaviour is changed: now a transaction rollbacks on Connection.Close() if Transaction.Commit() was not called before closing the connection" in version 7.1.45 28-Nov-13.

Does this mean that transactions in a transaction scope will not rollback until the connection is closed?

No, it doesn't. This means that local transaction (Devart.Data.PostgreSql.PgSqlTransaction) is committed only if Transaction.Commit() is called explicitly.

quooston wrote:I have a number of integration tests which are failing because previous transactions are not being rolled back.

maxxxxxxxim wrote:I have been having the same issue with the latest build!

This code commits nothing because ts.Complete() is not invoked:
Code: Select all
    using (var conn = new Devart.Data.PostgreSql.PgSqlConnection()) {
        using (var ts = new System.Transactions.TransactionScope()) {
            conn.ConnectionString = "connection_string_here";
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "insert into dept values (1,'a','a')";
            cmd.ExecuteNonQuery();
            //ts.Complete();
        }
    }

Please tell us how we should modify this snippet to reproduce the problem with incorrect committing.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Transaction scope issues

Postby quooston » Tue 21 Jan 2014 02:31

Hi,

The problem is that the result of the above snippet does not actually issue a ROLLBACK as it used to. If you open dbMonitor and do that in v6.x you will see a ROLLBACK, wheras v7.x does not issue the rollback.

Therefore, if you have multiple transactions which follow one another, and there is no rollback on the previous transaction, that strange things start happening as the following transactions are being subsumed by the previous one.

Here is the output from dbMonitor for a case where there are two transactions after one another, in V6.X:

C:\Program Files (x86)\JetBrains\ReSharper\v8.1\Bin\JetBrains.ReSharper.TaskRunner.CLR4.MSIL.exe (host: LMSMEL0210.leicabio.com)
21/01/2014 1:08:08 PM n/a dotConnect for PostgreSQL monitoring is started Complete
21/01/2014 1:08:08 PM 0.015 Creating pool manager Complete
21/01/2014 1:08:08 PM 0.000 Creating pool with connections string: "User Id=postgres;Password=password#23;Host=localhost;Database=Tester;Persist Security Info=True;Schema=testerschema" Complete
21/01/2014 1:08:08 PM 0.000 Creating object Complete
21/01/2014 1:08:08 PM 0.000 Creating pool with connections string: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs" Complete
21/01/2014 1:08:08 PM 0.000 Creating object Complete
21/01/2014 1:08:08 PM 0.000 Creating pool with connections string: "user id=postgres;password=password#23;host=localhost;database=Dogs;persist security info=True;schema=dogs" Complete
21/01/2014 1:08:08 PM 0.000 Creating object Complete
21/01/2014 1:08:10 PM 0.140 Open connection: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs;" Complete
21/01/2014 1:08:10 PM 0.000 Connect: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs" Complete
21/01/2014 1:08:10 PM 0.015 Execute: SELECT version() Complete
21/01/2014 1:08:10 PM 0.000 Execute: show integer_datetimes Complete
21/01/2014 1:08:10 PM 0.000 Execute: show bytea_output Complete
21/01/2014 1:08:10 PM 0.000 Execute: SET autocommit=true Complete
21/01/2014 1:08:10 PM 0.000 Execute: SET datestyle=ISO Complete
21/01/2014 1:08:10 PM 0.000 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
21/01/2014 1:08:10 PM 0.000 Execute: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ Complete
21/01/2014 1:08:10 PM 0.000 Execute: SET search_path TO dogs; Complete
21/01/2014 1:08:11 PM 0.016 Prepare: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:08:11 PM 0.016 Execute: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:08:12 PM 0.000 Prepare: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id" Complete
21/01/2014 1:08:12 PM 0.000 Execute: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id" Complete
21/01/2014 1:08:12 PM 0.000 Execute: ROLLBACK Complete
21/01/2014 1:08:12 PM 0.000 Connection is returned to pool. Pool has 1 connection(s). Complete
21/01/2014 1:08:12 PM 0.000 Open connection: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs;" Complete
21/01/2014 1:08:12 PM 0.000 Taking connection from connection pool: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs" Complete
21/01/2014 1:08:12 PM 0.000 Connection is taken from pool. Pool has 1 connection(s). Complete
21/01/2014 1:08:12 PM 0.000 Execute: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ Complete
21/01/2014 1:08:12 PM 0.000 Execute: SET search_path TO dogs; Complete
21/01/2014 1:08:12 PM 0.000 Prepare: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:08:12 PM 0.000 Execute: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:08:12 PM 0.000 Prepare: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id" Complete
21/01/2014 1:08:12 PM 0.000 Execute: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id" Complete
21/01/2014 1:08:12 PM 0.000 Execute: ROLLBACK Complete
21/01/2014 1:08:12 PM 0.000 Connection is returned to pool. Pool has 1 connection(s). Complete

----------------------------------------

Now, here is the output log from dbMonitor for exactly the same code being executed with v7.x:

C:\Program Files (x86)\JetBrains\ReSharper\v8.1\Bin\JetBrains.ReSharper.TaskRunner.CLR4.MSIL.exe (host: LMSMEL0210.leicabio.com)
21/01/2014 1:24:49 PM n/a dotConnect for PostgreSQL monitoring is started Complete
21/01/2014 1:24:49 PM 0.000 Creating pool manager Complete
21/01/2014 1:24:49 PM 0.000 Creating pool with connections string: "User Id=postgres;Password=password#23;Host=localhost;Database=Tester;Persist Security Info=True;Schema=testerschema" Complete
21/01/2014 1:24:49 PM 0.016 Creating object Complete
21/01/2014 1:24:49 PM 0.000 Creating pool with connections string: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs" Complete
21/01/2014 1:24:49 PM 0.000 Creating object Complete
21/01/2014 1:24:49 PM 0.000 Creating pool with connections string: "user id=postgres;password=password#23;host=localhost;database=Dogs;persist security info=True;schema=dogs" Complete
21/01/2014 1:24:49 PM 0.000 Creating object Complete
21/01/2014 1:24:51 PM 0.140 Open connection: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs;" Complete
21/01/2014 1:24:51 PM 0.094 Connect: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs" Complete
21/01/2014 1:24:51 PM 0.000 Execute: SET autocommit=true Complete
21/01/2014 1:24:51 PM 0.000 Execute: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ Complete
21/01/2014 1:24:51 PM 0.000 Execute: SET search_path TO dogs; Complete
21/01/2014 1:24:52 PM 0.000 Prepare: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:24:52 PM 0.000 Execute: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:24:52 PM 0.016 Prepare: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id" Complete
21/01/2014 1:24:52 PM 0.000 Execute: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id" Complete
21/01/2014 1:24:53 PM 0.000 Connection is returned to pool. Pool has 1 connection(s). Complete
21/01/2014 1:24:53 PM 0.000 Open connection: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs;" Complete
21/01/2014 1:24:53 PM 0.000 Taking connection from connection pool: "User Id=postgres;Password=password#23;Host=localhost;Database=dogs;Persist Security Info=True;Schema=dogs" Complete
21/01/2014 1:24:53 PM 0.000 Connection is taken from pool. Pool has 1 connection(s). Complete
21/01/2014 1:24:53 PM 0.000 Execute: SET autocommit=true Complete
21/01/2014 1:24:53 PM 0.000 Execute: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ Complete
21/01/2014 1:24:53 PM 0.000 Execute: SET search_path TO dogs; Complete
21/01/2014 1:24:53 PM 0.000 Prepare: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:24:53 PM 0.016 Execute: INSERT INTO dogs."Owners"("Name", "Age")
VALUES ($1, $2)
RETURNING "Id" Complete
21/01/2014 1:24:53 PM 0.000 Prepare: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id" Complete
21/01/2014 1:24:53 PM 0.000 Execute: INSERT INTO dogs."Dogs"("Breed", "Name", "OwnerId")
VALUES ($1, $2, $3)
RETURNING "Id"Error
21/01/2014 1:24:53 PM 0.000 Connection is returned to pool. Pool has 1 connection(s). Complete

As you can see there is no rollback

Please fix v7.x so that rollbacks are issued at the end of a transactionscope where a call to Complete() is not taking place.
quooston
 
Posts: 5
Joined: Tue 05 Mar 2013 00:19

Re: Transaction scope issues

Postby Shalex » Tue 21 Jan 2014 11:14

Thank you for an additional information.

quooston wrote:if you have multiple transactions which follow one another, and there is no rollback on the previous transaction, that strange things start happening as the following transactions are being subsumed by the previous one

We have reproduced the issue and are investigating it.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Transaction scope issues

Postby Shalex » Mon 27 Jan 2014 10:27

The bug with rolling back changes when using TransactionScope is fixed. We will notify you when the corresponding build of dotConnect for PostgreSQL is available for download.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Transaction scope issues

Postby quooston » Tue 28 Jan 2014 23:04

Thanks, looking forward to it! Do I get a prize for finding it?

:wink:
quooston
 
Posts: 5
Joined: Tue 05 Mar 2013 00:19

Re: Transaction scope issues

Postby Shalex » Thu 30 Jan 2014 16:13

New build of dotConnect for PostgreSQL 7.2.90 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=28828.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL