4 ms·
I have found that corruption of SQLite files in practice is a very difficult thing to achieve. On Windows, the worst I've ever seen is a wall of "Database File
by bob1029 7y ago
I have found that corruption of SQLite files in practice is a very difficult thing to achieve. On Windows, the worst I've ever seen is a wall of "Database File is Locked" which was resolved with a rewrite of the DB access logic. After much experimentation, I have found that the happy path for SQLite is as follows:
1. Use a SQLite distribution that is compiled or otherwise configured for serialized threading mode (this is the default in most cases).
2. Use a single connection from a single process to access the SQLite database file. This connection can be safely shared between multiple threads with optional locking as required (due to the guarantees offered by #1 above).
3. Transactions are handled with locking in the application code (i.e. on the SQLiteConnection instance), not in the database. This is predominantly required for inserts with PK generation, but also applicable in cases where you don't want 2 different threads to interleave updates during a certain multi-step operation (e.g. debiting one account and crediting another).
So far, we have found this to be the most performant and simple way to get business data in and out of a SQLite database file. The "one connection per operation" approach is NOT ideal for SQLite because there is an actual database file that needs a handle created/closed each time. One connection per makes sense when you are using a hosted database designed for this access model.
- catalogia 7y agoI've found WAL mode with one writer thread and as many reader threads as you want works pretty well.
- bob1029 7y agoYes, absolutely use WAL mode whenever feasible. Does wonders for performance.
- blattimwind 7y agoIn WAL mode you can write from multiple threads without any problem, but you need to keep serializability in mind. Specifically, no concurrent write transactions are allowed. In other words, you can't have a transaction start at X, have another transaction start at X+1, issue DML and commit, and expect your original transaction from X being able to commit - it will never be able to. Some SQLite wrappers (notably, Python's) mess with the start and rollbacks of transactions and make this appear buggy.
- oefrha 7y ago> Some SQLite wrappers (notably, Python's) mess with the start and rollbacks of transactions and make this appear buggy. @coleifer (author of peewee) has a pretty informative blog post exploring — in part — transactional semantics, which might be of value to people who want to know more about this. https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ https://charlesleifer.com/blog/going-fast-with-sqlite-and-py...
- mox1 7y ago+1 for @coleifer, peewee is an awesome ORM, he knows his DB stuff very well.
- platz 7y agoEven in WAL mode, you can still get a SQLITE_BUSY error even when just trying to open a new connection. The application I worked on could not tolerate these busy exceptions so we had to acquire an exclusive global mutex before attempting to open new connections to sqlite and make sure to release it at soon as the operation was done.
- liuliu 7y agoWAL mode doesn't really have "read-only" connection (if you try to open a read-only connection by marking as READ_ONLY with sqlite_open_v2, it will error out for WAL mode). A "supposedly" read-only connection can end up doing writes for example, consolidating WAL log into the main DB file. That can cause SQLITE_BUSY errors. You pretty much have to set the sqlite_busy_timeout in any cases.
- stephen82 7y agoThey are working on it; please check for 'begin-concurrent' and 'wal2' branch names here https://www.sqlite.org/cgi/src/brlist https://www.sqlite.org/cgi/src/brlist
- kccqzy 7y agoPython's wrapper is one of the worst. It attempts to parse the SQL you are trying to execute and automatically send BEGIN or COMMIT to the database. Setting the "isolation_level" property (badly named) to None makes things better.
- harikb 7y agoIf you have to handle (write code for) 2 and 3, why isn’t something like Postgres better?
- cheez 7y agoIt's a good question. For me, lack of yet-another-service.
- bob1029 7y agoThis is my primary answer as well. I have no problem with Postgres as a solution, but I will only invite additional complexity into my life when absolutely required by the business constraints. SQLite is dead simple compared to Postgres (or any other hosted solution). I can copy a SQLite .db file from a production machine to my local box and be reviewing rows/running queries within seconds. Also, sharing SQLite databases with other users & developers or even using them as a contract between systems is a very compelling set of use cases. Finally, going from a blank windows machine to a working development (or production) environment is very quick if your application exclusively uses SQLite to store its data. You simply stop thinking about databases after a while because everything just works.
- jbverschoor 7y agoFor me, the single file is the nr 1 reason. It sluist works, the file is the data.. very clear.
- ebg13 7y agoI can answer this. Adding a queue thread to your executable is infinitely easier to redistribute and run than something that requires the user to install a persistent postgres daemon. There are premade libraries for several languages that provide thread safe query queueing for sqlite transparently as drop in imports so you don't even really need to think about it. See e.g. https://github.com/dashawn888/sqlite3worker https://github.com/dashawn888/sqlite3worker (not mine)
- barrkel 7y ago
- tracker1 7y agoPosted in another thread... but I discovered how the FS in Docker for (Windows/Mac) worked the hard way. If you use a mounted volume in Win/Mac it does a syncing, not a direct mount/share. This lags and isn't a great option for live databases and if you're accessing the same SQLite db outside the container and inside, you will see corruption happen. In any case, it was definitely an interesting discovery... since then, I only use shared volumes with the host as backup/restore targets, never for shared anything.
- zeeZ 7y agoI run Plex on my Windows desktop as a container, with everything I want to persist mounted in (this includes the database). If I don't stop the container before hibernating my PC, I'm pretty much guaranteed to have some kind of database corruption on the next start.
- tracker1 7y agoI'd consider a volume container for your database, the content is probably safe enough, since it's not a lot of heavy write. Then have a timed event that copies the DB out of the volume container for backups regularly.
- tracker1 7y agoIt's also worth noting, that using a DB in a shared volume, even if you don't touch it outside the container, is MUCH slower... I was doing this for mssql containers and when I stopped, my spinup for a fresh instance went from nearly 90 seconds to about 15.
- xienze 7y ago> I have found that corruption of SQLite files in practice is a very difficult thing to achieve. Not in the slightest. Stick your Plex DB on NFS and it’ll get messed up in very short order.
- justinclift 7y agoWonder if it changing some of the NFS mount options (caching, sync, etc) would make it better? And/or options known to cause corruption for this Plex DB usage pattern. eg: "Don't use options foo, bar, bazz with PlexDB over NFS, as corruption will occur".
- ta999999171 7y agoPeople who know what NFS is will be on Emby or similar before I finish writing this comment.