Completing a transaction with TransactionScope class

[JORGEMAL]

I have also posted this same issue in the ASP.NET forum; if this has nothing to do with you please accept my apologies. My intention is to find out if there is any special consideration in using the TransactionScope Class with DotConnect for PostgreSQL.

I have 2 methods; one for inserting and one for deleting records. Every operation inserts or deletes several records at a time so I want to use the TransactionScope class from the SystemTransactions namespace. Also, there is a data modification method that first calls the Delete method and then the Insert method.

// --------------------------
// Insert Method
// --------------------------
protected void Insert()
{
using (TransactionScope scope1 = new TransactionScope())
{
// Code for inserting records.
scope1.Complete();
}
}

// --------------------------
// Delete Method
// --------------------------
protected void Delete()
{
using (TransactionScope scope2 = new TransactionScope())
{
// Code for deleting records.
scope2.Complete();
}
}

// --------------------------
// Update Method
// --------------------------
protected void Update()
{
using (TransactionScope scope3 = new TransactionScope())
{
Delete();
Insert();
scope3.Complete();
}
}

Methods Insert and Delete call the Complete method each one but, what happens with the Update method? It calls the Delete and Insert methods, each one calling the Complete method, and finally calls its own Complete method.

When the Insert or Delete method is called independently (not from the Update method) my code runs fine inserting or deleting correctly; but when the Update method is called an exception is thrown as follows:

---------------------------------------------------------------------------------------------------------------------
[TransactionAbortedException: The transaction has aborted.]
System.Transactions.TransactionStateAborted.EndCommit(InternalTransaction tx) +11
System.Transactions.CommittableTransaction.Commit() +228
System.Transactions.TransactionScope.InternalDispose() +328
System.Transactions.TransactionScope.Dispose() +1607
Mantenimiento_ListasPreTitulos.btnModificaLista_Click(Object sender, EventArgs e) in d:\Visual Studio 2008 Projects\lasuperlista\Mantenimiento\ListasPreTitulos.aspx.cs:340
DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e) +101
DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String eventArgument) +413
DevExpress.Web.ASPxClasses.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
---------------------------------------------------------------------------------------------------------------------

I ran my app using the debugger and chose to modify data, every instruction of both the Delete and Insert methods was executed successfully until the end of the Update's using statement was reached; the exception was thrown in the closing bracket of the using statement.

I read that the TransactionAbortedException is thrown when an operation is attempted on a transaction that has already been rolled back, or an attempt is made to commit the transaction and the transaction aborts

What is wrong?

Respectfully,
Jorge Maldonado

[Settler]

May be this is the same problem? Or solution will be the same?
http://www.devart.com/forums/viewtopic. ... ctionscope

[Shalex]

JORGEMAL, please give us the following information:
1) tell us the exact version of dotConnect for PostgreSQL your are using (x.xx.xxx);
2) modify the following sample to reproduce the error you are getting in our environment:

Code: Select all

CREATE TABLE test
(
  id serial NOT NULL,
  "name" character varying(20),
  CONSTRAINT id_pkey PRIMARY KEY (id)
);
insert into test values (1, 'asdf');
insert into test values (2, 'asdf');

Code: Select all

    class Program {
        string connStr_pg = "server=localhost;port=5432;uid=postgres;pwd=postgres;Database=postgres;schema=public;";
        PgSqlCommand cmd;
        // --------------------------
        // Insert Method
        // --------------------------
        protected void Insert_pg()
        {
            using (TransactionScope scope1 = new TransactionScope()) {
               //  Code for inserting records.
                using (PgSqlConnection conn = new PgSqlConnection(connStr_pg)) {
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandText = "insert into test values (2, 'asdfs')";
                    cmd.ExecuteNonQuery();
                }
                scope1.Complete();
            }
        }

        // --------------------------
        // Delete Method
        // --------------------------
        protected void Delete_pg()
        {
            using (TransactionScope scope2 = new TransactionScope()) {
                // Code for deleting records.
                using (PgSqlConnection conn = new PgSqlConnection(connStr_pg)) {
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandText = "delete from test where id = 2";
                    cmd.ExecuteNonQuery();
                }
                scope2.Complete();
            }
        }

        // --------------------------
        // Update Method
        // --------------------------
        protected void Update_pg()
        {
            using (TransactionScope scope3 = new TransactionScope()) {
                Delete_pg();
                Insert_pg();
                scope3.Complete();
            }
        } 

        static void Main(string[] args)
        {
            Program prog = new Program();
            prog.Delete_pg();
            prog.Insert_pg();
            prog.Update_pg();
        }
    }
}

[JORGEMAL]

May be this is the same problem? Or solution will be the same?
http://www.devart.com/forums/viewtopic. ... ctionscope

Thank you (and thanks to Shalex too) for the quick reply.
I read the topic you suggested in the above link and my code worked just fine after enabling and setting the "max_prepared_transactions" parameter. Nevertheless, I noticed the following comment just below such a parameter in the postgresql.conf file:

# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.

Is there a big impact on the overall database processes?
Could there be a serious drawback when setting this parameter?
What does your experience says about it?

Best regards,
Jorge Maldonado

[Shalex]

We didn't investigate dependency of shared memory usage on the value of the max_prepared_transactions parameter. This is a question rather to PostgreSQL team. Please refer to PostgreSQL documentation: http://www.postgresql.org/docs/current/ ... ource.html, the max_prepared_transactions (integer) section.

[alemstr]

I have done the same way, but in my case some prepared transactions are not commited (no error, no exceptions) and I can see them in pgAdmin.
So I increased max_prepared_transactions parameter and some time later I received "The transaction has aborted". Why it happening?

How I can resolve this problem.

Thanks.

[Shalex]

alemstr, please give us the following information:
1) the exact versions of your dotConnect for PostgreSQL (x.xx.xxx) and PostgreSQL server (x.x);
2) your call stack;
3) the value of max_prepared_transactions of your PostgreSQL server;
4) can you reproduce the problem with the code from my post (Mon Aug 16, 2010 6:52 pm)? If not, please send us a small test project with the corresponding DDL/DML script so that we can reproduce the issue in our environment.

[alemstr]

1) versions of mydotConnect for PostgreSQL (5.30.165) and PostgreSQL server (9.0);
2) I can not show the call stack, because I did not have any exceptions;
3) the value of max_prepared_transactions of my PostgreSQL server is 20;
4) My problem cannot be reproduce with your code.

A small test project for reproduce this problem has been sent to email.

Send me a private message to get the test project.

[Shalex]

We have received your test project. We will investigate the issue and notify you about the results as soon as possible.

[Shalex]

The problem is fixed. Please try using the latest (6.7.287) build of dotConnect for PostgreSQL and notify us about the result.