Transaction scope issues

[quooston]

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

[maxxxxxxxim]

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...

[Shalex]

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.

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

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.

[quooston]

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.

[Shalex]

Thank you for an additional information.

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]

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.

[quooston]

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

[Shalex]

New build of dotConnect for PostgreSQL 7.2.90 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.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.