Transactions behaviors

Oct 20, 2010 at 8:33 PM
Good afternoon folks:

I wrote the next code to test the use of transactions during a delete of a set of records, but i have two strange scenarios:

    Private Sub eliminar(ByVal ateCodigo As Integer)
        Dim cmd2 As New EfzCommand
        Dim EfzTran As EfzTransaction = Nothing
        Try
            EfzTran = EfzConn.BeginTransaction()
            With cmd2
                .CommandType = CommandType.StoredProcedure
                .CommandText = "JD_ATENCION"
                .Connection = EfzConn
                .Transaction = EfzTran
                .Parameters.Add(New EfzParameter("@ateCodigo", ateCodigo))
            End With
            cmd2.ExecuteNonQuery()
            EfzTran.Commit()
        Catch ex As Exception
            EfzTran.Rollback()
        End Try
    End Sub
If i set the AutoCommit = False in the connection variable (EfzConn) the program flow correctly but despite of the fact that i do de Commit() the data is lost after the application is closed.

Second, i have try with the AutoCommit = True. In this case the program stop with an exception in the line "EfzTran = EfzConn.BeginTransaction()" that say "No transaction is active on this connection".

I want to know why the single change of AutoCommit throw that exception and if i am using correctly the instruction or i forget something important.
Thank you in advance.
Coordinator
Oct 21, 2010 at 1:51 AM

Good Morning,

What do you mean by "data is lost after the second"? Could you post the complete test case?

Currently you need to set Auto Commit False to start a manual transaction. But we hope to change this behavior in a future release.

-Thanks

Oct 21, 2010 at 2:16 PM

Good morning igloobone:

First of all, thank you for your quick response. Ok you have put it clear the fact that i need to put AutoCommit =False to start a manual transaction.

I don't know if the forum page is wrap the text or not; i wrote "data is lost after the application is closed.", that is the right sentence.

If you review the code that i send you before, i start a transaction manually but, after i do .Commit() (in the scenario of AutoCommit=False), i close the application and reopen it, only to see the records i deleted still there.

I debug the code and i see when the .Commit() it executed but nothing happends, that's why i ask you to see if i miss something like a parameter, or if i declare, set or put the instructions in an incorrect order.

Thank  you in advance.

Coordinator
Oct 21, 2010 at 2:24 PM

Hi colomaj,

What version are you using?

First try a simple DELETE statement and see if that works. 

Can you post JD_ATENCION sp definition? 

-thanks

 

 

Oct 21, 2010 at 2:59 PM
Edited Oct 21, 2010 at 3:06 PM

Hi igloobone:

Despite you recomendation to do a simple DELETE, i do a little change in my code and now works fine.

In the line "EfzTran = EfzConn.BeginTransaction()" y put the parameter [isolationLevel] like this "EfzTran = EfzConn.BeginTransaction(IsolationLevel.Serializable)"

Now i have two questions if you don't mind...

First: If [isolationLevel] is optional, why the code don't do the .Commit(), or maybe it's a problem with the default value of the BeginTransaction function?

Second: Can you give me a recomendation about the seven values of [isolationLevel]?, i mean what would you use in the most common transactions.

P.D.: I use the last version of EffiProz.Core.CF that you guys released (1.4.3937.33976)

Coordinator
Oct 21, 2010 at 3:13 PM
Edited Oct 21, 2010 at 5:02 PM

Hi colomaj,

 I’m glad it worked for you.

We won't be able to help much without seeing the entire code.

Hope this will be helpful http://hsqldb.org/doc/2.0/guide/sessions-chapt.html#sqlgeneral_trans_cc-sect

-thanks