5 ms·

Sorry to ask an obvious question, but are both users committing their transactions? Does the other user requery after a transaction?

• wila 10y ago
  Yeah, they both can write, although almost all of the writes are done by the daemon process and the normal user (the GUI process) reads and processes the results. The 'database is locked' problem seemed to happen most while the daemon user is writing and the normal user is reading. For the moment I added a patch to my apps whereby the applications handle the locking by itself at a slightly higher level as I got a bit tired of the problem. This is done via a separate lock file that is opened exclusively before any write action and closed after the write. By doing that I can simply delay the reads for a bit when the GUI process tests to open the lock file and that appears to have cured most problems. It's a tiny bit more advanced as the above, but that's basically it and it appears to have cured most issues. edit: might have misread your question, was it about the WAL journal mode? Yes the processes do commit the transactions they write. I need the results immediately, not after sqlite decides to process the WAL journal.
  • domador 10y ago
    No, the question was not about WAL journal mode, but about running the SQLite COMMIT command. You may wish to take a look at the last section (Transaction Control At The SQL Level) of the following page. Maybe it's relevant to your case: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html
    • wila 10y ago
      Thanks, I had read that part before though. When staying in normal journal mode the app sees the data just fine and the data is committed directly in that case. Updates/Deletes are all pretty much instant and any queries results are correct. There might be an issue with the database drivers I depend on (FireDAC) in that layer I even go as far as closing the tables on each query/update after a commit. The problem with normal journal mode is the lock error popping up. When I switch to WAL journal mode the data no longer appears to be written directly even when turning autocommit back on. So while WAL mode appears to fix the lock issue, the data only gets committed on closing the database connection. As a result the GUI process can't interact with the daemon process anymore as it only sees old data. Opening and closing the database on each insert/update/delete to force the data to be written simply isn't an option.
      • domador 10y ago
        Quite a thorny issue! I wish you the best as you look for a good solution.
        • wila 10y ago
          Thanks! Looks like the manual locking that I added around reading/writing the data is working out OK. Time and a lot of testing will have to tell. Despite all that I still like SQLite a lot. It's so incredibly easy to deploy and runs on all the platforms my app is targeting.
        • wila 10y ago
          An interesting issue as it turns out. The reason this was happening was not because of SQLite, but due to how the FireDAC driver handles the locking. That driver had a setting "BusyTimeout" which supposedly takes care of a lock waiting time. According to the documentation it has a default timeout setting of 10 seconds. That clearly did not work, I even had set it manually, still to no effect. The other day I figured to try and set this via an SQLite pragma setting... (busy_timeout) I've not seen a "database is locked" issue since then and I've completely removed my manual locking layer, so "case solved" and it certainly wasn't SQLite to blame.