effectively unlocking an SQLite database

Question

I have a program which frequently crashes (Zotero), leaving its SQLite database locked and inaccessible for use by the restarted program unless I reboot (which I really want to avoid). So this does not work (using the generic name, db.sqlite in place of the actual file in my case, zotero.sqlite):

sqlite3 db.sqlite
sqlite> .backup main backup.sqlite
Error: database is locked

Based on an answer here, I tried:

echo ".dump" | sqlite3 db.sqlite | sqlite3 db.sqlite2
mv db.sqlite2 db.sqlite

db.sqlite2 is 0KB so this obviously did not work. Inspired by another thread, I also tried

echo '.dump' | sqlite3 db.sqlite > db.dump
cat db.dump | sqlite3 db.sqlite2
mv db.sqlite2 db.sqlite

which leads to db.sqlite2 which is slightly smaller in filesize to db.sqlite, and Zotero (the file which requires the database) was not able to recognize its contents.

So this seems like a very brute force way of doing it, but worked:

cp -pv db.sqlite db.sqlite2
rm -f db.sqlite
mv db.sqlite2 db.sqlite

I wonder if there are any drawbacks to this solution and why the other methods are proposed before this one.

Answer 1

It may be that for transaction control zotero uses table based locking to prevent database inconsistencies during concurrent access. When one user uses the database it locks the tables so only they can use it and prevent them seeing the database in an inconsistent state. However when it crashes it's not releasing these locks.

In a well setup database upon crashing the transaction should be rolled back (undone) and locks released to prevent data being left in an inconsistent state or the tables being locked and you having to manually release them.

I've read on the internet that storing the database on a Network File System can interfere with SQLites locking mechanism, by changing the database location to a none network location you should be able to avoid these problems unless it's somewhere else.

If it is somewhere else I would recommend making a copy of your database and using the zotero database repair tool to try and identify any faults in set up etc that could cause this and repair them: https://www.zotero.org/utils/dbfix/

I hope this helps.