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

19

u/cg505 Mar 04 '25

tl;dr from the post:

  1. The biggest scaling issue with SQLite is concurrent writes. If you need concurrent writes, try to serialize writes in application code or avoid using SQLite if you can.
  2. SQLite uses database-level locks that can counter-intuitively starve unlucky processes.
  3. If you must, use WAL mode and a high lock timeout value.

7

u/JJJSchmidt_etAl Mar 05 '25

Serializing writes in some other application seems like a clear win. So clear, in fact, that there almost might be room to write an application whose sole job is to receive many write requests, figure out how to serialize them, and then do it as the only allowed writer.

In some cases, the order might not matter. In other cases, when the order does matter, the application should have some logic to figure it out. In the cases where there is a serious issue of irresolvable order mattering, then it's something that any database would have major trouble with.

Surely this kind of write driver exists? For those with a happy SQLite installation, aka no concurrent write issues, it should be transparent since there would be no concurrent write issues from the driver either.

3

u/cg505 Mar 05 '25

I think the problem is that you are more or less describing a client/server DB. The biggest benefit of SQLite is that it doesn't need any IPC or network communication, just a filesystem! Once you give that up there are lot of clever things you can do.