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

21–30 of 189 posts

Re: SQLite concurrency and why you should care about it

#21

Post by simonw »

summarity wrote: post #8
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…

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.

Re: SQLite concurrency and why you should care about it

#22

Post by hruk »

mickeyp wrote: post #15
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…

Edit: disregard. I read it as he'd done it and had contention problems. You can't. You have a single writer - it's one of the many reasons sqlite is terrible for serious work. You'll need a multiprocessing Queue and a writer that picks off sentences one by one and commits it.

This is just untrue - the naive implementation (make the API call, write a single row to the db) will work fine, as transactions are quite fast on modern hardware.

What do you consider "serious" work? We've served a SaaS product from SQLite (roughly 300-500 queries per second at peak) for several years without much pain. Plus, it's not like PG and MySQL are pain-free, either - they all have their quirks.

Re: SQLite concurrency and why you should care about it

#23

Post by adzm »

mickeyp wrote: post #17
tejinderss wrote: post #13

Earlier quoted context omitted.

Do you know any good default PRAGMAs that one should enable?

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…

Really, no mmap?

Re: SQLite concurrency and why you should care about it

#24

Post by justin66 »

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

It has been forked at least once:

https://docs.turso.tech/libsql

Re: SQLite concurrency and why you should care about it

#25

Post by ignoramous » 

  So, I decided on three locking strategies:

  No-Lock
  Optimistic locking
  Pessimistic locking

  As a default, the no-lock behavior does exactly what the name implies. Nothing. This is the default because my research shows that for 99% all of this is not an issue and every interaction at this level will slow down the whole application.
Aren't the mutexes in the more modern implementations (like Cosmo [0]) & runtimes (like Go [1]) already optimized so applications can use mutexes fearlessly?

[0] https://justine.lol/mutex/

[1] https://victoriametrics.com/blog/go-sync-mutex/

Re: SQLite concurrency and why you should care about it

#26

Post by metrix »

adzm wrote: post #23
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…

Really, no mmap?

I'm curious what your suggest mmap pragma would be.

Re: SQLite concurrency and why you should care about it

#27

Post by zie »

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…

Technically SQLite can only have 1 writer at any given moment, but it can appear like it works across multiple writers and let it serialize the calls for you.

By default SQLite will not do what you want out of the box. You have to turn on some feature flags(PRAGMA) to get it to behave for you. You need WAL mode, etc read:

* https://kerkour.com/sqlite-for-servers * https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-lo...

My larger question is why multiprocessing? this looks like an IO heavy workload, not CPU bound, so python asyncio or python threads would probably do you better.

multiprocessing is when your resource hog is CPU(probably 1 python process per CPU), not IO bound.

Re: SQLite concurrency and why you should care about it

#28

Post by mangecoeur »

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.

Re: SQLite concurrency and why you should care about it

#30

Post by kbolino »

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

SQLite is fairly fork-resistant due to much of its test suite being proprietary: https://www.sqlite.org/testing.html
Post reply on HN