NESTED TRANSACTIONS
NESTED TRANSACTIONS
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
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
Thank you for your fast response and the sample.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.
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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?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.
Respectfully,
Jorge Maldonado
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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?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.
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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 ... scope.aspx
http://msdn.microsoft.com/ru-ru/library ... scope.aspx
I have read what you suggested.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 ... scope.aspx
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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();
}
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
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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
Exception was caught and the transaction rolled back.
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