Problem with relations and System.Transactions

Problem with relations and System.Transactions

Postby JORGEMAL » Mon 17 Oct 2011 16:33

I am inserting records in 2 tables so I decided to use the TransactionScope class from the System.Transactions namesape. Firstly, I wrote my code without transactions to make sure it is working. Then, I added transactions and I am having a problem. The tables have a relation which is defined using a foreign key. Insertion takes place in the parent table first, and then continues inserting several records in the child table. As soon as it tries to insert the first child record I get an exception saying that there is a foreign key violation. It seems that the parent table record is not inserted immediately when transactions are used but until transaction is completed. Is this a question for you?

Respectfully,
Jorge Maldonado

Code: Select all
// **********************************************
// Calls insertion methods for parent and child tables.
// **********************************************
Protected void Insertion
    Boolean blnError = false;
    using (TransactionScope trnScope = new TransactionScope())
    {
        // Insertion in parent table.
        ParentTable objParentTable as new ParentTable();
        blnError = ParentTable.Insert();
       
        // Insertion in child table.
        if (!blnError)
        {
            ChildTable objChildTable = new ChildTable();
            blnError = ChildTable.Insert();
        }

        if (!blnError)
            trnScope.Complete();
    }

// **********************************************
// Parent and child table methods are part of a class library.
// **********************************************
protected Boolean ParentTable()
{
    // Insertion in the parent table takes place here.
    // The parent table has a serial field which is the primary key.
    // The insert command has a RETURNING clause to get such
    // a serial field which will be used by the ChildTable method.
    // I tested with and without TransactionScope here.
    // If an exception is caught then TRUE is returned, else FALSE is returned.
}

protected Boolean ChildTable()
{
    // Insertion in the child table takes place here.
    // The primary key, which is the value of the serial field generated
    // in the ParentTable() method, is used here to relate the child records
    // with the parent record. It seems that the parent record is not
    // inserted yet, but until the transaction is completed.
    // I tested with and without TransactionScope here.
    // If an exception is caught then TRUE is returned, else FALSE is returned.
}

JORGEMAL
 
Posts: 164
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Fri 21 Oct 2011 12:19

We have reproduced the mentioned behaviour. We will investigate it and notify you about the results.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Mon 24 Oct 2011 09:17

This is a designed behaviour, because PostgreSQL server supports only the ReadCommitted and Serializable isolation levels, which do not allow second transaction to see the changes that are made in the first one.

Distributed transaction in PostgreSQL starts as a local transaction ("START TRANSACTION" or "BEGIN"). So, they behave in a similar way. The difference is that distributed transaction supports two-phase commit.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Mon 24 Oct 2011 13:40

So, this means that it will not work this way (using the TransactionScope class). In this case, what is the solution? Should I use PgSqlTransaction class instead?

Thank you,
Jorge Maldonado
JORGEMAL
 
Posts: 164
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Tue 25 Oct 2011 11:38

Yes, please use PgSqlTransaction. Here is an example:
Code: Select all
DDL:

CREATE TABLE parent
(
  id integer NOT NULL,
  data character varying,
  CONSTRAINT parent_pkey PRIMARY KEY (id)
);
CREATE TABLE child
(
  id integer NOT NULL,
  parent_id integer,
  CONSTRAINT child_pkey PRIMARY KEY (id),
  CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES parent (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

======================

C#:

    class Program {
        static void Main(string[] args) {
            string connStr = "server=db;port=5438;uid=postgres;pwd=postgres;database=postgres;";
            using (PgSqlConnection conn = new PgSqlConnection(connStr)){
                conn.Open();
                PgSqlTransaction trans = conn.BeginTransaction();
                ParentTable(conn);
                ChildTable(conn);
                trans.Commit();
            }
        }
        static protected void ParentTable(PgSqlConnection cs){
                PgSqlCommand cmd = cs.CreateCommand();
                cmd.CommandText = "insert into parent values (1,'some data')";
                cmd.ExecuteNonQuery();
        }
        static protected void ChildTable(PgSqlConnection cs){
                PgSqlCommand cmd = cs.CreateCommand();
                cmd.CommandText = "insert into child values (1,1);";
                cmd.ExecuteNonQuery();
        }
    }
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Wed 26 Oct 2011 13:36

One reason I was using the TransactionScope class is that I need to leave one table out of the transaction which can be done setting the TransactionScopeOption to Supress. How can I achieve this goal using the PgSqlTransaction class?

I supposed that not sharing the connection with such a table, but openning and closing its own one, would solve my issue but it seems it does not. This table is used as an event log so I need to insert data weather or not the operation to the DB is successful. I threw an exception on purpose and nothing was registered in the log table.

Respectfully,
Jorge Maldonado
JORGEMAL
 
Posts: 164
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Mon 31 Oct 2011 13:20

JORGEMAL wrote:I supposed that not sharing the connection with such a table, but openning and closing its own one, would solve my issue but it seems it does not.

Try this code. It works with dotConnect for PostgreSQL v 5.50.237.
Code: Select all
    class Program {
        static void Main(string[] args) {
            bool isSuccess = false;
            string errText = null;
            string connStr = "server=db;port=5438;uid=postgres;pwd=postgres;database=postgres;";
            try {
                using (PgSqlConnection conn = new PgSqlConnection(connStr)) {
                    conn.Open();
                    PgSqlTransaction trans = conn.BeginTransaction();
                    ParentTable(conn);
                    ChildTable(conn);
                    trans.Commit();
                    isSuccess = true;
                }
            }
            catch (PgSqlException ex) {
                isSuccess = false;
                errText = ex.Message;
            }
            finally {
                using (PgSqlConnection conn = new PgSqlConnection(connStr)) {
                    conn.Open();
                    PgSqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "insert into mylog (id, message) values (:p1,:p2)";
                    cmd.Parameters.Add("p1", PgSqlType.Int).Value = getId();
                    cmd.Parameters.Add("p2", PgSqlType.VarChar).Value = (isSuccess == true) ? "success" : errText;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        static protected void ParentTable(PgSqlConnection cs) {
            PgSqlCommand cmd = cs.CreateCommand();
            cmd.CommandText = "insert into parent values (4,'some data')";
            cmd.ExecuteNonQuery();
        }
        static protected void ChildTable(PgSqlConnection cs) {
            PgSqlCommand cmd = cs.CreateCommand();
            cmd.CommandText = "insert into child values (4,4);";
            cmd.ExecuteNonQuery();
        }
    }
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL