NESTED TRANSACTIONS

[JORGEMAL]

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

[StanislavK]

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.

[JORGEMAL]

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

[StanislavK]

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.

[JORGEMAL]

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

[StanislavK]

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.

[JORGEMAL]

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

[StanislavK]

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

[JORGEMAL]

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

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

[StanislavK]

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.

[JORGEMAL]

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

[StanislavK]

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();
}

[JORGEMAL]

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

[StanislavK]

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.

[JORGEMAL]

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