FastAPI SQLite BEGIN IMMEDIATE intermittent 500 errors under concurrent load
A FastAPI application using SQLite with explicit BEGIN IMMEDIATE transactions hit intermittent 500 errors under concurrent load. The Review endpoint performed thousands of N+1 queries to fetch items with ancestor paths, taking approximately 6 seconds. During this time, each query held SQLite's write lock because of BEGIN IMMEDIATE. Concurrent requests from other users waiting on busy_timeout (5 seconds configured) would timeout with "database is locked" and return HTTP 500.
SQLite's BEGIN IMMEDIATE acquires a write lock immediately, not a read lock, blocking other transactions. Reduce lock contention by decreasing transaction duration. In this case, replacing 9,194 separate queries with a single recursive CTE preloaded all required ancestor data at once, cutting transaction duration to milliseconds and eliminating concurrent timeout failures.