On 5 Oct 2016, at 4:30pm, Jens Alfke <j...@mooseyard.com> wrote: > I did find that SQLite’s own locking was less effective/fair than using a > mutex, and in situations with a lot of write contention could lead to > starvation and timeouts. It’s possible I was doing things wrong, as the > wrapper library I started with included a SQLite busy-handler that would > wait-and-retry. (But IIRC, if I removed the busy handler altogether, SQLite > would just immediately return a db-busy error if another thread had a lock.)
SQLite has its own built-in busy-handler which is written to work extremely efficiently with the things SQLite usually needs to do (though not necessarily with the way you're using SQLite). It's not obvious whether the wrapper library you're using simply calls this one or implements its own, possibly less-efficient, one. > There were situations where one thread was rapidly writing to the database, > in multiple transactions but with only brief gaps between them, and other > connections on other threads that wanted to make small writes were starved. > > Apparently the waiting connections were trying to spin-lock, i.e. attempting > to get the write lock, and on failure sleeping a little while and retrying. > But if the first thread has the write lock 99% of the time, it can take quite > a while for another thread to get lucky and snatch the lock. If there are > multiple other threads wanting the lock, it takes multiple times as long. The backoff algorithm implemented in SQLite is very likely to give each connection its fair turn. At least it did so in my tests of greedy vs. non-greedy three- and seven-process situations. But it does still fail to do this correctly sometimes depending on the timing of the requests. > To fix this I added my own (pthread) mutex and used it as a bottleneck > between my code and SQLite, so before trying any writes a thread would wait > to lock the mutex. The advantage is that the mutex keeps a queue of waiting > threads, so it grants access fairly, to the thread that’s been waiting the > longest. But this gets harder to implement, of course, if the database is > being used in multiple *processes*, not just threads in a single process. Indeed. Yet another problem that nobody has completely solved yet. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
