Android Question SQL operations in service, with transaction. The result: database is locked (code 5)

[rosippc64a]

Hi All,
Here's my case: in the background I download a json string from a website, which I use to update records in two tables. Obviously all this is done in the context of a transaction, my program does a great job, but after the transaction is closed, the program freezes and "android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): retrycount exceeded" error occurs after a few minutes of freezing. When I manage to see where the error occured, well on the first sql query, it's already in the main thread. Since the code snippet in question is running in a service, I have no way of knowing if others are not performing any database operations, such as queries, so, I open a separate sql object, yet the same freezing happens.
What is the correct approach to this logic?
Attached a code snippet to see what happens:

'ACTIVITY
...
CallSub(svcRESTnewWEB,"reg_last_sync_dates")
...

' SERVICE svcRESTnewWEB
reg_last_sync_dates download the json string, in the
Sub JobDoneX (J As HttpJob) I call the frissTorzsek() with the data I got from web.
'---------------------------------------------------------------------------------
'update in the background the master data changed on the server
'm has the resulting map with the updated data, e.g:
'{"success":1,"products":[
'{"id":1,"ceg_id":61,"bolt_id":61,"cikkcsop_id":1,"cikkszam":"1011","megnevezes":"Piros alma","vtszkodtip":"VTSZ","vtsz":"808","me":"Kilogramm","bruttoar":"550","afakod":"27%","adomentesseg":null,"megjegyzes":"Érett piros alma","vonalkod":"9993456789185","azon":1100546657,"termekkep":null,"status":"A","umdate":"2024-07-09T11:55:51.000Z","fokatjson":"EGYEB","alkatjson":"KEDVEZMENY","ntakszorzo":"1","ntakme":null,"afakodelv":"27%","repohar":false,"autszervizdij":"N","autszervizdijsz":"5","gongyolegazon":null,"keszlet":"0","keszlet_min":"0","keszletfigyelo":false},
'{"id":2,"ceg_id":61,"bolt_id":61,"cikkcsop_id":1,"cikkszam":"1012","megnevezes":"Kókusz","vtszkodtip":"VTSZ","vtsz":"801","me":"db","bruttoar":"300","afakod":"27%","adomentesseg":null,"megjegyzes":"Friss kókuszdió","vonalkod":"9993456789186","azon":640092854,"termekkep":"upload/termek/1720526269980.jpg","status":"A","umdate":"2024-07-09T11:57:53.000Z","fokatjson":"ALKMENTESITAL_HELYBEN","alkatjson":"ITALCSOMAG","ntakszorzo":"1","ntakme":null,"afakodelv":"27%","repohar":false,"autszervizdij":"N","autszervizdijsz":"5","gongyolegazon":null,"keszlet":"0","keszlet_min":"0","keszletfigyelo":false},
'{"id":3,"ceg_id":61,"bolt_id":61,"cikkcsop_id":1,"cikkszam":"1013","megnevezes":"Mogyoróvaj","vtszkodtip":"VTSZ","vtsz":"2008","me":"db","bruttoar":"800","afakod":"27%","adomentesseg":null,"megjegyzes":"Krémes mogyoróvaj","vonalkod":"9993456789187","azon":2907739029,"termekkep":"upload/termek/1720526304462.jpg","status":"A","umdate":"2024-07-09T11:58:26.000Z","fokatjson":"N/A","alkatjson":"N/A","ntakszorzo":"1","ntakme":"DARAB","afakodelv":"27%","repohar":false,"autszervizdij":"N","autszervizdijsz":"5","gongyolegazon":null,"keszlet":"0","keszlet_min":"0","keszletfigyelo":false}],
'"categories":[
'{"id":1,"bolt_id":61,"megnevezes":"ÉLELMISZER","megjegyzes":"finomságok tárháza","csoportkep":null,"ruhatarjegy":false,"status":"A","sorszamtip":0,"ksorszam":"","vsorszam":"","utsorszam":"","fokatjson":"ETEL","user":null,"umdate":"2024-07-09T11:55:18.000Z","azon":733624890,"merlegelendo":"N"}],
'"last_sync_date":"2024-07-07T12:06:22.00Z"}
private Sub frissTorzsek( m As Map)
    Try
        If sqll.IsInitialized Then
            sqll.BeginTransaction
            Try
                Dim sb As StringBuilder
                sb.Initialize
                'az egyes kulcsok alatt táblák vannak
                'később lehetnek mások
                For Each tbl As String In Array As String ("categories","products")
                    If m.ContainsKey(tbl) Then
                        Private realtabla As String
                        If tbl = "products" Then
                            realtabla = "cikkt"
                        else If tbl = "categories" Then
                            realtabla = "cikkcsop"
                        End If
                        'csak olyan mezőket mentek el, amik a nálam lévő táblában is vannak
                        Private tblfields As Map
                        tblfields.Initialize
                        Dim rs As ResultSet = sqll.ExecQuery($"
                        pragma table_info(${realtabla})"$)
                        Do While rs.NextRow
                            tblfields.Put(rs.Getstring("name"),rs.Getstring("type").ToLowerCase)
                        Loop
                        rs.Close
                        'most elkezdem feldolgozni egy tábla szerveren változott rekordjait
                        Private l As List = m.Get(tbl)
                        Private wheremap As Map
                        wheremap.Initialize
                        For Each item As Map In l
                            If realtabla = "cikkt" Then
                                'fordítani kell bizonyos mezőket
                                If item.ContainsKey("cikkcsop_id") Then
                                    item.Put("csopazon",item.Get("cikkcsop_id"))
                                    wheremap.Put("azon",item.Get("azon"))
                                End If
                            else If realtabla = "cikkcsop" Then
                                'fordítani kell bizonyos mezőket
                                If item.ContainsKey("id") Then
                                    item.Put("azon",item.Get("id"))
                                    wheremap.Put("azon",item.Get("id"))
                                End If
                            End If
                            'kiszedem azokat a kapott mezőket, amik nálam nincsenek
                            Private datamap As Map
                            datamap.Initialize
                            For di = 0 To item.Size-1
                                If tblfields.ContainsKey( item.GetKeyAt(di) ) Then
                                    If tblfields.Get(item.GetKeyAt(di)).As(String).ToUpperCase = "DATETIME" Then
                                        datamap.Put(item.GetKeyAt(di),Codebase.UTCtoString(item.GetValueAt(di)))
                                    Else
                                        datamap.Put(item.GetKeyAt(di),item.GetValueAt(di))
                                    End If
                                End If
                            Next
                            'megnézem a where alapján van-e már létező tétel
                            Private sa(wheremap.Size) As String
                            di = 0
                            sb.Append("SELECT count(*) FROM ").Append(realtabla).Append(" WHERE ")
                            For Each col As String In wheremap.Keys
                                If di > 0 Then sb.Append(" AND ")
                                sb.Append(col).Append(" = ?")
                                sa(di) = wheremap.Get(col)
                                di = di + 1
                            Next
                            Private vane As Int = sqll.ExecQuerySingleResult2(sb.ToString, sa)
                            If vane > 0 Then
                                DBUtils.UpdateRecord2(sqll,realtabla,datamap,wheremap)
                            Else
                                DBUtils.InsertMaps(sqll,realtabla,Array As Object(datamap))
                            End If
                            sb.Remove(0,sb.Length)
                            Log(item)
                        Next
                    End If
                Next
                sqll.TransactionSuccessful
                Codebase.savTxtError("FRISSTORZS","Sikeres törzsfrissítés")
            Catch
                Log(LastException)
                Codebase.savTxtError("FRISSTORZS",LastException.Message)
                sqll.EndTransaction
            End Try
        End If
    Catch
        Codebase.savTxtError("FRISSTORZS 2",LastException.Message)
    End Try
End Sub

Thanks in advance
Steve

 

[Jeffrey Cameron]

Sqlite isn't designed to be a multi-user/thread database. You might try moving your begin transaction statement down to where you actually start writing and see if that helps.

To avoid issues like this I generally I have a single SqliteManager class that handles all of the I/O requests sequentially in a queue and raises events back to the caller with the results.

 

[rosippc64a]

The idea is good, but my application is too complex (and used by many) to completely rewrite. Anyway, I opened two new sql objects but that didn't help either, however, by eliminating the transaction, it no longer gives the error and works fine. This is definitely not the right solution, but maybe there will be more ideas...