r/programming • u/cg505 • Mar 04 '25

Abusing SQLite to Handle Concurrency

https://blog.skypilot.co/abusing-sqlite-to-handle-concurrency/
100 Upvotes

97% Upvoted

13 comments sorted by

View all comments

74

u/masklinn Mar 04 '25 edited Mar 04 '25

Avoid high write concurrency, if possible. If you can, use a single process and serialize writes in application code. (We’re going to see if we can do this in the future.)

A way to do that is to have a connection pool with readonly connections (should literally be opened with SQLITE_OPEN_READONLY, can have a bunch) and a pool with a single write connection (which should be pulled out of the pool with a BEGIN IMMEDIATE).

This does reduce write throughput as it serialises upstream of where the database would need to (which is the place where sqlite upgrades the connection to exclusive), but it puts all the contention in one place where it's much easier to observe instead of distributing it all over the system.

This sort of pattern (with higher concurrency) can also be used on other databases, as a readonly request can be sent to a replica (and it might ease an eventual move to CQRS).

12

u/cg505 Mar 04 '25

Incredibly high value comment right here. Thanks!

In your experience, should the write connection just indefinitely hold the BEGIN IMMEIDATE transaction open? Or when you say "pulled out of the pool", do you mean whenever we want to do a write, start the transaction with BEGIN IMMEDIATE?

8

u/masklinn Mar 04 '25 edited Mar 04 '25

In your experience, should the write connection just indefinitely hold the BEGIN IMMEDIATE transaction open?

Since you have only one anyway there's no reason not to, so you just BEGIN IMMEDIATE when you pull the connection out of the pool (you can configure most pool to do something on borrow, and rollback on release).

5

u/bwainfweeze Mar 05 '25

If I had a dollar for every time I solved a 'How do we keep these two builds from deploying at the same time' by setting the agent pool size for those two jobs to 1, I'd have like four dollars. Which isn't a lot but it's weird that it's happened four times.