Most active commenters
  • mickeyp(3)
  • sethev(3)

←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
353 points HunOL | 11 comments | 01 Nov 25 12:59 UTC | HN request time: 0s | source | bottom
Show context
asa400 ◴[01 Nov 25 13:32 UTC] No.45781519[source]▶
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 that does a write (any write, even if it is not the first operation in the transaction) in “immediate” mode rather than “deferred” mode.

https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-lo...

replies(9): >>45781577 #>>45781605 #>>45781634 #>>45781639 #>>45782026 #>>45782543 #>>45783088 #>>45787431 #>>45789444 #
summarity ◴[01 Nov 25 13:50 UTC] No.45781639[source]▶
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 observability into lock contention, etc.
replies(3): >>45781655 #>>45782042 #>>45788855 #
1. mickeyp ◴[01 Nov 25 13:51 UTC] No.45781655[source]▶
I mean, you're not wrong, and that is one way to solve it, but the whole point of a sensibly-designed WAL -- never mind database engine -- is that you do not need to commit to some sort of actor model to get your db to serialise writes.
replies(1): >>45782616 #
2. sethev ◴[01 Nov 25 15:49 UTC] No.45782616[source]▶
These are performance optimizations. SQLite does serialize writes. Avoiding concurrent writes to begin with just avoids some overhead on locking.
replies(2): >>45782671 #>>45782743 #
3. mickeyp ◴[01 Nov 25 15:57 UTC] No.45782671[source]▶
"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 make sqlite not suck at writes that is the problem.

replies(2): >>45782838 #>>45785847 #
4. ncruces ◴[01 Nov 25 16:04 UTC] No.45782743[source]▶
SQLite, for the most part, uses polling locks. That means it checks if a lock is available to be taken, and if it's not, it sleeps for a bit, then checks again, until this times out.

This becomes increasingly inefficient as contention increases, as you can easily get into a situation where everyone is sleeping, waiting for others, for a few milliseconds.

Ensuring all, or most, writes are serialized, improves this.

5. sethev ◴[01 Nov 25 16:14 UTC] No.45782838{3}[source]▶
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 true whether you decide to restrict writes to a single thread in your application or not.

replies(2): >>45782989 #>>45783020 #
6. mickeyp ◴[01 Nov 25 16:31 UTC] No.45782989{4}[source]▶
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

replies(1): >>45783202 #
7. catlifeonmars ◴[01 Nov 25 16:34 UTC] No.45783020{4}[source]▶
> 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.

replies(1): >>45783274 #
8. sethev ◴[01 Nov 25 16:53 UTC] No.45783202{5}[source]▶
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.

9. zimpenfish ◴[01 Nov 25 17:01 UTC] No.45783274{5}[source]▶
> 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?

replies(1): >>45783539 #
10. catlifeonmars ◴[01 Nov 25 17:33 UTC] No.45783539{6}[source]▶
That might have been it. Overall the whole “How to corrupt your database article” was quite a good read:

https://sqlite.org/howtocorrupt.html

11. jitl ◴[01 Nov 25 22:05 UTC] No.45785847{3}[source]▶
> Who knows when those writes you scheduled really get written

I await the write to complete before my next read in my application logic, same as any other bit of code that interacts with a database or does other IO. Just because another thread handles interacting with the writer connection, doesn't mean my logic thread just walks away pretending the write finished successfully in 0ms.