Using TransactionScope class for DB operations

Using TransactionScope class for DB operations

Postby JORGEMAL » Wed 12 Jan 2011 23:10

I am deleting several records from a table and I tested 2 different ways: one using the TransactionScope class and one without it. The code below uses the TransactionScope class and it does not work, but if I remove such a class it works just fine. I ran the web project with the debugger and it shows no problem, every instruction executes correctly. Nevertheless, the result is as if nothing had happened, the records are not deleted; it seems that the transaction does not complete. I set the TimeSpan to zero in order to avoid a timeout while debugging. At the end of the using statement, the value of variable "blnError" equals "false" so the Complete method is issued. The code is part of a class that resides in the App_Code folder.
What could be the possible reasons for not getting the result I expect?

I have a similar situation where several records are inserted using the TransactionScope class and here it works fine. The difference is that, while inserting, the code issues several single Insert operations within a loop, one for each record; but when deleting, only one Delete operation is issued which includes all the records to be deleted in the Where condition.

The whole idea is that, one insert operation includes more than one record; and one delete operation includes more than one record too. When the user needs to perform an update, the application issues a delete for all the records and then inserts the correct ones; this way I emulate an update.

Because insert and delete operation are independent classes, I use the TransactionScope class to control the process. The version of dotConnect for PostgreSQL I use is 4.95.180.

With respect,
Jorge Maldonado

Code: Select all
    public Boolean Borra(ref String strMensaje, String strOrigen)
    {
        // Variable para indicar si hubo error en el proceso de borrado.
        Boolean blnError = false;

        // Se define el query.
        StringBuilder strQuery = new StringBuilder();
        strQuery.Append("DELETE FROM listas_pre_titulos ");
        strQuery.Append("WHERE ");
        strQuery.Append("lpt_fuente = " + this.Fuente + " AND ");
        strQuery.Append("lpt_fecha = '" + ListaPreTitulo.Fecha.ToString("dd/MM/yyyy") + "' AND ");
        strQuery.Append("lpt_tipo = " + this.Tipo);

        // Se define la conexion.
        String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
        PgSqlConnection pgConn = new PgSqlConnection(strConn);
        PgSqlCommand pgCmd = new PgSqlCommand(Convert.ToString(strQuery), pgConn);

        // Se efectua la operacion a la base de datos.
        using (TransactionScope trnScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero))
        {
            try
            {
                pgConn.Open();
                if (pgCmd.ExecuteNonQuery() != 0)
                {
                    strMensaje += "SE HA BORRADO UN REGISTRO SATISFACTORIAMENTE. \n";
                    strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
                }
                else
                {
                    blnError = true;
                    strMensaje += "LA OPERACION DE BORRADO NO SE LLEVO A CABO POR CAUSAS DESCONOCIDAS. \n";
                    strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
                }
            }
            catch (Exception excParametro)
            {
                blnError = true;
                strMensaje += "SE HA PRODUCIDO UN ERROR AL BORRAR EL REGISTRO. \n";
                strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
                strMensaje += excParametro + "\n";
                Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Borra",
                    "SE HA PRODUCIDO UN ERROR AL BORRAR EL REGISTRO.",
                    excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
            }
            finally
            {
                pgConn.Close();
            }

            //Completo la transaccion si no hubo errores.
            if (!blnError)
                trnScope.Complete();
        }

        // Regreso el estatus del proceso.
        return blnError;
    }
JORGEMAL
 
Posts: 164
Joined: Thu 03 Jul 2008 23:55

Postby StanislavK » Fri 14 Jan 2011 17:51

Could you please specify the following so that we are able to reproduce the problem in our environment:
- the version of PostgreSQL server you are connecting to;
- the definitions of the database objects used;
- the exact command you are executing.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Mon 17 Jan 2011 16:02

* The version of PostgreSQL I am using is 9.0.1.
* I sent a backup of the database using your support form, it was performed with pgAdmin III.
* Below you can find more detailed C# code for deletion and insertion operations. Deletion does not work but insertion does.

The following code calls the method that deletes the information:
Code: Select all
protected void BorraLista()
    {
        ListaPreTitulo objListaPreTitulo = new ListaPreTitulo();
        objListaPreTitulo.Fuente = Convert.ToInt64(txtIDFuente.Text);
        ListaPreTitulo.Fecha = new PgSqlTimeStamp(dteFechaLista.Date.Year, dteFechaLista.Date.Month, dteFechaLista.Date.Day);
        objListaPreTitulo.Tipo = Convert.ToInt32(cboCatTiposMedia.SelectedItem.Value);
        objListaPreTitulo.Borra(ref strMensaje, "ListasPreTitulos.aspx");
    }


NOTE: The deletion process always deletes more than 1 record.

The following code (without using the TransactionScope class) deletes the records from the table and it works fine:
Code: Select all
    public Boolean Borra(ref String strMensaje, String strOrigen)
    {
        // Variable used to indicate if there is an error in the process.
        Boolean blnError = false;

        // Query definition.
        StringBuilder strQuery = new StringBuilder();
        strQuery.Append("DELETE FROM listas_pre_titulos ");
        strQuery.Append("WHERE ");
        strQuery.Append("lpt_fuente = " + this.Fuente + " AND ");
        strQuery.Append("lpt_fecha = '" + ListaPreTitulo.Fecha.ToString("dd/MM/yyyy") + "' AND ");
        strQuery.Append("lpt_tipo = " + this.Tipo);

        // Connection definition.
        String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
        PgSqlConnection pgConn = new PgSqlConnection(strConn);
        PgSqlCommand pgCmd = new PgSqlCommand(Convert.ToString(strQuery), pgConn);

        // The operation to the DB is performed.
        try
        {
            pgConn.Open();
            if (pgCmd.ExecuteNonQuery() != 0)
            {
                strMensaje += "DELETION SUCCEDED. \n";
                strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
            }
            else
            {
                blnError = true;
                strMensaje += "DELETION NOT SUCCESSFUL. \n";
                strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
            }
        }
        catch (Exception excParametro)
        {
            blnError = true;
            strMensaje += "AN ERROR HAS OCCURRED. \n";
            strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
            strMensaje += excParametro + "\n";
            Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Borra",
                "AN ERROR HAS OCCURRED.",
                excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
        }
        finally
        {
            pgConn.Close();
        }

        // Final result of the operation.
        return blnError;
    }


The following code (using the TransactionScope class) deletes the records from the table and does not work fine, nothing is deleted:
Code: Select all
public Boolean Borra(ref String strMensaje, String strOrigen)
    {
        // Variable used to indicate if there is an error in the process.
        Boolean blnError = false;

        // Query definition.
        StringBuilder strQuery = new StringBuilder();
        strQuery.Append("DELETE FROM listas_pre_titulos ");
        strQuery.Append("WHERE ");
        strQuery.Append("lpt_fuente = " + this.Fuente + " AND ");
        strQuery.Append("lpt_fecha = '" + ListaPreTitulo.Fecha.ToString("dd/MM/yyyy") + "' AND ");
        strQuery.Append("lpt_tipo = " + this.Tipo);

        // Connection definition.
        String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
        PgSqlConnection pgConn = new PgSqlConnection(strConn);
        PgSqlCommand pgCmd = new PgSqlCommand(Convert.ToString(strQuery), pgConn);

        // The operation to the DB is performed.
        using (TransactionScope trnScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero))
        {
            try
            {
                pgConn.Open();
                if (pgCmd.ExecuteNonQuery() != 0)
                {
                    strMensaje += "DELETION SUCCEDED. \n";
                    strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
                }
                else
                {
                    blnError = true;
                    strMensaje += "DELETION NOT SUCCESSFUL. \n";
                    strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
                }
            }
            catch (Exception excParametro)
            {
                blnError = true;
                strMensaje += "AN ERROR HAS OCCURRED. \n";
                strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
                strMensaje += excParametro + "\n";
                Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Borra",
                    "AN ERROR HAS OCCURRED.",
                    excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
            }
            finally
            {
                pgConn.Close();
            }

            // Transaction is completed if no error happened.
            if (!blnError)
                trnScope.Complete();
        }

        // Final result of the operation.
        return blnError;
    }


The following code calls a method that inserts records to the same table:
Code: Select all
protected void InsertaLista()
    {
        // Valido que no exista una lista preliminar con la misma fuente,
        // la misma fecha y el mismo tipo.
        ListaPreTitulo objListaPreTitulo = new ListaPreTitulo();
        objListaPreTitulo.Fuente = Convert.ToInt64(txtIDFuente.Text);
        ListaPreTitulo.Fecha = new PgSqlTimeStamp(dteFechaLista.Date.Year, dteFechaLista.Date.Month, dteFechaLista.Date.Day);
        objListaPreTitulo.Tipo = Convert.ToInt32(cboCatTiposMedia.SelectedItem.Value);
        if (objListaPreTitulo.ExisteLista(ref strMensaje, "ListasPreTitulo.aspx") > 0)
        {
            strMensaje += "DUPLICATE INFORMATION.";
        }
        else
        {
            objListaPreTitulo.Inserta(ref strMensaje, "ListasPreTitulos.aspx", lstTitulos);
        }
    }


The following code inserts records to the table. It uses the TransactionScope class and it works fine.
NOTE: The insertion process always inserts more than 1 record.
Code: Select all
public Boolean Inserta(ref String strMensaje, String strOrigen, ASPxListBox lstTitulos)
    {
        Boolean blnError = false;

        // Connection definition.
        String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
        PgSqlConnection pgConn = new PgSqlConnection(strConn);
        pgConn.Open();

        PgSqlCommand pgCmd = new PgSqlCommand();
        pgCmd.Connection = pgConn;

        using (TransactionScope trnScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero))
        {
            try
            {
                // Items to be inserted are in a ListBox.
                Int32 intIndice = 0;
                StringBuilder strQuery = new StringBuilder();
                for (intIndice = 1; intIndice <= lstTitulos.Items.Count; intIndice++)
                {
                    strQuery.Remove(0, strQuery.Length);
                    strQuery.Append("INSERT INTO listas_pre_titulos (");
                    strQuery.Append("lpt_fuente, ");
                    strQuery.Append("lpt_titulo, ");
                    strQuery.Append("lpt_fecha, ");
                    strQuery.Append("lpt_tipo, ");
                    strQuery.Append("lpt_posicion ");
                    strQuery.Append(") VALUES (");
                    strQuery.Append(this.Fuente + ", ");
                    strQuery.Append(Convert.ToInt64(lstTitulos.Items[intIndice - 1].Value) + ", ");
                    strQuery.Append("'" + ListaPreTitulo.Fecha.ToString("yyyy-MM-dd AD") + "', ");
                    strQuery.Append(this.Tipo + ", ");
                    strQuery.Append(Convert.ToInt32(lstTitulos.Items[intIndice - 1].Text.Substring(0, 3)) + ")");
                    pgCmd.CommandText = Convert.ToString(strQuery);
                    if (pgCmd.ExecuteNonQuery() == 0)
                    {
                        blnError = true;
                        strMensaje += "AN ERROR HAS OCCURRED.";
                        strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Inserta" + "\n";
                        break;
                    }
                }

                // Si no hubo errores se lleva a cabo la transaccion.
                if (!blnError)
                {
                    strMensaje += "INSERTION SUCCEEDED";
                    strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Inserta" + "\n";
                }
            }
            catch (Exception excParametro)
            {
                blnError = true;
                strMensaje += "INSERTION NOT SUCCESSFUL.";
                strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Inserta" + "\n";
                strMensaje += excParametro + "\n";
                Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Inserta",
                    "INSERTION NOT SUCCESSFUL.",
                    excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
            }
            finally
            {
                pgConn.Close();
            }
           
            // Transaction is completed if no error happened.
            if (!blnError)
                trnScope.Complete();
        }

        // Final result of the operation.
        return blnError;
    }


As you can see, insertion and deletion procedures are very similar; insertion works fine but deletion does not.
I know that I could delete records without using the TransactionScope class but I need to include transactions because the user has the option of modifying data which actually is performed by deleting records and then inserting the new ones so I need to be sure the complete process is made successfully.
I hope the information I am giving you is appropriate.

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

Postby StanislavK » Tue 18 Jan 2011 15:40

Please check the max_prepared_transactions server variable (e.g., execute the 'show max_prepared_transactions' command). It should be set to a positive number so that TransactionScope can work properly (changing this server variable needs restarting the server). Please tell us if this helps.

However, we will consider throwing an exception when leaving the TransactionScope environment in case max_prepared_transactions is set to 0.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Tue 18 Jan 2011 16:45

Parameter "max_prepared_transactions" is already set to the same value as "max_connections". I read PostgreSQL documentation and it says that this is a good setting. So, this is not what is causing my issue.
The strange situation is that the insertion process works fine but not the deletion process.

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

Postby StanislavK » Wed 19 Jan 2011 18:02

Apparently, a problem is that an error occurs at the server side, but no exception is generated for it in the application. Could you please trace the operations performed in the database with dbMonitor and send us the log, so that we are able to check the server error?
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Thu 20 Jan 2011 00:11

I installed DBMonitor and ran my site. I found a syntax error in one SELECT statement (a space was missing) that was not catched as an exception. It caused several operations to get a "Pending" status, being one of them the DELETE command I am having problems with. As soon as I made the necessary corrections, such a status disappeared. Nevertheless, an "Error" status now appears precisely in this delete command and maybe it is also a syntax error, but I do not see it. I sent the log file generated by DBMonitor for your review using the Contact Us web page.
One more thing I see in the log file is an error in an entry with the following text: "Connection is returned to pool. Pool has 1 connection(s). "

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

Postby StanislavK » Fri 21 Jan 2011 09:10

According to the log, an error occurs when a connection is returned to the pool, and several other errors occur when performing deletions. Could you please specify the messages for these errors (you can check them, e.g., in the Error tab of dbMonitor)?
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Fri 21 Jan 2011 15:25

I am not getting an error for the connection anymore (I do not know why) but only for the DELETE command. I clicked the Error tab and this is what is shown: "InternalConnection without proxy". I performed several tests in order to see if the error appeared when the connection is returned to the pool but it did not.

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

Postby StanislavK » Tue 25 Jan 2011 10:09

There was a problem with TransactionScope in the current version of dotConnect for PostgreSQL (it was possible that an error occurred at the server, but no exception was generated in the application). We have fixed this issue, and the fix will be available in the nearest build. Please try the upcoming version (we will inform you here when it is released) and tell us about the results.

E.g., if an exception is thrown when leaving TransactionScope, please specify its exact message and stack trace, and the details of inner exceptions (if any).
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Thu 27 Jan 2011 18:05

We have released the new 5.10.96 version of dotConnect for PostgreSQL. It includes the fix for the bug with throwing no exception when disposing TransactionScope. The new build can be downloaded from
http://www.devart.com/dotconnect/postgresql/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in dotConnect for Oracle 6.10.96, please refer to
http://www.devart.com/forums/viewtopic.php?t=20122
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Fri 28 Jan 2011 15:27

It is working now. I ran DBMonitor and the transaction commited successfully. I will continue testing and contact you in case I need.

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


Return to dotConnect for PostgreSQL