On Mon, 21 Jan 2019 at 15:46, <andrew.g...@l3t.com> wrote: > For the moment, the solution that is working for me is to disable syncing > with PRAGMA synchronous = OFF. This is acceptable in this particular > application because a power failure or OS crash will necessitate restarting > the data gathering process anyway. >
If the data gathering process is creating a database for later consumption by other applications, and you potentially want to use this on a network file system, note that you want to turn synchronous back ON and run one final transaction to update the database on each connection you've opened. This is because sqlite ignores errors from close() - which doesn't matter in normal circumstances because any i/o errors should have been collected by fdatasync(). But when you're not syncing it opens the possibility of a silently corrupt data set, because even though the file server reported the error sqlite suppressed it. > In looking at the logs, I'm seeing several unlucky locking patterns. > Let's just pick one. Process A gets starved by process B since B does many > write transactions in a row, each time successfully getting an EXCLUSIVE > lock. When B calls fdatasync() (actually fsync()), it blocks while holding > EXCLUSIVE. A wakes up, but A can't immediately get a SHARED lock because B > holds EXCLUSIVE. A goes to sleep while holding no locks, then B wakes up > when the fsync() completes. B then releases locks but grabs EXCLUSIVE > again to complete its next transaction, and the cycle repeats. A still > can't get its SHARED lock, so it goes to sleep again, and then B continues > to monopolize the lock. This goes on long enough to exhaust A's patience, > at which point SQLITE_BUSY propagates to the application. > Yeah, I've seen a tight loop of write transactions starve readers before - since they've most likely backed off to sleep for 100ms at a time they have to get really lucky to wake up while the writer is idle. It doesn't strike me as a common workload though? Like if you need that kind of constant throughput without disrupting readers it may be time to consider an RDMS. > I saw another pattern that I'm having a harder time explaining. It looked > almost like the process holding EXCLUSIVE got stuck waiting for the process > trying to get EXCLUSIVE. The latter would eventually timeout, at which > point the former would proceed. Shrug! I should investigate this further, > but look at the time. > I don't understand this one, and it doesn't really add up because: 1. A process holding EXCLUSIVE has the highest lock possible; it's not waiting for anyone 2. Why is any other process blocked trying to acquire EXCLUSIVE at this point? It shouldn't even be able to get a SHARED/RESERVED lock. -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
