NESTED TRANSACTIONS

NESTED TRANSACTIONS

Postby JORGEMAL » Fri 05 Feb 2010 02:19

I have a process that performs several database operations. Such a process is split into 3 classes depending on which table will be updated. CLASS1 performs one insertion to TABLE1; CLASS2 and CLASS3 perform several insertions to TABLE2 and TABLE3.

I want to use transactions and I wonder if they can be nested.
My main process calls a method in CLASS1; from CLASS1 is called a method in CLASS2; from CLASS2 is called a method in CLASS3.

Can I define a transaction in every class such that TRANSACTION3 is within TRANSACTION2 and TRANSACTION2 is within TRANSACTION1?

Please advice.

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

Postby StanislavK » Fri 05 Feb 2010 11:12

As far as I understand what you need, you can do it.

I am sending you a test project, please specify if its functionality is similar to the one you want to be implemented.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Fri 05 Feb 2010 16:40

StanislavK wrote:As far as I understand what you need, you can do it.

I am sending you a test project, please specify if its functionality is similar to the one you want to be implemented.


Thank you for your fast response and the sample.
I see that I must "share" the connection among the classes.

Let's look, for example, at Class Table2. It calls method Insert of Class Table3 and if insertion in Class Table3 fails then the transaction is rolled back and control is returned to Class Table2 which executes a Commint for trans2. I suppose I must add code to avoid this situation because trans3 failed and trans2 should not commit. Am I right ?

With respect,
Jorge Maldonado
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby StanislavK » Mon 08 Feb 2010 09:31

As PgSqlTransaction objects represent database transaction, you should share the same connection to use nested transactions.

Actually, if Trans3 fails, Trans2 will roll back too on the server side. To ensure the same behaviour in the application, please remove all catch blocks except the one in the outer transaction.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Tue 09 Feb 2010 16:59

StanislavK wrote:As PgSqlTransaction objects represent database transaction, you should share the same connection to use nested transactions.

Actually, if Trans3 fails, Trans2 will roll back too on the server side. To ensure the same behaviour in the application, please remove all catch blocks except the one in the outer transaction.


Removing catch blocks from the outer transaction means that the application will detect a failure in the last nested transaction (Trans3 in this case) and will perform a rollback here. Such a rollback will affect Trans2 and Trans1 without the need to issue a rollback on each of them. Is this correct?

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

Postby StanislavK » Tue 09 Feb 2010 17:24

The catch blocks need to be removed from inner transactions and left in the outer one. In this case, if Trans3 fails, all three transactions are rolled back on the server side, and the exception thrown is caught in Trans1 only.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Tue 09 Feb 2010 20:17

StanislavK wrote:The catch blocks need to be removed from inner transactions and left in the outer one. In this case, if Trans3 fails, all three transactions are rolled back on the server side, and the exception thrown is caught in Trans1 only.


I am sorry, I misunderstood the concept. So, the catch blocks to be removed are, in the example, those in classes Table2 and Table3. If trans3 fails then trans2 automatically fails, and if trans2 has failed then trans1 will automatically fail too. What if trans3 succeeds and trans2 fails?

What if I want to rollback trans3 if the ExecuteNonQuery() returns zero records processed?
Or, lets say that trans3 succeded but the ExecuteNonQuery in trans2 returned zero records processed and I want to rollback everything. Is my scenario logical?

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

Postby StanislavK » Wed 10 Feb 2010 13:30

I have investigated the situation. Several PgSqlTransaction objects sharing the same connection represent the same transaction on the server side, thus they do not behave exactly as nested transactions. To use nested or distributed transactions, please apply the System.Transactions.TransactionScope objects:
http://msdn.microsoft.com/ru-ru/library/system.transactions.transactionscope.aspx
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Thu 11 Feb 2010 17:10

StanislavK wrote:I have investigated the situation. Several PgSqlTransaction objects sharing the same connection represent the same transaction on the server side, thus they do not behave exactly as nested transactions. To use nested or distributed transactions, please apply the System.Transactions.TransactionScope objects:
http://msdn.microsoft.com/ru-ru/library/system.transactions.transactionscope.aspx


I have read what you suggested.
Does System.Transactions.TransactionScope class support PostgreSQL or any other database?
If so then it has nothing to do with dotConnect for PostgreSQL. Is this correct?

With respect,
Jorge Maldonado
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby StanislavK » Fri 12 Feb 2010 12:12

The TransactionScope class only provides a mechanism to make a code block transactional, it does not have ability to connect and operate PostgreSQL or any other database by itself. dotConnect for PostgreSQL supports TransactionScope, making the commands executed inside the TransactionScope block transactional.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Fri 12 Feb 2010 15:33

So it means that all I have to do is include the TransactionScope concept in my design and use the ordinary Insert, Delete and Update operations through dotConnect. Is this correct?

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

Postby StanislavK » Mon 15 Feb 2010 09:24

This is correct. Inside TransactionScope, you can use nested transactions like follows:

Code: Select all
using (TransactionScope sc = new TransactionScope(TransactionScopeOption.RequiresNew))
{
   PgSqlConnection connection = new PgSqlConnection(connectionString);
   connection.Open();

   PgSqlCommand command = new PgSqlCommand
      ("insert into table1(id,value) values (10, 'some text')", connection);

   try
   { command.ExecuteNonQuery(); }
   catch (Exception ex) { }

   using (TransactionScope sc2 = new TransactionScope(TransactionScopeOption.RequiresNew))
   {
      PgSqlCommand command2 = new PgSqlCommand
         ("insert into table2(id,value) values (10, 'some text')", connection);

      try
      { command2.ExecuteNonQuery(); }
      catch (Exception ex) { }

      sc2.Complete();
   }

   connection.Close();
   sc.Complete();
}
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Tue 16 Feb 2010 17:01

Is there any special configuration somewhere? I just added TransactionScope to my code and it seems that nothing happens because, in spite of an error gotten in a CATCH block, the record is inserted anyway. I send the code for your review.

There is an error in the line "objCarritoEspecs.CopiaEspecs(Me.Platillo.ToString, Me.Clave.ToString, pgConn)" just above the line that completes the transaction which is caught by the CATCH block. I know it because the CATCH block has code to display any problem. I suppose that, as soon as an exception is thrown, the flow of the code goes to the catch block and the complete transaction line should not be executed; nevertheless, the record is inserted. I even commented the objTransScope.Complete() line so it never executes and the record is inserted anyway.

Another test I made was to throw an exception (purposely) just above the objTransScope.Complete() line. I also added a display message (after the thrown exception) that is not executed and this proves that the code is routed to the CATCH block.

Why is the record added anyway?
I made sure the DTC service is running, in fact, it starts automatically.

' ***************
' * Insertion code *
' ***************
Public Sub Inserta()
Using objTransScope As New TransactionScope()
Dim strConn As String = ConfigurationManager.ConnectionStrings("PgSqlConnection").ConnectionString
Dim pgConn As New PgSqlConnection(strConn)
Dim strQuery As String
strQuery = "INSERT INTO carrito (…) RETURNING clave"
pgConn.Open()
Dim pgCmd As New PgSqlCommand()
pgCmd.Connection = pgConn
pgCmd.CommandText = strQuery
Try
Dim drInfo As PgSqlDataReader = pgCmd.ExecuteReader()
If drInfo.HasRows() Then
drInfo.Read()
Me.Clave = Convert.ToInt64(drInfo("clave"))
Dim objCarritoEspecs As New CarritoEspecs()
objCarritoEspecs.CopiaEspecs(Me.Platillo.ToString, Me.Clave.ToString, pgConn)
objTransScope.Complete()
Else
‘ Error message is sent to the user.
End If
Catch ex As Exception
‘ Exception is catch here.
Finally
pgConn.Close()
End Try
End Using
End Sub
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby StanislavK » Wed 17 Feb 2010 16:32

Please specify the exact versions of IDE, PostgreSQL server and dotConnect for PostgreSQL you are using.

I couldn't reproduce the problem. I ran the code like
Code: Select all
Using objTransScope As New TransactionScope()
   Dim strConn As String = "..."
   Dim pgConn As New PgSqlConnection(strConn)
   Dim strQuery As String = "INSERT INTO test_table values(...) RETURNING id "
   pgConn.Open()
   Dim pgCmd As New PgSqlCommand(strQuery, pgConn)
   Try
      Dim drInfo As PgSqlDataReader = pgCmd.ExecuteReader()
      If drInfo.HasRows() Then
         drInfo.Read()
         Dim x = New Exception()
         Throw x
         objTransScope.Complete()
      Else
                End If
   Catch ex As Exception
   Finally
      pgConn.Close()
   End Try
Using

Exception was caught and the transaction rolled back.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby JORGEMAL » Wed 17 Feb 2010 17:39

This is the information you requested:
* Visual Studio 2008
* PostgreSQL - V8.3.1
* dotConnect for PostgreSQL - V4.0.12.0 (dcpostgresql400pro)

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

Next

Return to dotConnect for PostgreSQL