When 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
SQLite concurrency and why you should care about it
11–20 of 189 posts
Re: SQLite concurrency and why you should care about it
#12[deleted]
Re: SQLite concurrency and why you should care about it
#13SQLite 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.
Do you know any good default PRAGMAs that one should enable?
Re: SQLite concurrency and why you should care about it
#14Curious 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.
Re: SQLite concurrency and why you should care about it
#15Curious 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 handl…
Edit: 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.
Re: SQLite concurrency and why you should care about it
#16SQLite 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.
Seems like it's asking to be forked
Re: SQLite concurrency and why you should care about it
#17SQLite 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.
Do you know any good default PRAGMAs that one should enable?
These 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 defer_foreign_keys is useful if you understand the pros and cons of enabling it.
Re: SQLite concurrency and why you should care about it
#18In 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 th…
Yeah 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/
Re: SQLite concurrency and why you should care about it
#19> 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?
Re: SQLite concurrency and why you should care about it
#20SQLite 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.
Do you know any good default PRAGMAs that one should enable?
Explanation of sqlite performance PRAGMAs