Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite concurrency and why you should care about it

jellyfin.org

Post reply on HN
Print view View original

161–170 of 189 posts

Re: SQLite concurrency and why you should care about it

#161

Post by johannes1234321 »

nasretdinov wrote: post #157
apitman wrote: post #139

Earlier quoted context omitted.

The necessity of this sort of tribal knowledge kills a lot of the simplicity of sqlite for me. Honestly it seems to have a lot of footguns. I've tried to understand proper concurrent use of sqlite with Golang about 5 times and never come away feeling like I actually get it.

With Go it's quite straightforward actually: use WAL mode + two connection pools, one for reads and the other, with MaxConnections set to 1, for writes. This way you should never encounter any concurrency issues, and Go will serialise writes for you too

I have no experience in Go, but won't that be a full database lock and prevent transactions with more than one operation?

Re: SQLite concurrency and why you should care about it

#162

Post by crazygringo »

dv35z wrote: post #14

Curious 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…

It should just work.

If one thread is writing another thread tries to write, the first thread will have the file write lock, and the second thread will wait to write until that lock is released.

I've written code using the pattern you describe and it's totally fine.

Re: SQLite concurrency and why you should care about it

#163

Post by Daniel_sk »

ricardobeat wrote: post #29

Articles 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.

I 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.

Re: SQLite concurrency and why you should care about it

#164

Post by tredre3 »

ErroneousBosh wrote: post #135
EionRobb wrote: post #90

One 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…

> One of the biggest contributors I've had in the past for SQLite blocking was disk fragmentation. Is that even still a thing? I thought modern filesystems like ext4 were supposed to be largely immune to that.

Ext4 isn't used on Android, and it isn't immune to fragmentation.

The way ext4 reduces fragmentation is with some basic heuristics: mainly, it spreads files across the full disk instead of finding the next free spot. So they have room to grow without fragmenting. When the space gets low, it fragments just as badly as older file systems unfortunately.

Re: SQLite concurrency and why you should care about it

#165

Post by thayne »

throwaway894345 wrote: post #122
thayne wrote: post #113

Earlier quoted context omitted.

That's just not true. Distributed software is much more complicated and difficult than non-distributed software. Distributed systems have many failure modes that you don't have to worry about in non-distributed systems. Now maybe you could have an abstraction layer over your storage layer that supports multiple data stores, including a distributed one. But that comes with tradeoffs, like being limited to the least co…

I’m a distributed systems architect. I design, build, and operate distributed systems. > Distributed systems have many failure modes that you don't have to worry about in non-distributed systems. Yes, but as previously mentioned, those failure modes are handled by abiding a few simple principles. It’s also worth noting that multiprocess or multithreaded software have many of the same failure modes, including the one…

> just target storage interfaces that can be easily distributed—things like Postgres

But as I mentioned above, that makes the system more complicated for people who don't need it to be distributed.

Setting up separate db software, configuring the connection, handling separate updates, etc. is a lot more work for most users than Jellyfin just using a local embedded sqlite database. And it would probably make the application code more complicated as well.

Re: SQLite concurrency and why you should care about it

#167

Post by throwaway894345 »

heavyset_go wrote: post #159
throwaway894345 wrote: post #154

Earlier quoted context omitted.

They have clients for nearly every device; it’s clearly intended to be a streaming media server.

It's a local media library manager in the same vein as media servers that came before it that were intended to run on desktops and serve up content to consoles and whatever on your LAN back when that was the thing to do. My point is to treat it like software from that lineage and you won't have a problem, trying to treat it like something it's not, like a distributed web app, will lead to issues.

It feels like we’re saying similar things. We both agree that its architecture makes it difficult to run with high availability, although I’ll point out that the issues documented in the article apply to single nodes and even on a single node it has pretty specific hardware requirements. I think we just disagree about whether “you have to hold it very carefully and it works just fine” is a good thing or not.

Re: SQLite concurrency and why you should care about it

#168

Post by throwaway894345 »

thayne wrote: post #165
throwaway894345 wrote: post #122

Earlier quoted context omitted.

I’m a distributed systems architect. I design, build, and operate distributed systems. > Distributed systems have many failure modes that you don't have to worry about in non-distributed systems. Yes, but as previously mentioned, those failure modes are handled by abiding a few simple principles. It’s also worth noting that multiprocess or multithreaded software have many of the same failure modes, including the one…

> just target storage interfaces that can be easily distributed—things like Postgres But as I mentioned above, that makes the system more complicated for people who don't need it to be distributed. Setting up separate db software, configuring the connection, handling separate updates, etc. is a lot more work for most users than Jellyfin just using a local embedded sqlite database. And it would probably make the appli…

> But as I mentioned above, that makes the system more complicated for people who don't need it to be distributed. Setting up separate db software, configuring the connection, handling separate updates, etc. is a lot more work for most users than Jellyfin just using a local embedded sqlite database.

You can package a Postgres database with your app just like SQLite. Users should not have to know that they are using Postgres much less configuring connections, handling updates, etc.

> And it would probably make the application code more complicated as well.

Not at all, this is an article about the hoops the application has to jump through to make SQLite behave well with parallel access. Postgres is designed for parallel access by default. It’s strictly simpler from the perspective of the application.

Re: SQLite concurrency and why you should care about it

#169

Post by throwaway894345 »

apitman wrote: post #158
throwaway894345 wrote: post #155

Earlier quoted context omitted.

That’s a bit of a strange argument considering all the hoops one needs to jump through to make Jellyfin work on account of Sqlite. I just want to run the software I use on the computers I have.

You're having issues because you're trying to shoehorn it into your desired architecture. Most people just want to run an app on their Windows laptop and start streaming their videos.

Maybe that’s what most users want, but that’s not what the software was designed to target, judging from all of the documentation and marketing. But yes, clearly the software wasn’t designed to run in a distributed fashion, and that’s kind of the point of my criticism—they had to go out of their way to couple their application in a way that precludes distributed execution. Well designed server software is trivial to distribute, and even if you never run it in a distributed configuration it makes it easy to do the basic parallelism described in this article.

Re: SQLite concurrency and why you should care about it

#170

Post by sgbeal »

gwking wrote: post #109
simonw wrote: post #18

Earlier quoted context omitted.

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/

One 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 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.…

> 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.

Post reply on HN