11 ms·
SQLite concurrency and why you should care about it
- asa400 11mo agoIn SQLite, transactions by default start in “deferred” mode. This means they do not take a write lock until they attempt to perform a write. You get SQLITE_BUSY when transaction #1 starts in read mode, transaction #2 starts in write mode, and then transaction #1 attempts to upgrade from read to write mode while transaction #2 still holds the write lock. The fix is to set a busy_timeout and to begin any transaction that does a write (any write, even if it is not the first operation in the transaction) in “immediate” mode rather than “deferred” mode. https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-locked https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-lo...
- tlaverdure 11mo agoYes, these are both important points. I didn't see any mention of SQLITE_BUSY in the blog post and wonder if that was never configured. Something that people miss quite often.
- BobbyTables2 11mo agoThats the best explanation I’ve seen of this issue. However, it screams of a broken implementation. Imagine if Linux PAM logins randomly failed if someone else was concurrently changing their password or vice versa. In no other application would random failures due to concurrency be tolerated. SQLite is broken by design; the world shouldn’t give them a free pass.
- asa400 11mo agoSQLite is a truly remarkable piece of software that is a victim both of its own success and its unwavering commitment to backward compatibility. It has its quirks. There are definitely things we can learn from it.
- mickeyp 11mo agoIndeed. Everyone who uses sqlite will get burnt by this one day and spend a lot of time chasing down errant write-upgraded transactions that cling on for a little bit longer than intended.
- BinaryIgor 11mo agoSQLite has its quirks, but in this particular case all you need is set PRAGMA busy_timeout=<a few seconds> and the problem is solved; and if you google it, it's widely known issue with described (this) solution. It's just weird that it's set to 0 by default rather than something resonable like 3000 or 5000 ms.
- summarity 11mo agoI've always tried to avoid situations that could lead to SQLITE_BUSY. SQLITE_BUSY is an architecture smell. For standard SQLite in WAL, I usually structure an app with a read "connection" pool, and a single-entry write connection pool. Making the application aware of who _actually_ holds the write lock gives you the ability to proactively design access patterns, not try to react in the moment, and to get observability into lock contention, etc.
- mickeyp 11mo agoI mean, you're not wrong, and that is one way to solve it, but the whole point of a sensibly-designed WAL -- never mind database engine -- is that you do not need to commit to some sort of actor model to get your db to serialise writes.
- sethev 11mo agoThese are performance optimizations. SQLite does serialize writes. Avoiding concurrent writes to begin with just avoids some overhead on locking.
- mickeyp 11mo ago"performance optimisation" --- yeees, well, if you don't care about data integrity between your reads and writes. Who knows when those writes you scheduled really get written. And what of rollbacks due to constraint violations? There's we co-locate transactions with code: they are intertwined. But yes, a queue-writer is fine for a wide range of tasks, but not everything. It's that we need to contort our software to make sqlite not suck at writes that is the problem.
- sethev 11mo agoThis is just FUD. The reason SQLite does locking to begin with is to avoid data corruption. Almost every statement this blog post makes about concurrency in SQLite is wrong, so it's little surprise that their application doesn't do what they expect. >Who knows when those writes you scheduled really get written When a commit completes for a transaction, that transaction has been durably written. No mystery. That's true whether you decide to restrict writes to a single thread in your application or not.
- simonw 11mo agoYeah I read the OP and my first instinct was that this is SQLITE_BUSY. I've been collecting posts about that here: https://simonwillison.net/tags/sqlite-busy/ https://simonwillison.net/tags/sqlite-busy/
- gwking 11mo agoOne tidbit that I don't see mentioned here yet is that ATTACH requires a lock. I just went looking for the documentation about this and couldn't find it, especially for WAL mode (https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html mentions the super-journal, but the WAL docs do not mention ATTACH at all). I have a python web app that creates a DB connection per request (not ideal I know) and immediately attaches 3 auxiliary DBs. This is a low traffic site but we have a serious reliability problem when load increases: the ATTACH calls occasionally fail with "database is locked". I don't know if this is because the ATTACH fails immediately without respecting the normal 5 second database timeout or what. To be honest I haven't implemented connection pooling yet because I want to understand what exactly causes this problem.
- sgbeal 11mo ago> I have a python web app that creates a DB connection per request (not ideal I know) FWIW, "one per request per connection is bad" (for SQLite) is FUD, plain and simple. SQLite's own forum software creates one connection per request (it creates a whole forked process per request, for that matter) and we do not have any problems whatsoever with that approach. Connection pools (with SQLite) are a solution looking for a problem, not a solution to a real problem.
- benhurmarcel 11mo agoWhere can I read more about this? I use connection pools with SQLite, I’m interested if I can simplify.
- sgbeal 11mo ago> Where can I read more about this? There's nothing specific to read about it, just plenty of anecdotal evidence. People use connection pools because connecting to _remote_ databases is slow. SQLite _is not remote_. It's _in-process_ and _fast_. Any connection-pool _adds_ to the amount of work needed to get an SQLite instance going. It's _conceivable_ that pooling _might_ speed it up _just a tad_ for databases with _very large schemas_ because parsing the schema (which is not done at open-time, but when the schema is first needed) can be "slow" (maybe even several whole milliseconds!).
- kijin 11mo agoWouldn't that "fix" make the problem worse on the whole, by making transactions hold onto write locks longer than necessary? (Not trying to disagree, just curious about potential downsides.)
- asa400 11mo agoIt’s a reasonable question! In WAL mode, writers and readers don’t interfere with each other, so you can still do pure read queries in parallel. Only one writer is allowed at a time no matter what, so writers queue up and you have to take the write lock at some point anyway. In general, it’s hard to say without benchmarking your own application. This will get rid of SQLITE_BUSY errors firing immediately in the situation of read/write/upgrade-read-to-write scenario I described, however. You’d be retrying the transactions that fail from SQLITE_BUSY anyway, so that retrying is what you’d need to benchmark against. It’s a subtle problem, but I’d rather queue up writes than have to write the code that retries failed transactions that shouldn’t really be failing.
- chasil 11mo agoIn an Oracle database, there is only one process that is allowed to write to tablespace datafiles, the DBWR (or its slaves). Running transactions can write to ram buffers and the redo logs only. A similar design for SQLite would design for only one writer, with all other processes passing their SQL to it.
- liuliu 11mo agoNote that busy_timeout is not applicable to SQLite in this case (the SQLITE_BUSY issued immediately, no wait in this case). Also this is because WAL mode (and I believe only for WAL mode, since there is really no concurrent reads in the other mode). The reason is because pages in WAL mode appended to a single log file. Hence, if you read something inside a BEGIN transaction, later wants to mutate something else, there could be another page already appended and potentially interfere with the strict serializable guarantee for WAL mode. Hence, SQLite has to fail at the point of lock upgrade. Immediate mode solves this problem because at BEGIN time (or more correctly, at the time of first read in that transaction), a write lock is acquired hence no page can be appended between read -> write, unlike in the deferred mode.
- BinaryIgor 11mo agoAlso worth mentioning - it happens more often when you set journal_mode=WAL, which is not a default. The default is DELETE mode, where the rollback journal is deleted at the conclusion of each transaction. What's more - in this mode (not-WAL), readers can coexist, but they do block the writer (which is always one) and the writer block readers - concurrency is highly limited. In WAL mode - which pretty much always you should set - there's also at most one writer, but writer can coexist with readers.
- mickeyp 11mo agoSQLite is a cracking database -- I love it -- that is let down by its awful defaults in service of 'backwards compatibility.' You need a brace of PRAGMAs to get it to behave reasonably sanely if you do anything serious with it.
- tejinderss 11mo agoDo you know any good default PRAGMAs that one should enable?
- mickeyp 11mo agoThese are my PRAGMAs and not your PRAGMAs. Be very careful about blindly copying something that may or may not match your needs. PRAGMA foreign_keys=ON PRAGMA recursive_triggers=ON PRAGMA journal_mode=WAL PRAGMA busy_timeout=30000 PRAGMA synchronous=NORMAL PRAGMA cache_size=10000 PRAGMA temp_store=MEMORY PRAGMA wal_autocheckpoint=1000 PRAGMA optimize <- run on tx start Note that I do not use auto_vacuum for DELETEs are uncommon in my workflows and I am fine with the trade-off and if I do need it I can always PRAGMA it. defer_foreign_keys is useful if you understand the pros and cons of enabling it.
- deleted 11mo ago[deleted]
- stefanos82 11mo agoWhen hctree [1] becomes stable in SQLite, it will be the only database I will be using lol! I presume the `hc` part in project's code name should be High Concurrency. [1] https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html
- andersmurphy 11mo agoThing is if you design your app to have a single writer you can probably get higher throughput than multiple writers in concurrency mode.
- deleted 11mo ago[deleted]
- dv35z 11mo agoCurious if anyone has strategies on how to perform parallel writes to an SQLite database using Python's `multiprocessing` Pool. I am using it to loop through a database of 11,000 words, hit an HTTP API for each (ChatGPT) and generate example sentences for the word. I would love to be able to asynchronously launch these API calls and have them come back and update the database row when ready, but not sure how to handle the database getting hit by all these writes from (as I understand it) multiple instances of the same Python program/function.
- mickeyp 11mo agoEdit: disregard. I read it as he'd done it and had contention problems. You can't. You have a single writer - it's one of the many reasons sqlite is terrible for serious work. You'll need a multiprocessing Queue and a writer that picks off sentences one by one and commits it.
- hruk 11mo agoThis is just untrue - the naive implementation (make the API call, write a single row to the db) will work fine, as transactions are quite fast on modern hardware. What do you consider "serious" work? We've served a SaaS product from SQLite (roughly 300-500 queries per second at peak) for several years without much pain. Plus, it's not like PG and MySQL are pain-free, either - they all have their quirks.
- mickeyp 11mo agoEdit: disregard. I read it as he'd done it and had contention problems. I mean it's not if he's got lock contention from BUSY signals, now is it, as he implies. Much of his issues will stem from transactions blocking each other; maybe they are long-lived, maybe they are not. And those 3-500 queries --- are they writes or reads? Because reads is not a problem.
- hruk 11mo agoRoughly 80/20 read to write. On the instance's gp3 EBS volume (which is pretty slow), we've pushed ~700 write transactions per second without much problem.
- porridgeraisin 11mo ago> So an application that wants to use SQLite as its database needs to be the only one accessing it. No. It uses OS level locks. fcntl(). You can access it from how many ever processes. The only rule is, single writer (at a time). > When another part of the application wants to read data, it reads from the actual database, then scans the WAL for modifications and applies them on the fly. Also wrong. WAL does not contain modifications, it contains the full pages. A reader checks the WAL, and if it finds the page it won't even read the DB. It's a bit like a cache in this sense, that's why shared cache mode was discouraged in favour of WAL (in addition to its other benefits). Multiple versions of a page can exist in the WAL (from different transactions), but each reader sees a consistent snapshot which is the newest version of each page up to its snapshot point. > For some reason on some systems that run Jellyfin when a transaction takes place the SQLite engine reports the database is locked and instead of waiting for the transaction to be resolved the engine refuses to wait and just crashes You can set a timeout for this - busy_timeout. > Reproducible There's nothing unreliable here. It will fail every single time. If it doesn't, then the write finished too fast for the read to notice and return SQLite busy. Not sure what they are seeing. > The solution So they've reimplemented SQLites serialisation, as well as SQLites busy_timeout in C#? > "engine", "crash" Sqlite is not an engine. It's literally functions you link into your app. It also doesn't crash, it returns sqlite_busy. Maybe EF throws an exception on top of that. I have to say, this article betrays a lack of fundamental DB knowledge and only knowing ORMs. Understand the DB and then use the ORM on top of it. Or atleast, don't flame the DB (context: blame-y tone of article) if you haven't bothered to understand it. Speaking of ORMs ... > EF Core You're telling me that burj khalifa of abstractions doesn't have room to tune SQLite to what web devs expect?
- yellow_lead 11mo agoC# devs*
- porridgeraisin 11mo agoDidn't mean to belittle any 'X' developer. By "what web devs expect", I meant the settings that are usually used for databases in web apps.
- ignoramous 11mo agoSo, I decided on three locking strategies: No-Lock Optimistic locking Pessimistic locking As a default, the no-lock behavior does exactly what the name implies. Nothing. This is the default because my research shows that for 99% all of this is not an issue and every interaction at this level will slow down the whole application. Aren't the mutexes in the more modern implementations (like Cosmo [0]) & runtimes (like Go [1]) already optimized so applications can use mutexes fearlessly? [0] https://justine.lol/mutex/ https://justine.lol/mutex/ [1] https://victoriametrics.com/blog/go-sync-mutex/ https://victoriametrics.com/blog/go-sync-mutex/
- keyliejener 11mo ago[dead]
- mangecoeur 11mo agoSqlite is a great bit of technology but sometimes I read articles like this and think, maybe they should have used postgres. I you don’t specifically need the “one file portability” aspect of sqlite, or its not embedded (in which case you shouldn’t have concurrency issues), Postgres is easy to get running and solves these problems.
- eduction 11mo ago100%. I specifically clicked for the “why you should care” and was disappointed I could not find it. I certainly don’t mind if someone is pushing the limits of what SQLite is designed for but personally I’d just rather invest the (rather small) overhead of setting up a db server if I need a lot of concurrency.
- abound 11mo agoJellyfin is a self-hostable media server. If they "used Postgres", that means anyone who runs it needs Postgres. I think SQLite is the better choice for this kind of application, if one is going to choose a single database instead of some pluggable layer
- morshu9001 11mo agoExactly, there are use cases where SQLite makes sense but you also want to make it faster. I really don't get why there isn't a more portable Postgres.
- zie 11mo agoThere is, you can even run PG under wasm if you are desperate. :) SQLite is probably the better option here and in most places where you want portability though.
- deleted 11mo ago[deleted]
- tombert 11mo ago
- ricardobeat 11mo agoArticles like this leave me with an uneasy feeling that the “solutions” are just blind workarounds - more debugging/research should be able to expose exactly what the problem is, now that would be something worth sharing.
- kccqzy 11mo agoArticles like this give me the feeling that the author did a little bit of research and shared a suboptimal solution, and was hoping that experts on HN would present better solutions. Wasn't there a saying about how the best way to get correct answers is to post not just the question but the wrong answers to it?
- deleted 11mo ago[deleted]
- npodbielski 11mo agoIf something is stupid but it works then it is not stupid. If this will help them find a solution then sure why not. Though I am wondering if this would not be easier to just use postgress and focus on features instead.
- apitman 11mo agoCunningham's Law
- Daniel_sk 11mo agoI am pretty sure in this case even Claude or ChatGPT would give them the correct answer quickly or at least it would point them to the right direction (the busy-timeout pragma) with 5 minutes of work.
- Leherenn 11mo agoA bit off topic, but there seems to be quite a few SQLite experts here. We're having troubles with memory usage when using SQLite in-memory DBs with "a lot" of inserts and deletes. Like maybe inserting up to a 100k rows in 5 minutes, deleting them all after 5 minutes, and doing this for days on end. We see memory usage slowly creeping up over hours/days when doing that. Any settings that would help with that? It's particularly bad on macOS, we've had instances where we reached 1GB of memory usage according to Activity Monitor after a week or so.
- asa400 11mo agoAre you running vacuums at all? auto_vacuum enabled at all? https://sqlite.org/lang_vacuum.html https://sqlite.org/lang_vacuum.html
- porridgeraisin 11mo agoIn memory DBs don't have anything to vacuum. However... what you (and OP) are looking for might be pragma shrink_memory [1]. [1] https://sqlite.org/pragma.html#pragma_shrink_memory https://sqlite.org/pragma.html#pragma_shrink_memory
- asa400 11mo agoAh, you're correct. I read too fast and missed that it was in-memory databases specifically!
- kachapopopow 11mo agosounds like normal behavior of adjusting buffers to better fit the usecase, not sure if it applies to sqlite or if sqlite even implements dynamic buffers.
- pstuart 11mo agoIf you're deleting all rows you can also just drop the table and recreate it.
- deleted 11mo ago[deleted]
- ddtaylor 11mo agoI have encountered this problem on Jellyfin before. It works like a dream, but there are some very strange circumstances that can cause the database to become locked and then just not work until I restart the docker container. If I check the logs it just says stuff about the database being locked. It happens quite rarely and seems to be when we fidget in the menus on the smart TV like starting to watch a show to realize it's the wrong episode as you click the button, then spam the back button, etc.
- thayne 11mo agoThere seem to be some misunderstandings in this: > If your application fully manages this file, the assumption must be made that your application is the sole owner of this file, and nobody else will tinker with it while you are writing data to it. Kind of, but sqlite does locking for you, so you don't have to do anything to ensure your process is the only one writing to the db file. > [The WAL] allows multiple parallel writes to take place and get enqueued into the WAL. The WAL doesn't allow multiple parallel writes. It just allows reads to be concurrent with a single write transaction.
- Sammi 11mo agoYeah... I adore Sqlite and upvote anything about it, but I couldn't upvote this article because it was just so poorly informed. It gets the very basics on sqlite concurrency wrong.
- yread 11mo agoI'm a bit confused. The point of this article is that the author used .NET Interceptors and TagWith to somehow tag his EF Core operations so that they make their own busy_timeout (which EF Core devs think is not necessary https://github.com/dotnet/efcore/issues/28135 https://github.com/dotnet/efcore/issues/28135 ) or do a horrible global lock? No data is presented on how it improved things if it did. Nor is it described which operations were tagged with what. The only interesting thing about it are the interceptors but that's somehow not discussed in HN's comments at all.
- deleted 11mo ago[deleted]
- fitsumbelay 11mo agoVery helpful and a model for how technical posts should be written: clarity, concision, anchor links that summarize the top lines. It was a pleasure to read.
- tombert 11mo agoDoes this mean I can finally load-balance with multiple Jellyfin instances? A million years ago, back when I still used Emby, I was annoyed that I couldn't use it across multiple in Docker Swarm due to locking of SQLite. It really annoyed me, enough to where I started (but never completed) a driver to change the DB to postgres [1]. I ended up moving everything over to a single server, which is mostly fine unless I have multiple people transcoding at the same time. If this is actually fixed then I might have an excuse to rearchitect my home server setup again. [1] https://github.com/Tombert/embypostgres https://github.com/Tombert/embypostgres
- Yodel0914 11mo agoJellyfin have just gone through a massive refactor and pulled all their data access code into EFCore. This opens the path for supporting different RBDMSs which think is next on their list.
- keyliejener 11mo ago[dead]
- EionRobb 11mo agoOne of the biggest contributors I've had in the past for SQLite blocking was disk fragmentation. We had some old Android tablets using our app 8 hours a day for 3-4 years. They'd complain if locking errors and slowness but every time they'd copy their data to send to us, we couldn't replicate, even on the same hardware. It wasn't until we bought one user a new device and got them to send us the old one that we could check it out. We thought maybe the ssd had worn out over the few years of continual use but installing a dev copy of our app was super fast. In the end what did work was to "defrag" the db file by copying it to a new location, deleting the original, then moving it back to the same name. Boom, no more "unable to open database" errors, no more slow downs. I tried this on Jellyfin dbs a few months ago after running it for years and then suddenly running into performance issues, it made a big difference there too.
- Multicomp 11mo agoWould the SQLite vacuum function help with that?
- mceachen 11mo agoYou can VACUUM INTO, ~~but standard vacuum won’t rewrite the whole db~~ (vacuum rewrites the whole db) https://sqlite.org/lang_vacuum.html https://sqlite.org/lang_vacuum.html (Edit: if multiple processes are concurrently reading and writing, and one process vacuums, verify that the right things happen: specifically, that concurrent writes from other processes during a vacuum don’t get erased by the other processes’ vacuum. You may need an external advisory lock to avoid data loss).
- return_to_monke 11mo ago> You can VACUUM INTO, but standard vacuum won’t rewrite the whole db. This is not true. From the link you posted: > The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file.
- keyliejener 11mo ago[dead]
- slashdave 11mo agoI am a little confused, but maybe I am missing some context? Wouldn't using a proper database be a lot easier than all of this transaction hacking? I mean, is Postgres that hard to use?
- rpcope1 11mo agoDo these guys really not understand that WAL is still single writer multi reader? You could do concurrent (but not parallel) write DML in both the normal and WAL journaling models. WAL alleviates read transactions being blocked by writers but you still have to lock it down to a single writer. It would be nice if SQLite3 had full blown MVCC, but it still works if you understand it.