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

51–60 of 189 posts

Re: SQLite concurrency and why you should care about it

#51

Post by bambax »

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.

Jellyfin is a media server app that gets installed on a great variety of platforms and while it would certainly be possible to add a postgres server to the install, the choice of sqlite is more than justified here IMHO.

Re: SQLite concurrency and why you should care about it

#52

Post by throwaway894345 »

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.

As a user of Jellyfin, I’m very sad that it doesn’t just use Postgres. I basically have to run an NFS system just for Jellyfin so that its data can be available to it no matter which node it gets scheduled on and also that there are never multiple instances running at the same time, even during deployments (e.g., I need to take care that deployments completely stop the first Jellyfin instance before starting the subsequent instance). There are so many unnecessary single points of failure, and Postgres would make a pretty big one go away (never mind addressing the parallelism problems that plague the developers).

Jellyfin is by far the least reliable application I run, but it also seems to be best in class.

Re: SQLite concurrency and why you should care about it

#53

Post by thayne »

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.

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.

Re: SQLite concurrency and why you should care about it

#54

Post by masklinn »

porridgeraisin wrote: post #38
mickeyp wrote: post #17

Earlier quoted context omitted.

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 Note that I do not use auto_vacuum for DELETEs are uncommon in my w…

You should pragna optimize before TX end, not at tx start. Except for long lived connections where you do it periodically. https://www.sqlite.org/lang_analyze.html#periodically_run_pr...

Also foreign_keys has to be set per connection but journal_mode is sticky (it changes the database itself).

Re: SQLite concurrency and why you should care about it

#55

Post by mickeyp »

sethev wrote: post #49
mickeyp wrote: post #42

Earlier quoted context omitted.

"performance optimisation" --- yeees, well, if you don't care about data integrity between your reads and writes. Who knows when those writes you scheduled really get written. And what of rollbacks due to constraint violations? There's we co-locate transactions with code: they are intertwined. But yes, a queue-writer is fine for a wide range of tasks, but not everything. It's that we need to contort our software to m…

This is just FUD. The reason SQLite does locking to begin with is to avoid data corruption. Almost every statement this blog post makes about concurrency in SQLite is wrong, so it's little surprise that their application doesn't do what they expect. >Who knows when those writes you scheduled really get written When a commit completes for a transaction, that transaction has been durably written. No mystery. That's tru…

You are talking about low level stuff like syncing to the filesystem; that data is journalled and ensuring atomicity is maintained and I am in actual fact not.

Dislocating DML from the code that triggers it creates many problems around ensuring proper data integrity and it divorces consistent reads of uncommitted data that you may want to tightly control before committing. By punting it to a dedicated writer you're removing the ability to ensure serialised modification of your data and the ability to cleanly react to integrity errors that may arise. If you don't need that? Go ahead. But it's not fud. We build relational acid compliant databases this way for a reason

Re: SQLite concurrency and why you should care about it

#56

Post by ddtaylor »

I have encountered this problem on Jellyfin before. It works like a dream, but there are some very strange circumstances that can cause the database to become locked and then just not work until I restart the docker container. If I check the logs it just says stuff about the database being locked. It happens quite rarely and seems to be when we fidget in the menus on the smart TV like starting to watch a show to realize it's the wrong episode as you click the button, then spam the back button, etc.

Re: SQLite concurrency and why you should care about it

#57

Post by summarity »

simonw wrote: post #21
summarity wrote: post #8

Earlier quoted context omitted.

I've always tried to avoid situations that could lead to SQLITE_BUSY. SQLITE_BUSY is an architecture smell. For standard SQLite in WAL, I usually structure an app with a read "connection" pool, and a single-entry write connection pool. Making the application aware of who _actually_ holds the write lock gives you the ability to proactively design access patterns, not try to react in the moment, and to get observabilit…

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.

Re: SQLite concurrency and why you should care about it

#58

Post by catlifeonmars »

sethev wrote: post #49
mickeyp wrote: post #42

Earlier quoted context omitted.

"performance optimisation" --- yeees, well, if you don't care about data integrity between your reads and writes. Who knows when those writes you scheduled really get written. And what of rollbacks due to constraint violations? There's we co-locate transactions with code: they are intertwined. But yes, a queue-writer is fine for a wide range of tasks, but not everything. It's that we need to contort our software to m…

This is just FUD. The reason SQLite does locking to begin with is to avoid data corruption. Almost every statement this blog post makes about concurrency in SQLite is wrong, so it's little surprise that their application doesn't do what they expect. >Who knows when those writes you scheduled really get written When a commit completes for a transaction, that transaction has been durably written. No mystery. That's tru…

> When a commit completes for a transaction, that transaction has been durably written. No mystery. That's true whether you decide to restrict writes to a single thread in your application or not.

Usually this is true but there are edge cases for certain journaled file systems. IIRC sqlite.org has a discussion on this.

Re: SQLite concurrency and why you should care about it

#59

Post by throwaway894345 »

thayne wrote: post #53
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.

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 all down to SQLite, but it’s all down to a similar set of assumptions (exactly one application instance interacting with state over a filesystem interface).

Re: SQLite concurrency and why you should care about it

#60

Post by thayne »

There seem to be some misunderstandings in this:

> If your application fully manages this file, the assumption must be made that your application is the sole owner of this file, and nobody else will tinker with it while you are writing data to it.

Kind of, but sqlite does locking for you, so you don't have to do anything to ensure your process is the only one writing to the db file.

> [The WAL] allows multiple parallel writes to take place and get enqueued into the WAL.

The WAL doesn't allow multiple parallel writes. It just allows reads to be concurrent with a single write transaction.

Post reply on HN