SQLite and Concurrency: The Lockout Experienced at islistesi.com
A few weeks ago, the SQLite database I use to ensure data consistency in a production ERP system gave me serious trouble with an unexpected lockout (lock) issue. Specifically, I encountered situations where certain operations would get stuck, and the system would become unresponsive, especially during high-volume transactions and concurrent access. Although this situation resembled performance problems I'd previously experienced with a large e-commerce site, the root cause was different: the limitations of SQLite's own concurrency model.
In this post, I will share my personal experience with the SQLite concurrency issue I faced in the islistesi.com project, detailing the lockouts, the debugging process, and the solutions I ultimately found. While I generally prefer more advanced databases like PostgreSQL for my own projects, SQLite's simplicity and lightweight nature can sometimes be appealing in certain scenarios. However, the concurrency management behind this appeal is a topic that requires careful attention.
The Emergence of the Problem: Unexpected Lockouts
It all started with an intense reporting request hitting my system. When users tried to fetch order data for a specific date range, some requests would hang for a long time, or even get completely stuck. Initially, I thought the problem stemmed from heavy queries, but upon examining the journald logs, I realized the situation was more complex.
May 12 03:14:15 servername systemd[1]: islistesi-backend.service: Main process exited, code=killed, status=9/n/a
May 12 03:14:15 servername systemd[1]: islistesi-backend.service: Failed with result 'signal'.
May 12 03:14:16 servername systemd[1]: islistesi-backend.service: Scheduled restart job, restart counter is: 1
May 12 03:14:16 servername systemd[1]: Stopped islistesi backend service.
May 12