An application I wrote long ago that used File_DB for short-term persistent multithreaded storage had a few issues with concurrency. I debated rewriting the script to use BerkeleyDB which included proper file locking, but, decided to use Sqlite3 instead as it was closer to SQL and would eliminate a bit of code.
The transition was relatively easy. Writing self-test functions worked well and a few bugs were dealt with along the way. Most of the issues were getting used to Sqlite3’s quirks, but, all in all the code worked fine. Multiple tests with multiple terminal windows resulted in everything working as expected including locking tables, concurrency issues and removing a logic error on the prior application.
First startup of the application resulted in a rather strange result which didn’t make a lot of sense. I chalked that up to something I had done during testing, deleted the sqlite3 database file and restarted the application.
The application started, set it self as a daemon and detached from the terminal. I sent a task to the daemon, and bam. It seemed to work, it complained of a length error in the unpack which meant there was some data that didn’t get retrieved correctly from the database. A second task was sent and the error received was even stranger. Trying to connect to sqlite3 through the command line resulted in:
sqlite> select * from tasks;
SQL error: database disk image is malformed
Ok, something broke badly.
I checked and doublechecked my code with perl running in strict mode and could find nothing that would cause this. It seems that the packaged version of sqlite3 in debian’s packaged perl is not compiled with threading enabled.
I missed that when I was digging through the library configs and will have to build that package for testing. I did want to avoid using the BerkeleyDB library and move to Sqlite3, but, I think in the interest of finishing this project a little sooner, I will rewrite the code and adjust the locking and use Sqlite3 in the future.
Sqlite3 works very well with SQLAlchemy and TurboGears, but, in this case, it didn’t quite solve the problem that I needed solved.