3 ms·
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 y
by simonw 2y ago
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-the-how-and-why-of-optimal-performance/ https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t... - search for SQLITE_BUSY.
- hnfong 2y agoSame in Django. Glad we were not alone! https://code.djangoproject.com/ticket/29280 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." 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).
- pbronez 2y agoIs this resolved now? How well is the SQLite + Django stack working for you?
- hnfong 2y agoWhen I found out the underlying issue, I basically applied the patch suggested in the ticket. It's been great since.
- medmunds 2y agoDjango would likely welcome a correction or clarification to the docs. (And "they" seem to appreciate help fixing problems.)
- fulafel 2y agoInteresting 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?
- hnfong 2y agoIt depends on the usage patterns of your 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) 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.
- simonw 2y ago"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.
- yawpitch 2y ago> NFS will lead to all kinds of nasty problems 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.
- chasil 2y agoYou cannot use WAL mode over NFS. All connections are required to map shared memory for WAL to work, which precludes network file sharing in general.
- yawpitch 2y agoRight, 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.
- somat 2y agosqlite 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.
- SoftTalker 2y agoIn 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.