SQLite Database locked error

  1. 4 months ago

    Craig G

    May 8 Australia

    I recently updated an old project from RealSQLDatabase to SQLiteDatabase. Although the desktop app has worked fine for many years users are intermittently getting an "SQL error: Database is locked" error when they open it.
    The only solution is to restore a backup.

  2. Emile S

    May 8 Europe (France, Strasbourg)

    Where in the user hard disk resides the .sqlite data base file ?

  3. Dave S

    May 8 San Diego, California USA
    Edited 4 months ago
    1. it is a single user database, right?
    2. it is in a read/write authorized file area?
    3. it doesn't have a read only attribute set?

    there may also be a lock file that became corrupted and can't be deleted...

  4. Jean-Yves P

    May 8 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    my bet is sqlite file shared between users ...?

  5. Rick A

    May 8 (Brazil. GMT-3:00)
    Edited 4 months ago

    Any chance of people running 2 instances of your single user app side by side? Maybe there is a copy running... or had. If that's the case, put a mutex check at the start of the app and quit the second instance rising before the first access. Closing all the instances of your app accessing this DB should release it. If it doesn't, a reboot should.

  6. Dave S

    May 8 San Diego, California USA

    @Rick A If that's the case, put a mutex check at the start of the app and quit the second copy

    // APP.OPEN
    zMutex = New Mutex("MutexExample")
    
    If Not zMutex.TryEnter Then
    MsgBox("You cannot have more than one copy of this app running!")
    zMutex = Nil
    Quit
    End If
  7. Rick A

    May 8 (Brazil. GMT-3:00)
    Edited 4 months ago

    That ^

    But make sure "zMutex" is a global property of the type Mutex (can be an App.Property), that exists all the app running life holding the mutex lock. Also, make an exclusive app string like "MyGreatAppNamedXThing", something unique to your app.

  8. Craig G

    May 9 Australia

    @Rick A Any chance of people running 2 instances of your single user app side by side? Maybe there is a copy running... or had. If that's the case, put a mutex check at the start of the app and quit the second instance rising before the first access. Closing all the instances of your app accessing this DB should release it. If it doesn't, a reboot should.

    As far as I know there is only one user but it is possible to open the same file with two instances of the app at the same time. I'm sure users have been doing this for a long time as some access it simultaneously over their home network.
    I haven't heard of problems. It's never happened before I upgraded to SQLiteDatabase.
    Rebooting normally fixes it although some databases are permanently corrupt and can never be opened.
    I think the database close process is not completing before the app quits and it leaves it in an inaccessible state.

  9. Tim S

    May 9 Canterbury, UK

    @Craig G As far as I know there is only one user but it is possible to open the same file with two instances of the app at the same time. I'm sure users have been doing this for a long time as some access it simultaneously over their home network.

    Two instances on the same machine should work but doing it over any sort of network is unwise.

    You should look at https://www.sqlite.org/howtocorrupt.html

  10. Rick A

    May 9 (Brazil. GMT-3:00)
    Edited 4 months ago

    Sqlite, in the same machine, globally locks writes. Any writing process will get an exception (or error code) due being "locked" or being "busy" when trying it from DIFFERENT connections in the same machine, as it serializes operations from the SAME connection. Always use the same connection in your app for SQLite IO. I would never try accessing it from different machines, as you'll get 2 SQLite libs not knowing the states of each other; this is a scenario for a DB server like PostgreSQL. I think that one way of preventing a bit more "bad" things in the same machine is making everything containing CREATES, INSERTS, DELETES and UPDATES inside a TRANSACTION block and in case of a fail do a rollback() to release it and try again later. Always look for lock errors, that means that you couldn't have committed your data in a SQLite "multi-user" environment. You can try later, but there are some cases where you can lock a DB and not unlock it, like an app crash. A reboot could "fix" that for a while, while you take care of inspecting crashes.

  11. Ralph A

    May 9 Santa Monica, California

    The only times I've see this is when I had a Transaction that mistakenly wasn't coded to Commit.

  12. Rick A

    May 9 (Brazil. GMT-3:00)

    You'll need to commit() or rollback() to put the DB back into READY state; or the DB can be "busy" or "write locked" waiting. If you write a full transaction sequence like "BEGIN TRANSACTION; INSERT...; UPDATE...; SELECT...; END TRANSACTION;" the commit() is embedded. You just need to worry about a DB.Error() and call Rollback() to undo any possible pending things avoiding it to become busy/locked. For example, a bad select clause will generate an error and end the sequence without reaching the "END TRANSACTION". You will notice the fail looking into DB.ERROR(), then take some (fast) action, call DB.Rollback(), and move on.

    That's why I would expect people not doing things like:

    MsgBox("Error: " + DB.ErrorMessage)
    DB.Rollback

    And instead something like:

    Dim e As String = DB.ErrorMessage
    DB.Rollback
    MsgBox("Error: " + e)

  13. I recommend wrapping your SQLite database access in a class that has flags to denote that:
    A) You've established a connection to the database
    B) You've commenced a transaction that has yet to be commited or rolled back
    Then in the destructor you can check whether you have a pending transaction to rollback and a connection to close.

    Also if you want to share a SQLite database you really need to roll your own server application.
    I've created a multithreaded JSON-RPC server (runs with PyPy though not Xojo) that works with a SQLite database and that buzzes along quite nicely. Because any data that needs writing arrives as a whole encapsulated in a JSON object and the server is on the same physical machine as the database each thread's connection to the database usually only exists for a matter of milliseconds. I think that's the only way I'd consider sharing SQLite really.

  14. Karen A

    May 9 Pre-Release Testers
    Edited 4 months ago

    @Steve W Also if you want to share a SQLite database you really need to roll your own server application.
    I've created a multithreaded JSON-RPC server (runs with PyPy though not Xojo) that works with a SQLite database and that buzzes along quite nicely.

    Can be done all in Xojo using the open source AloeExpress xojo project.

    - karen

or Sign Up to reply!