SQLite concurrency and why you should care about it • Hacker News Board

Re: SQLite concurrency and why you should care about it

#61

Post by chasil » Sat, Nov 01, 2025, 4:40 PM UTC

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…

In an Oracle database, there is only one process that is allowed to write to tablespace datafiles, the DBWR (or its slaves). Running transactions can write to ram buffers and the redo logs only.

A similar design for SQLite would design for only one writer, with all other processes passing their SQL to it.

Re: SQLite concurrency and why you should care about it

#62

Post by sethev » Sat, Nov 01, 2025, 4:53 PM UTC

mickeyp wrote: post #55

sethev wrote: post #49

Earlier quoted context omitted.

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

Oh, I think you're picturing executing your transaction logic and then sending writes off to a background queue. I agree, that's not a general strategy - it only works for certain cases.

I just meant that if you can structure your application to run write transactions in a single thread (the whole transaction and it's associated logic, not just deferring writing the end result to a separate thread) then you minimize contention at the SQLite level.

Re: SQLite concurrency and why you should care about it

#63

Post by zimpenfish » Sat, Nov 01, 2025, 5:01 PM UTC

catlifeonmars wrote: post #58

sethev wrote: post #49

Earlier quoted context omitted.

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.

> there are edge cases for certain journaled file systems. IIRC sqlite.org has a discussion on this.

Can't currently find it but I guess it comes under the "if the OS or hardware lies to SQLite, what can it do?" banner?

Re: SQLite concurrency and why you should care about it

#64

Post by morshu9001 » Sat, Nov 01, 2025, 5:06 PM UTC

abound wrote: post #48

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

Re: SQLite concurrency and why you should care about it

#65

Post by porridgeraisin » Sat, Nov 01, 2025, 5:14 PM UTC

masklinn wrote: post #54

porridgeraisin wrote: post #38

Earlier quoted context omitted.

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

Yes, if journal_mode was not sticky, a new process opening the db would not know to look for the wal and shm files and read the unflushed latest data from there. On the other hand, foreign key enforcement has nothing to do with the file itself, it's a transaction level thing.

In any case, there is no harm in setting sticky pragmas every connection.

Re: SQLite concurrency and why you should care about it

#66

Post by pstuart » Sat, Nov 01, 2025, 5:20 PM UTC

mkoubaa wrote: post #16

mickeyp wrote: post #5

SQLite 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

The real fork is DuckDB in a way, it has SQLite compatibility and so much more.

The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster.

https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html

As to the problem that prompted the article, there's another way of addressing the problem that is kind of a kludge but is guaranteed to work in scenarios like theirs: Have each thread in the parallel scan write to it's own temporary database and then bulk import them once the scan is done.

It's easy to get hung up on having "a database" but sharding to different files by use is trivial to do.

Another thing to bear in mind with a lot of SQLite use cases is that the data is effectively read only save for occasional updates. Read only databases are a lot easier to deal with regarding locking.

Re: SQLite concurrency and why you should care about it

#67

Post by pstuart » Sat, Nov 01, 2025, 5:22 PM UTC

Leherenn wrote: post #33

A bit off topic, but there seems to be quite a few SQLite experts here. We're having troubles with memory usage when using SQLite in-memory DBs with "a lot" of inserts and deletes. Like maybe inserting up to a 100k rows in 5 minutes, deleting them all after 5 minutes, and doing this for days on end. We see memory usage slowly creeping up over hours/days when doing that. Any settings that would help with that? It's pa…

If you're deleting all rows you can also just drop the table and recreate it.

Re: SQLite concurrency and why you should care about it

#68

Post by porridgeraisin » Sat, Nov 01, 2025, 5:23 PM UTC

yellow_lead wrote: post #50

porridgeraisin wrote: post #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 conta…

C# devs*

Didn't mean to belittle any 'X' developer. By "what web devs expect", I meant the settings that are usually used for databases in web apps.

Re: SQLite concurrency and why you should care about it

#69

Post by [deleted] » Sat, Nov 01, 2025, 5:24 PM UTC

Leherenn wrote: post #33

A bit off topic, but there seems to be quite a few SQLite experts here. We're having troubles with memory usage when using SQLite in-memory DBs with "a lot" of inserts and deletes. Like maybe inserting up to a 100k rows in 5 minutes, deleting them all after 5 minutes, and doing this for days on end. We see memory usage slowly creeping up over hours/days when doing that. Any settings that would help with that? It's pa…

[deleted]

Re: SQLite concurrency and why you should care about it

#70

Post by amaccuish » Sat, Nov 01, 2025, 5:32 PM UTC

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.

Their whole recent rewrite of the DB code (to Entity Framework) is to allow the user choice of DB in future.