4 ms·

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 Cont

by 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.

by domador 10y ago

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.