4 ms·

In my experience SQLite is fine for single process concurrency (just push everything onto a worker thread). However multiprocess concurrency support is very pri

by jeeeb 5y ago

In my experience SQLite is fine for single process concurrency (just push everything onto a worker thread). However multiprocess concurrency support is very primitive even with WAL support enabled. Internally SQLite relies on file locks to mediate access and this seems to necessitate very coarse locking (lots of table locks). Comparatively the client server model of other databases like PostgreSQL seems to allow better coordination between writes and much finer grained (row level) locking.

akvadrako 5y ago
In both the worker-thread and table-locking models writing is essentially single-threaded. What matters is the performance. Have you seen a difference between these two approaches?
- remram 5y ago
  You can run into problems if you have two threads handling two requests. Each of them might have a shared read-lock as they are getting data, and when they both try to update to a write-lock to do some update, one of them will be canceled (throw an exception: SQLITE_BUSY). This seems to me like a Rails bug, it should be serializing transactions when running against SQLite.
- jeeeb 5y ago
  My experience is that this doesn’t necessarily hold true in practice. The concurrency primitives being used are different. I’ve put a war story below :) but it’s kind of beside the point. My main point was that other DBs like PostgreSQL implement row level locking so can handle concurrent writes much better than SQLite. As for my war story I hit on false deadlock detection problems writing to two separate tables from two processes with an fkey between them. Fun fact if it thinks there’s a deadlock SQLite will not call your busy callback. It will just immediately fail. I know the deadlocks were false positives because my solution was to move my backoff logic out of my busy handler and instead catch SQLITE_BUSY and call sleep myself. Identical backoff values but the “problem” disappeared.

by jeeeb 5y ago

akvadrako 5y ago

In both the worker-thread and table-locking models writing is essentially single-threaded. What matters is the performance. Have you seen a difference between these two approaches?

remram 5y ago
You can run into problems if you have two threads handling two requests. Each of them might have a shared read-lock as they are getting data, and when they both try to update to a write-lock to do some update, one of them will be canceled (throw an exception: SQLITE_BUSY). This seems to me like a Rails bug, it should be serializing transactions when running against SQLite.
jeeeb 5y ago
My experience is that this doesn’t necessarily hold true in practice. The concurrency primitives being used are different. I’ve put a war story below :) but it’s kind of beside the point. My main point was that other DBs like PostgreSQL implement row level locking so can handle concurrent writes much better than SQLite. As for my war story I hit on false deadlock detection problems writing to two separate tables from two processes with an fkey between them. Fun fact if it thinks there’s a deadlock SQLite will not call your busy callback. It will just immediately fail. I know the deadlocks were false positives because my solution was to move my backoff logic out of my busy handler and instead catch SQLITE_BUSY and call sleep myself. Identical backoff values but the “problem” disappeared.