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.
Where in the user hard disk resides the .sqlite data base file ?
- it is a single user database, right?
- it is in a read/write authorized file area?
- it doesn’t have a read only attribute set?
there may also be a lock file that became corrupted and can’t be deleted…
my bet is sqlite file shared between users …?
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.
// 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 IfThat ^
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.
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.
Two instances on the same machine should work but doing it over any sort of network is unwise.
You should look at How To Corrupt An SQLite Database File
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.
The only times I’ve see this is when I had a Transaction that mistakenly wasn’t coded to Commit.
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)
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.
[quote=435712:@Steve Wilson]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. [/quote]
Can be done all in Xojo using the open source AloeExpress xojo project.
- karen