5 ms·
OK I love SQLite but there has been a long-standing misconception, or issue, that I'd like to get clarification on: Is using SQLite from multiple processes safe
by stavros 5y ago
OK I love SQLite but there has been a long-standing misconception, or issue, that I'd like to get clarification on: Is using SQLite from multiple processes safe?
For a long while, either SQLite itself or the Python bindings weren't safe for concurrent access, is this still the case? Can I use SQLite for my Django app? With the backup system on the Tailscale post yesterday, the operational burden is much much lower than Postgres for many use cases.
- drunkpotato 5y agoThis is just what I’ve heard so take with a grain of salt, but as I understand it sqlite is “mostly” safe to use concurrently. There are situations where it doesn’t behave correctly under concurrent load, the one I’ve heard about being when the sqlite is on a network-mounted drive. I’d love to hear from someone who knows more though.
- stavros 5y agoThat's kind of expected, I think. Due to its design, SQLite relies on filesystem semantics to provide atomicity. If the filesystem doesn't provide the semantics, it makes sense that atomicity will fail. I'm more asking whether it's still unsafe to use in a filesystem that DOES provide those semantics.
- tyilo 5y agoMy experience is that it is only safe to have one connection to a SQLite database. Having multiple connections mostly works, however if the database is stored on a slow medium, such as an SD-card, concurrent queries will fail.
- stavros 5y agoHmm, fail completely, or just block?
- tyilo 5y agoOne the queries will fail with something like SQLITE_BUSY
- benbjohnson 5y agoYou need to explicitly set the “busy_timeout” PRAGMA to allow concurrent write queries to block under WAL mode. Otherwise the query will return a BUSY error. I wrote up some tips/caveats on the Litestream site with more explanation. https://litestream.io/tips/ https://litestream.io/tips/ Generally there’s just a few settings you want to set when developing with SQLite: PRAGMA journal_mode = wal; PRAGMA busy_timeout = 5000; Also worth setting STRICT mode and setting SYNCHRONOUS to NORMAL but those are less important.
- tyingq 5y agoIt appears to be safe, but potentially slow, assuming you have working fcntl() locking, that is, for example, not using NFS. And you have to handle SQLITE_BUSY or set a timeout. More in their FAQ, Q5: https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5 There's also "BEGIN CONCURRENT" with WAL mode: https://sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md https://sqlite.org/src/doc/begin-concurrent/doc/begin_concur...
- stavros 5y agoInteresting, thanks! EDIT: Looks like it has a simple read/write lock, which isn't great for write-heavy workloads, but that's up to the application developer to decide. BEGIN CONCURRENT looks great, however.
- tored 5y agoHome page of SQLite uses SQLite concurrently. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html If it exist a concurrency problem it should therefore either be in the bindings, operating system or the file system. I only used SQLite in a non-concurrent fashion from PHP (single use command line scripts). It should be easy to disprove with a concurrent test program if you can reliably tell when concurrency has failed.
- Grimburger 5y agoWith WAL it's a generally smooth experience. Really depends on your use case though. https://sqlite.org/wal.html https://sqlite.org/wal.html
- magicalhippo 5y agoThe WAL doesn't work with shared directories (NFS/SMB) though. Found this out when I tried to store the Plex data directory on a NSF share in a VM and it had really weird issues. Turned out Plex uses SQLite with WAL enabled.
- Grimburger 5y agoThat's point number two in the disadvantages: > WAL does not work over a network filesystem. Somewhat strange for plex to ship with that by default given how many people generally have network shares for such things.
- stavros 5y agoThat looks great, thanks. I didn't realize it wasn't on by default, but it sounds like it'll greatly benefit the web app use case, where all web workers are on the same Linux machine. Sounds like WAL is the way to go in most use cases, unless you want shared access from different machines.
- polyrand 5y ago> Is using SQLite from multiple processes safe? Yes [0]. > The Python bindings weren't safe for concurrent access, is this still the case? I think they're safe now [1]. The error message when using the connection from multiple threads is "outdated" [2]. [0]: https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html [1]: https://bugs.python.org/issue45613 https://bugs.python.org/issue45613 [2]: https://docs.python.org/3/library/sqlite3.html#sqlite3.threadsafety https://docs.python.org/3/library/sqlite3.html#sqlite3.threa...
- stavros 5y agoExcellent, thank you! Combined with WAL mode, it sounds like SQLite is eminently usable for most of my use cases.
- meribold 5y ago> The error message when using the connection from multiple threads is "outdated" I think typically you'd still want to use one connection per thread because (for example) there can only be one transaction per connection at any given time.
- chaxor 5y agoYeah it's safe. The biggest problem SQLite has is it's size limitations. It can only hold ~281 TB in a database unfortunately. If you need more storage than that - that's the only reason I could endorse someone using a different database. :P
- stavros 5y agoHaha, well, Postgres does have some niceties like isolation levels, JSON fields, etc.
- groue 5y agoIt is always safe, and by "safe" I mean "safe for data". You won't have to deal with data corruption. Precisely, see "How To Corrupt An SQLite Database File": https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html Now concurrent accesses from different processes/connections can lead to runtime errors (SQLITE_BUSY), because the database happens to be locked by one connection. Those errors are greatly reduced by the WAL mode (https://sqlite.org/wal.html https://sqlite.org/wal.html) which provides ultra-robust single-writer/multiple-readers semantics: - Writes can not happen concurrently (SQLITE_BUSY). - One can reduce the occurrences of such SQLITE_BUSY errors by using a built-in timeout (https://www.sqlite.org/c3ref/busy_timeout.html https://www.sqlite.org/c3ref/busy_timeout.html). - Several reads can happen concurrently, including with writers. - A writer connection can enter the "Serializable" isolation level. - A reader connection can enter the "Snapshot Isolation" level. For more details, see https://www.sqlite.org/isolation.html https://www.sqlite.org/isolation.html During all the years I've been developing the GRDB library (https://github.com/groue/GRDB.swift https://github.com/groue/GRDB.swift), I could never see SQLite fail its documented guarantees. This made it possible to build one of the most concurrency-focused SQLite toolkit for Swift, and I'm pretty happy with it (https://github.com/groue/GRDB.swift/blob/master/Documentation/Concurrency.md https://github.com/groue/GRDB.swift/blob/master/Documentatio...).
- stavros 5y agoOh excellent, I wasn't sure the "snapshot isolation" level existed (the WAL kind of implied it, but I wasn't sure). SQLITE_BUSY only happens if a write lock is kept for longer than the readers' configured timeout, right? It shouldn't happen for short writes?
- groue 5y agoWhat's cool with WAL mode is that SQLITE_BUSY won't happen for readers (except very rare scenarios: https://www.sqlite.org/wal.html#sometimes_queries_return_sqlite_busy_in_wal_mode https://www.sqlite.org/wal.html#sometimes_queries_return_sql...) One should only expect SQLITE_BUSY for writes (if a writer is already holding the lock, and the busy timeout expires before the other writer releases the lock). So yes, prefer short writes, or adjust your timeout. Generally speaking, SQLITE_BUSY can not be 100% prevented for writes.