On 13 Sep 2016, at 9:00pm, Alex Ward <cov...@yahoo.com> wrote: > What is the standard idiom to avoid stale data while still allowing all but a > writing thread not to see uncommitted data?
You should not need to do anything special to arrange this. > Is there a window of time between a commit on one connection and the data > being available on another connection? Is that deterministic? When is the > WAL mode "end mark" moved on a connection past other changes? Is it possible > that we have an end mark on the read connection that is still behind the > write on the other connection? What would trigger it to move? It doesn't work quite the way you describe but again you should not need to do anything special to make it all work properly. > Would other threads doing reads in parallel on the same connection affect > when the end mark is moved? In our test we serialized reads so there could > only be one happening at a time in an attempt to remove any chance of this. > But we still saw stale data. There's no 'end mark' as such. A read should, at absolute worst, delay changes making it to the database until the read is finalized. A read done using sqlite3_exec() finalizes itself. A read done using _prepare() and _step() must be manually finalized using either _finalize() or _reset(). Your description of your fault suggests that at least two of your threads/processes are trying to use the same connection to the database at the same time. (It doesn't really matter whether these threads are reading or writing, you can get confusion on the connection either way.) Another possibility is that you're using a PRAGMA which looks like it should speed things up but it does so by telling SQLite not to do necessary multi-thread/multi-processing checks. > We are trying to implement a system where all writes occur on one connection > (in a SQL transaction where a writing thread would see the uncommitted data) > and all other reads on a second connection. Does it sound like we are doing > something fundamentally wrong by trying to share connections across threads > in this way? If you don't have your own mutex locks, then yes. Don't try to set up a situation where two transactions are happening at the same time with the same connection. The standard way to do that correctly is to give each thread its own connection to the database and let SQLite do all the locking necessary. However it's not difficult to make your own mutex system work, it just seems like your own one isn't working properly. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
