SQLite is not a single connection database
igorstechnoclub.com
igorstechnoclub.com
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.
https://code.djangoproject.com/ticket/29280
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."
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).
It's been great since.
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.
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.
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.
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.
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.
https://www.sqlite.org/threadsafe.html
You must use serialized mode or provide your own concurrency control.
But in the unit tests I have setup, it hasn't been the case, at least with prepared statements being shared between threads.
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.
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.
If it's the former then yeah that makes no sense.
THREAD 1: Start transaction on Dbconnection - Success
THREAD 2: Start transaction on Dbconnection - Success
And there is no confusion about who owns what?
I don't see any way in the SQLite C api for that to not be a race condition and would require two connections for one of them not to throw an error.
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)
Commenting too late at night.
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.
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.
https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-ba...
Another some APIs, like the backup API, work better if they can track writes, and (e.g.) backing up from the single writer connection helps there.
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).
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.
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.
Are you sure about that? You are talking about the single most widely deployed piece of software ever.