4 ms·
What I've never got to grips with is the "use a single writer with SQLite" advice. That seems doable with a long-running application server, but if your applica
by pbowyer 6y ago
What I've never got to grips with is the "use a single writer with SQLite" advice. That seems doable with a long-running application server, but if your application boots up on every request (like PHP, Python & Ruby applications generally do) how do you do this, when you have multiple simultaneous users?
It feels like SQLite is missing a separate gatekeeper-binary to act as the single writer: the database server as it were.
- liuliu 6y agoSQLite uses file lock to coordinate with many processes. Your performance will be similar to use single writer if we ignore the repeated DB open / close due to the app boot on every request. I am not sure about real-world DB open / close cost. Because it is considered good practice to always use some kinds of SQLite connection pool if you need many readers / writers. Didn't get a chance to try.
- jononor 6y agoWith Python at least an application server with long-running processes is the most common way to deploy, for example with gunicorn. Though typical config will have green threads and/or multiple worker processes, so I am not quite sure how one the single-writer is enforced.
- nine_k 6y agoFile locking and waiting on the lock? Assuming that open + update + close is very fast and not very frequent, the wait time will be near zero, can be done synchronously pretty well.
- coliveira 6y agoA solution is to append requests as files to a directory, and have a single process that reads the files and performs the write on SQLite. This way you can avoid locks and use the OS to manage the coordination.