Menu
I've an unfortunate situation where multiple Perl processes write and read the same SQLite3 database at the same time.
This often causes Perl processes to crash as two processes would be writing at the same time, or one process would be reading from the database while the other tries to update the same record.
Does anyone know how I could coordinate the multiple processes to work with the same sqlite database?
I'll be working on moving this system to a different database engine but before I do that, I somehow need to fix it to work as it is.
859
SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds.
The ATTACH statement is an SQLite extension that allows two or more databases to be associated to the same database connection and to operate as if they were a single database. The number of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default.
Usually, SQLite allows at most one writer to proceed concurrently. The BEGIN CONCURRENT enhancement allows multiple writers to process write transactions simultanously if the database is in "wal" or "wal2" mode, although the system still serializes COMMIT commands.
Yes SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds - so for most uses this does not matter).
SQLite is designed to be used from multiple processes. There are some exceptions if you host the sqlite file on a network drive, and there maybe a way to compile it such that it expects to be used from one process, but I use it from multiple processes regularly. If you are experiencing problems, try increasing the timeout value. SQLite uses the filesystem locks to protect the data from simultaneous access. If one process is writing to the file, a second process might have to wait. I set my timeouts to 3 seconds, and have very little problems with that.
Here is the link to set the timeout value
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
