B4J Question B4J server handler SQL lite lock

Discussion in 'B4J Questions' started by Philip Prins, Aug 31, 2015.

  1. Philip Prins

    Philip Prins Active Member Licensed User

    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
    Code:
    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?
     
  2. Roycefer

    Roycefer Well-Known Member Licensed User

    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.
     
  3. Philip Prins

    Philip Prins Active Member Licensed User

    This is the code
    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

    Code:
    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?
     
  4. Erel

    Erel Administrator Staff Member Licensed User

  5. Philip Prins

    Philip Prins Active Member Licensed User

    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"?
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  7. Philip Prins

    Philip Prins Active Member Licensed User

    Thanks Erel , i think i found the problem , i found a Begin Transaction without any further code.
     
    Erel likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice