Missing from this, but very important: you can connect to the same SQLite database file multiple times inside the same process (e.g. in different threads) but you can also connect to the same SQLite database file from multiple different processes on the same machine (with the same filesystem - NFS will lead to all kinds of nasty problems).
Easy way to prove this to yourself: run two different terminals, run "sqlite3 data.db" in each one, run create table / insert / update queries in one and watch the results become visible to selects in the other.
Changes only become visible to other connections after they have been committed.
In default mode any writes will hold an exclusive lock and will cause any reads to be blocked waiting for that lock. In WAL mode reads can continue even while writes are taking place.
Writes can only run one at a time, but can queue waiting for each other to finish - and since most writes take microseconds to run this is very rarely a problem.
The most complicated thing to understand is the need to use BEGIN IMMEDIATE TRANSACTION in concurrent environments to avoid the occasional SQLITE_BUSY exception. The best explanation I've seen of this is here: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t... - search for SQLITE_BUSY.
In short, whatever database you are using, get familiar with the concurrency behaviors, modes, and limitations. They are all slightly (or sometimes significantly) different even if your ORM pretends otherwise.
Really important caveat, cause dear [whatever you pray to] will it ever blow up in your face on NFS storage. I worked at a company that tried to store production-deployed code as state in a globally-reachable SVN repo on NFS… someone(s) at each location had write access, and every so often the underlying SQLite3 file would go ping in a catastrophic manner.
Love SQLite, ludicrously underrated and ridiculously flexible, and the problems we had started way before that was deployed, but yeah stay away from a file system without reliably atomic locking.
Right, should have mentioned this was in the pre-WAL mode days. SVN repos were never intended for wanton and masochistic abuse anything like we were doing.
sqlite on nfs from several processes on the same host should work. I think... Perhaps... maybe... (but why then are you using nfs?) It is just where it is unable to use the shared memory lock system that it starts corrupting. At least I hope, I don't use much sqlite myself, but I like having a nfs home directory and a lot of applications use sqlite.
I once worked at a place where they were using a database(unidata) that would error out if it detected it was being run on nfs. so they got around this problem by... running it on... smb. Sigh... I was like, you guys know why it does not want to run on nfs right.... smb will have the same problems, the database is just not explicitly looking for smb. if you ever write from both systems it will blow up the database and you have a very good chance to get incorrect data on reads. but the database team liked it and just dealt with the fires as they started. so it stuck.
The issue has since been fixed somehow (after being outstanding for 6 years).
The Django docs gaslights the developer into thinking it's of some SQLite limitations:
"SQLite is meant to be a lightweight database, and thus can’t support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration."
But that's not true, the issue could be reproduced with just two "concurrent" connections given unfortunate timing.
Since I was stubborn enough to not switch to another database (for production loads) and I knew sqlite could handle loads of a couple requests per sec, I'm still salty that Django basically introduced random errors to a service I maintain just because they didn't want to fix the problem (and lied to me in the docs).
Interesting history on that ticket. Closed as "invalid" 7 years ago, reopened 10 months ago, and 6 months ago added as non-default option to git (but not released yet?).
I wonder what the downsides would be in enabling this by default. Would lit engthen the period of the write lock on the DB so this change could be a compatibility problem for existing systems?
If it's write heavy, and you need to use transactions for writes, you're screwed either way. (i.e. don't use sqlite)
If it's not write heavy, but you enabled `ATOMIC_REQUESTS = True` then all your requests will be serialized (which is obviously bad)
If your site does not write often and your reads don't require transactions, using BEGIN IMMEDIATE TRANSACTION is probably fine. Your transactional writes will still be serialized but if they don't happen often it's probably fine for a low/medium-low traffic site.
"If it's write heavy, and you need to use transactions for writes, you're screwed either way. (i.e. don't use sqlite)"
That depends on your definition of "write heavy". Given SQLite's performance I would expect anything below 1,000 writes a second to work just fine even with write locks, given moderately capable hardware.
I just dealt with a lot of sqlite concurrency stuff recently. When I first got started, a lot of recommendations were that apps should create a connection and hold that connection its the duration. This works great but I feel like it's worth noting that this can get dangerous when you're working in a multi-threaded context.
You can have one thread in the middle of stepping through the results of a SELECT prepared statement and if another thread resets the statement and runs it again, that original thread will start stepping through the results from the other thread's call. Or if both threads begin a transaction, you'll get nested transaction errors.
Things get really confusing, especially because you might not notice the problems until the app is running on a slower machine.
In my case, it's using default serialized mode. I would assume I was safe judging by "In serialized mode, API calls to affect or use any SQLite database connection or any object derived from such a database connection can be made safely from multiple threads"
But in the unit tests I have setup, it hasn't been the case, at least with prepared statements being shared between threads.
Yes that's exactly how it works in serialized build/mode.
Are you sure not ultimately compiling with -DSQLITE_THREADSAFE=0 or 2?
Also the serialized setting is distinct from the isolation level.
sqlite has a famously extensive test suite and is effectively one of the most heavily tested libraries ever, and there are multithread tests in the suite. If your tests are triggering some other kind of race/undefined behavior there is also that to consider.
Just for clarification, did you have one connection per thread or one global connection?
If it's the latter your error would make sense since you created a race condition, databases connections aren't magic, they are still just file descriptors without synchronisation, most ORMs/frameworks put the magic in there for you.
SQLite database connection objects are not "just file descriptors" and with the default serialization build they are appropriately locked to allow multi-threaded use. In that sense, they are magic.
I think we are talking past each other and see what you’re saying.
Also rereading the initial comment it is now obvious what is going on.
For some reason I thought they were claiming weird interactions between statement objects (which would occur if serialization was not used). It appears they are just sharing a single statement object between threads and expecting that to work.
I read past that because it never occurred someone working with the API would think that would work. It would imply some kind of thread local storage, and as you are aware that is definitely not something commonly done on APIs of this type.
> And there is no confusion about who owns what?
What you are describing is a higher level of race condition of course, but not one where the internal state of SQLite is inconsistent or breaking invariants. Specifically there is no confusion about ownership: there is one tx (stack) per connection and it is owned by the connection, to which access is serialized by the library.
In SQLite you implicitly open a transaction with any statement so it’s not necessarily a problem to be “starting a transaction” since the implicit start will be idempotent. And you can certainly use this behavior to run concurrent reads and writers across multiple threads on a single connection without issue.
Yes if you try to run multiple BEGIN statements that will error out. You’re right some ORMs do some fancy magic over a connection to serialize those. Sorry that was not the initial problem I was addressing (though obviously the GP had confusion in this area too)
I 100% would believe what you described in this comment is expected behaviour, it makes sense to me.
And yeah I was thinking about this a decent amount today and thought the only way you could even begin doing this is with thread local storage but could not think why they would do that concidering how widely supported (platform wise) sqlite is.
I think what you're describing is what I was confused about: sharing prepared statement objects across multiple threads.
It makes sense why it would not work but, at least in my case, it was an easy place to find myself when combining the "best practices" of prepared statements and a single shared connection.
Thanks for the clear interpretation of what's going on. Until now, it just seemed like a "gotcha." But I always knew it was user error and that sqlite was too battle tested for it to be a bug.
There are benefits to having a single writer connection (and multiple readers).
One is that you can get fairer and more efficient locking. The (interruptible) file lock APIs that SQLite uses lead to a lot of sleeping and retrying that wastes time, and is not very fair.
Yeah pretty light for HN content, especially in an area like SQLite which has great visibility into how stuff works.
Though technically I guess it's right? Maybe some value for those who know nothing about SQLite and have dismissed it out of hand for lots of things it is actually very good at (like backing most websites that aren't unicorn scale).
In general I agree, but in this case, even those three sentences could be very useful to many.
I remember when I first started playing with SQLite, looking at some project using it, browsing some examples, skimming the SQL side of the docs - I had an impression that it's customary to open a single connection to a given SQLite database, and use it throughout the entire program. And I mean, it feels logical - it's a file database, and you wouldn't fopen() the same file independently several times and write to it when there's even a remote chance you'll end up doing so concurrently, even on a single thread.
So it's valuable to spell it out up front that yes, you can and should open multiple connections to the same SQLite database.
It has unfair properties, there is no lock queue and workers could starve forever trying to get lock in high contention context. It's very easy to get busy timeout. You have to use external proper lock if you want to use IMMEDIATE mode. A peculiar thing: with external write lock (flock) you could get a true serializability without errors.
IMPLICIT (default) transaction mode in other hand could be tricky to manage, because you could get integrity error on commit.
I don't know exactly what you mean, but sqlite probably supports your use case.
sqlite's defaults balance performance, flexibility, and backwards compatibility. They don't work for every case, but there is almost always a lever to pull.
The downside is you have to learn them. The docs are really excellent, though.
true, concurrency, let alone parallelism, never worked for me when i tried it. never used in anywhere. sounds good in theory but it is unusable for most real world cases. there are endless key-value databases suitable for 90% of what sqlite does. and if you truly need multiple indexes per table and joins, just use a proper rdbms and stop playing around.
CREATE TABLE etc in SQLite are fully transactional, as are updates to PRAGMA user_version which you can use to store a logical schema version number. I built Notion’s SQLite migration system for client devices on those primitives.
Easy way to prove this to yourself: run two different terminals, run "sqlite3 data.db" in each one, run create table / insert / update queries in one and watch the results become visible to selects in the other.
Changes only become visible to other connections after they have been committed.
In default mode any writes will hold an exclusive lock and will cause any reads to be blocked waiting for that lock. In WAL mode reads can continue even while writes are taking place.
Writes can only run one at a time, but can queue waiting for each other to finish - and since most writes take microseconds to run this is very rarely a problem.
The most complicated thing to understand is the need to use BEGIN IMMEDIATE TRANSACTION in concurrent environments to avoid the occasional SQLITE_BUSY exception. The best explanation I've seen of this is here: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t... - search for SQLITE_BUSY.