B4J Question B4J server handler SQL lite lock

[Philip Prins]

I often get a failure SQL lock , i made all the server handlers single thread (true)
In my websocket connections i only read from the database.
I can not see exactly when this happens because the error shows up after 60 seconds with the error waiting on

Error

java.lang.Exception: Stack trace
    at java.lang.Thread.dumpStack(Thread.java:1329)
    at anywheresoftware.b4j.objects.SQL.startLock(SQL.java:164)
    at anywheresoftware.b4j.objects.SQL.BeginTransaction(SQL.java:405)
    at cunacc.server.chatshared._writestatus(chatshared.java:454)
    at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
    at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:447)
    at anywheresoftware.b4a.keywords.Common.CallSubNew2(Common.java:417)
    at cunacc.server.webalarms._previewbtn_click(webalarms.java:385)
    at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
    at anywheresoftware.b4j.object.WebSocketModule$Adapter$1.run(WebSocketModule.java:126)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
    at anywheresoftware.b4j.object.WebSocketModule$Adapter$ThreadHandler.run(WebSocketModule.java:195)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

is there a way to trace back where the error occurs?

 

[Roycefer]

Is your package name "cunacc.server"? If so, it looks like your WebAlarms.PreviewBtn_Click method is invoking your ChatShared.WriteStatus method, the latter of which is causing the SQL lock problem.

 

[Philip Prins]

Is your package name "cunacc.server"? If so, it looks like your WebAlarms.PreviewBtn_Click method is invoking your ChatShared.WriteStatus method, the latter of which is causing the SQL lock problem.

This is the code

Sub previewBtn_Click (Params As Map)''b4j_raiseEvent

    Dim NwStatus As String
   
   
    Select DbStatus
   
    Case "NVT"
    NwStatus = "NVT"
   
    Case "Nieuw"
    NwStatus = "Gezien"
   
    Case "Gezien"
    NwStatus = "Afgehandeld"
   
    Case "Afgehandeld"
    NwStatus = "NVT"
   
    Case "Verzonden"
    NwStatus = "NVT"
   
    Case "Geaccepteerd"
    NwStatus = "NVT"
   
    Case "Afgewezen"
    NwStatus = "NVT"
   
    Case "Bericht ontvangen"
    NwStatus = "NVT"
   
    Case "Bericht gelezen"
    NwStatus = "NVT"
   
    Case "Bericht verzonden"
    NwStatus = "NVT"
   
    End Select
    DbStatus = "NVT"
    selectednr.SetText("Kies een item")
   
    If NwStatus <> "NVT" And DbStatus <> "" Then
        'Change status in Db
   
    Dim stsDB As Map
    stsDB.Initialize
    stsDB.Put("NR",selectNR)
    stsDB.Put("NwStatus",NwStatus)
    stsDB.Put("Actie",ws.Session.GetAttribute("name"))
   
    CallSub2(ChatShared,"WriteStatus",stsDB)
   
    End If
    ws.RunFunction("previewshow",Array(selectNR,Main.filesFolder&"/"& filelocation))   
   
End Sub

This calls WriteStatus

Public Sub WriteStatus(sts As Map)
                        Main.db.BeginTransaction
                Try
                        Dim WhereFields As Map
                            WhereFields.Initialize
                        WhereFields.Put("NR",sts.Get("NR"))
                   
                        DBUtils.UpdateRecord(Main.db, "Alarms", "Status",sts.Get("NwStatus"), WhereFields)
                        DBUtils.UpdateRecord(Main.db, "Alarms", "Actie",sts.Get("Actie"), WhereFields)
                        DBUtils.UpdateRecord(Main.db, "Alarms", "Tijd",DateTime.Now, WhereFields)
                       
                        Main.db.TransactionSuccessful
                       
                       
                Catch
               
                Main.db.Rollback
               
                   
                End Try

End Sub

Is this wrong?

 

[Erel]

This message means that you have somewhere started a transaction and haven't completed it.

Note that you don't need to use single threaded servlets.

See this tutorial: https://www.b4x.com/android/forum/t...ent-access-to-sqlite-databases.39904/#content

 

[Philip Prins]

Thank you Erel,

Is there a quick way to see where the last transaction is started?
What is meant by "Don't use a ConnectionPool"?

 

[Erel]

Is there a quick way to see where the last transaction is started?

No. Search the code for BeginTransaction.

Remember that you only need to create a transaction when you write to the database.

Your code doesn't use a connection pool. Connection pool is an object from the server library. It is not relevant for SQLite databases.

 

[Philip Prins]

Thanks Erel , i think i found the problem , i found a Begin Transaction without any further code.