Menu

One writer at a time: SQLite and D1 concurrency, busy locks, and schema changes that ship without downtime
📰

One writer at a time: SQLite and D1 concurrency, busy locks, and schema changes that ship without downtime

DEV Community: database·MapToSite·1 day ago
#NRLfHmDR
#devtodatabase#devto#dev#write#conn
Reading 0:00
15s threshold

At 12:41, in the middle of the lunch orders, a deploy ran a single line: CREATE INDEX idx_orders_shop_created ON orders (shop_id, created_at) . The table had 380,000 rows. The statement took nine seconds. For those nine seconds every write to that database waited, every request that waited long enough gave up, and the mobile clients retried — which added 2,000 more requests to a queue that was already behind. The index was correct. The deploy was not.

Nothing in that story is exotic, and the fix was not a faster index. The fix was admitting a property of the database that the code had been written as if it did not exist: SQLite — and D1, which is SQLite behind a Worker API — accepts exactly one writer at a time. Not one per table, not one per connection, not one per tenant: one, per database file. Every design decision below follows from that sentence.

This is not an article about how to make SQLite fast.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.