Multiuser Real Database (SQLiteDatabase) Keeps Blocking

I have software that I have now implemented as multiuser using the Database.MultiUser=True command. A Database.shm and a Database.wal (write ahead log) file are created on the server. The issue I have is that the write ahead file keeps blocking and not syncing its data to the database. As I understand it this is because there are commits that need to happen before the data can be written to the database. I have placed a button in the software that loops through Database commits until I get a database error. Oddly enough I sometimes see the database accept 2 - 3 commits before throwing an error. After that the write ahead log appears to synch to the database.
Unfortunately the write ahead file keeps blocking despite my best intentions (e.g. after every database update I perform a commit). I am trying to find a way of preventing this from happening. It seems that even when the software is idle the write ahead log gets written too and again the database can no longer accept updates from the other clients. Does anyone have good examples/methodologies of implementing multiuser SQLiteDatabase functionality?

Personally, I wouldn’t use SQLite in multi-user mode. I would move to a database server. Or, at the very least perhaps move to CubeSQL Server which uses SQLite but is designed with multiple users in mind.

In addition, I believe the SQLite has issues with being on remote drives. I seem to remember that the recommendation was to always have it on the local drive. This might be out of date but something to look into as well.

We use the RealSQLDatabase in a multi-user environment and haven’t had any issues. The application is local and the database in on a network mapped drive. We have 5 or so users accessing the database most of the day and 1 or 2 actually writing to the DB. We have about 20,000 records in the database. The RealSQLDatabase is based on SQLite, but I believe the prior version to the version that “technically” allows multi-user. So in our case the Multi-User is false, but it still works.

We do error checking after every update, if there is an error, we retry a few times then alert the user. Our updates and commits are very tight and execute very fast. I track and log the errors to see if things are going off the rails and I can’t say I have seen more than a few errors that were automatically corrected on a retry, the user isn’t even alerted.

Not saying that this is “ideal” or a solution for a much larger application with millions of records, only what my experience has been.

The SQLiteDatabase.MultiUser property doesn’t actually allow multiple users to access the database at one time. It just enables write-ahead logging (WAL) so that writes happen more quickly. Quicker writes will allow multiple users to work with SQLite better because it does lock during writing so the shorter the write, the better. But you do not need it set to True to have multiple users access the DB.

With that said, I don’t see why you would be getting this blocking behavior if you are actually doing a Commit after any DB changes. Could a thread or something be modifying the DB often causing it to frequently lock?

As a test I suggest you turn off all of the multi-user stuff and try it. There is no reason your DB should be locked for more than a few milliseconds during an actual write, then commit. The key is your code should not being doing any updates to the DB unless you can do the update and commit it very fast.

Thanks for all of the replies… You have given me some good ideas.

I use Studio Stable DataBase and it works fine