B4J Question How to handle unclosed query, resultset & trsancsaction when database connection is broken?

incendio · Jan 17, 2020

Hi guys,

In a non UI database, I have a codes like these

B4X:

Sub Process_Globals
    Private pool As ConnectionPool
    Private Tm As Timer
End Sub

Sub AppStart (Args() As String)
    pool.Initialize(....)

    Tm.Initialize("TM",3000)
    Tm.Enabled = True      
   
    Try
        StartMessageLoop
    Catch
        Log("main error")
    End Try
End Sub

Sub TM_Tick
    Private RS As ResultSet
    Private Qry,ExecQ As SQL
    Tm.Enabled = False
    Log("==========================================")
    Try
        Qry = pool.GetConnection
        RS = Qry.ExecQuery("SELECT * FROM table where id = -9")
        ExecQ.BeginTransaction
        do while RS.NextRow
            'do some operations
        loop
        RS.Close
        Qry.Close
        ExecQ.TransactionSuccessful
    Catch
        Log("unable to connect!")
    End Try
    Tm.Enabled = True
End Sub

When datatabase connection errors happen, Query, ResultSet & Transaction will not closed properly, how to handle it? Or B4J automatically delete unclosed Query,ResultSet & Transaction when next execution occurred? What happen to unfinished transaction? Will it rollback automatically?

Harris · Jan 17, 2020

Maybe this:

B4X:

Sub TM_Tick
    Private RS As ResultSet
    Private Qry As SQL
    Tm.Enabled = False
    Log("==========================================")
    Try
        Qry = pool.GetConnection
        RS = Qry.ExecQuery("SELECT * FROM table where id = -9") ' just a select - nothing to rollback here...
        Qry.BeginTransaction
        do while RS.NextRow
            'do some operations
        loop
        Qry.TransactionSuccessful
        RS.Close
        Qry.Close

    Catch
        RS.Close  ' close if open
        Qry.RollBack   ' only if performing edits or inserts in (do some operations...)
        Qry.Close  ' close if open so you don't run out of pool handles...
        Log("unable to connect!")
    End Try
    Tm.Enabled = True
End Sub

incendio · Jan 17, 2020

Those codes under Catch will raised errors when connection to the database is broken.

I can add try... Catch when try to close resultset or query, this way errors will still be cache by sub TM_tick, but the problems still exists, resultset/queries are unclosed, transactions still not finished.

Will these conditions OK or can raise problems to the database?

mindful · Jan 17, 2020

@incendio as you are working with a transactional database in a transactional manner until the end of the transcation is received by the database (Qry.TransactionSuccessful does exactly this) the real data from you tabels is not altered. So if your connection with the database is broken before the finished command is received you can rest assured that your data will not be altered.

Harris · Jan 17, 2020

Bruce Dickenson - You know, it really sounds great- but I could have used a little more cow bell...

B4X:

Sub TM_Tick
    Private RS As ResultSet
    Private Qry As SQL
    Tm.Enabled = False
    Log("==========================================")
    Try
       Try
           Qry = pool.GetConnection
       Catch
       ' .....   no pool?
            Return
       End Try
       Try
           RS = Qry.ExecQuery("SELECT * FROM table where id = -9") ' just a select - nothing to rollback here...
       Catch
         ' ....  cant get RS?
          Return 
       End Try
        
       '  no return - carry on safely....    You Get The Idea....

        Qry.BeginTransaction
        do while RS.NextRow
            'If you can break something here - Try, Catch....
            
        loop
        Qry.TransactionSuccessful
        RS.Close
        Qry.Close

    Catch
        RS.Close  ' close if open
        Qry.RollBack   ' only if performing edits or inserts in (do some operations...)
        Qry.Close  ' close if open so you don't run out of pool handles...
        Log("unable to connect!")
    End Try
    Tm.Enabled = True
End Sub

incendio · Jan 18, 2020

@Harris ,
Those codes to close resultset or query under Catch cannot be guaranteed will be successful.

Even you have tested that connection before that was a successful, there is no guarantee that the connection after that will always be OK.

incendio · Jan 18, 2020

mindful said:
@incendio as you are working with a transactional database in a transactional manner until the end of the transcation is received by the database (Qry.TransactionSuccessful does exactly this) the real data from you tabels is not altered. So if your connection with the database is broken before the finished command is received you can rest assured that your data will not be altered.

How is B4J behaviour against unfinished transactions when database connection resume again?

There is no guarantee that database connection always available, so there is always a risk that there are hanging transactions, resultsets & queries.

Should I handle these hangings transactions, etc or it will aborted or handle automatically by B4J?

Harris · Jan 18, 2020

If they were opened - and got closed, all should be stable on next try...
Do you experience anything different - or just a hunch / gut feeling?

incendio · Jan 18, 2020

Those app will handle huge and critical data.
I want to know the importance and critical things before experiencing any problems.

mindful · Jan 18, 2020

incendio said:
There is no guarantee that database connection always available, so there is always a risk that there are hanging transactions, resultsets & queries

If your db conncetion breakes it will throw an error so you need to catch it within a try catch ... and if those apps will handle huge and critical data like you say you should prepare some tests so you can find out how to handle this exceptions.

Regarding the hanging transactions, results sets or querys i can see in your code that those variables are local to the sub so when the sub finishes those variables will get picked up by the garbage collector, but you need to handle exceptions because you will be using, more likely variables/parameters in your query so if you have a bad query it will also throw an exception so again you need to catch it, analyse it and create your desired outcome. (A little example: if you have an error in an select query you will not get a result set that you need to close ...)

incendio · Jan 18, 2020

mindful said:
If your db conncetion breakes it will throw an error so you need to catch it within a try catch ... and if those apps will handle huge and critical data like you say you should prepare some tests so you can find out how to handle this exceptions.

Regarding the hanging transactions, results sets or querys i can see in your code that those variables are local to the sub so when the sub finishes those variables will get picked up by the garbage collector, but you need to handle exceptions because you will be using, more likely variables/parameters in your query so if you have a bad query it will also throw an exception so again you need to catch it, analyse it and create your desired outcome. (A little example: if you have an error in an select query you will not get a result set that you need to close ...)

I have handle others exception but no sure for transactions, resultsets/queries. So, since these variables will be handle by garbage collector, I won't need to handle those again.

I am testing RAM consumption right now. This app will runs in loops for 7/24, at it seems RAM consumption is increased every second, will see when it will stop.

Erel · Jan 19, 2020

The correct pattern for working with a connection taken from the pool is:

B4X:

Dim sql As SQL = pool.GetConnection
Try
'work with sql
Catch
 'handle failure
End Try
sql.Close

The online servers are using connection pools and run for months without any issues.

incendio · Jan 21, 2020

Erel said:
The correct pattern for working with a connection taken from the pool is:

B4X:

[CODE=b4x]Dim sql As SQL = pool.GetConnection

Try
'work with sql
Catch
'handle failure
End Try
sql.Close
[/code]

The online servers are using connection pools and run for months without any issues.

Aren't these codes will raised errors and make application quit?

B4X:

Dim sql As SQL = pool.GetConnection

B4X:

sql.Close

I placed pool.GetConnection and sql.close inside try ... catch so when there are errors, application won't quit and will resume when errors disappear.

Erel · Jan 21, 2020

incendio said:
Aren't these codes will raised errors and make application quit?

Depends on the type of app and on Application_Error code.

Don't put sql.Close inside the same Try / Catch block as it will not be useful. You can either add another layer of Try / Catch or catch the potential exception in the calling code.

B4J Question How to handle unclosed query, resultset & trsancsaction when database connection is broken?

incendio

Well-Known Member

Harris

Expert

incendio

Well-Known Member

mindful

Active Member

Harris

Expert

incendio

Well-Known Member

incendio

Well-Known Member

Harris

Expert

incendio

Well-Known Member

mindful

Active Member

incendio

Well-Known Member

Erel

B4X founder

incendio

Well-Known Member

Erel

B4X founder

Similar Threads

B4J Question How to handle unclosed query, resultset & trsancsaction when database connection is broken?

Well-Known Member

Expert

Well-Known Member

Active Member

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Active Member

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency