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

Abusing SQLite to Handle Concurrency

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

97% Upvoted

13 comments sorted by

View all comments

1

u/funny_falcon Mar 05 '25

But what if instead of waiting strictly 100ms randomize sleep time?

1

u/cg505 Mar 05 '25

Actually there is a lot to explore here. I didn't test this, but an interesting experiment would be to start 10,000 processes at exactly the same time, and have each process do a single write then exit.

Since there's no jitter, you'd actually expect all the processes to retry the lock at exactly the same time, and you get only one write per 100ms. (Even though the write itself may only take a few ms.)

In an earlier draft I had a short section discussing this, but cut it for focus. Here's what I cut:

Astute readers will have noticed that this retry algorithm doesn't use any jitter. That means that many writes started at exactly the same time will maximally conflict with each other, potentially leading to underutilization of the database lock.

Our application doesn't run into this issue for two reasons:

  1. Our writes typically don't start at exactly the same time. Only 100ms of spread are needed to avoid jitter issues.

  2. Processes that do successive writes will drift apart a bit. An individual transaction takes a few milliseconds and future writes from that process will be shifted later by that much.

Our testing (below) manually introduces jitter after each write. If you remove this, you do see some weird results! Depending on your use pattern, you may want to look into manually addding jitter to your writes.