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

91–100 of 189 posts

Re: SQLite concurrency and why you should care about it

#91

Post by o11c »

mangecoeur wrote: post #28

Sqlite is a great bit of technology but sometimes I read articles like this and think, maybe they should have used postgres. I you don’t specifically need the “one file portability” aspect of sqlite, or its not embedded (in which case you shouldn’t have concurrency issues), Postgres is easy to get running and solves these problems.

Even with postgres, you don't have to use the system instance; there's nothing stopping you from running the server as a child process.

You probably need to support this for your testsuite anyway.

Re: SQLite concurrency and why you should care about it

#92

Post by Multicomp »

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…

Would the SQLite vacuum function help with that?

Re: SQLite concurrency and why you should care about it

#93

Post by mceachen »

Multicomp wrote: post #92
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…

Would the SQLite vacuum function help with that?

You can VACUUM INTO, ~~but standard vacuum won’t rewrite the whole db~~ (vacuum rewrites the whole db)

https://sqlite.org/lang_vacuum.html

(Edit: if multiple processes are concurrently reading and writing, and one process vacuums, verify that the right things happen: specifically, that concurrent writes from other processes during a vacuum don’t get erased by the other processes’ vacuum. You may need an external advisory lock to avoid data loss).

Re: SQLite concurrency and why you should care about it

#94

Post by FrinkleFrankle »

throwaway894345 wrote: post #59
thayne wrote: post #53

Earlier quoted context omitted.

Using postgres would make it significantly more complicated for Jellyfin users to install and set up Jellyfin. And then users would need to worry about migrating the databases when PostgreSQL has a major version upgrade. An embedded database like sqlite is a much better fit for something like Jellyfin.

As a Jellyfin user, this hasn’t been my experience. I needed to do a fair bit of work to make sure Jellyfin could access its database no matter which node it was scheduled onto and that no more than one instance ever accessed the database at the same time. Jellyfin by far required more work to setup maintainably than any of the other applications I run, and it is also easily the least reliable application. This isn’t…

Care to share your setup?

Re: SQLite concurrency and why you should care about it

#95

Post by probst »

summarity wrote: post #57
simonw wrote: post #21

Earlier quoted context omitted.

Even with that pattern (which I use too) you still need to ensure those write operations always start a transaction at the beginning in order to avoid SQLITE_BUSY.

Yes, indeed. In my apps, which are mostly Nim, my pool manager ensures this always happens - along with a host of other optimizations. I often start with barebones SQLite and then later switch to LiteSync (distributed SQLite with multi-master replication), so I keep the lock management at the app level to adapt to whatever backend I'm using.

I am really curious about LiteSync. Any chance you could share a bit on your experiences with it (recognising it’s somewhat off-topic…). Do you run with multiple primaries? What sort of use cases do you reach to it for? Conflict resolution seems a bit simplistic at first glance (from the perspective of someone very into CRDTs), have you experienced any issues as a result of that?

Re: SQLite concurrency and why you should care about it

#96

Post by hamandcheese »

o11c wrote: post #91
mangecoeur wrote: post #28

Sqlite is a great bit of technology but sometimes I read articles like this and think, maybe they should have used postgres. I you don’t specifically need the “one file portability” aspect of sqlite, or its not embedded (in which case you shouldn’t have concurrency issues), Postgres is easy to get running and solves these problems.

Even with postgres, you don't have to use the system instance; there's nothing stopping you from running the server as a child process. You probably need to support this for your testsuite anyway.

Maybe in theory. In practice, most people who need Postgres for their test suite will boot an instance in a docker container in CI, and maybe just assume a system version is available for local dev.

Re: SQLite concurrency and why you should care about it

#97

Post by asa400 »

kijin wrote: post #39
asa400 wrote: post #2

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

Wouldn't that "fix" make the problem worse on the whole, by making transactions hold onto write locks longer than necessary? (Not trying to disagree, just curious about potential downsides.)

It’s a reasonable question!

In WAL mode, writers and readers don’t interfere with each other, so you can still do pure read queries in parallel.

Only one writer is allowed at a time no matter what, so writers queue up and you have to take the write lock at some point anyway.

In general, it’s hard to say without benchmarking your own application. This will get rid of SQLITE_BUSY errors firing immediately in the situation of read/write/upgrade-read-to-write scenario I described, however. You’d be retrying the transactions that fail from SQLITE_BUSY anyway, so that retrying is what you’d need to benchmark against.

It’s a subtle problem, but I’d rather queue up writes than have to write the code that retries failed transactions that shouldn’t really be failing.

Re: SQLite concurrency and why you should care about it

#98

Post by didip »

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…

This is fascinating. What would be the solution for this? You can’t ask users to defrag.

Re: SQLite concurrency and why you should care about it

#99

Post by return_to_monke »

mceachen wrote: post #93
Multicomp wrote: post #92

Earlier quoted context omitted.

Would the SQLite vacuum function help with that?

You can VACUUM INTO, ~~but standard vacuum won’t rewrite the whole db~~ (vacuum rewrites the whole db) https://sqlite.org/lang_vacuum.html (Edit: if multiple processes are concurrently reading and writing, and one process vacuums, verify that the right things happen: specifically, that concurrent writes from other processes during a vacuum don’t get erased by the other processes’ vacuum. You may need an external advi…

> You can VACUUM INTO, but standard vacuum won’t rewrite the whole db.

This is not true. From the link you posted:

> The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file.

Re: SQLite concurrency and why you should care about it

#100

Post by zie »

morshu9001 wrote: post #64
abound wrote: post #48

Earlier quoted context omitted.

Jellyfin is a self-hostable media server. If they "used Postgres", that means anyone who runs it needs Postgres. I think SQLite is the better choice for this kind of application, if one is going to choose a single database instead of some pluggable layer

Exactly, there are use cases where SQLite makes sense but you also want to make it faster. I really don't get why there isn't a more portable Postgres.

There is, you can even run PG under wasm if you are desperate. :)

SQLite is probably the better option here and in most places where you want portability though.

Post reply on HN