On 9/13/2016 4:00 PM, Alex Ward wrote:
We think we are seeing the case where a read on one WAL mode Sqlite database
connection using the C API is reading stale data after a transaction has
committed data changes on another connection.
For instance, a deleted row on one connection is still found by a select on the
other. The BEGIN/DELETE/COMMIT then SELECT (prepare/step/finalize for each) is
happening in the same thread. We expected the commit of the write to be the
point in time after which any read would read that committed data. This does
not seem to be the case here.
Somehow or other, you have an open read transaction on the connection
doing the SELECT, dating from before the write was committed. Often
caused by forgetting to reset or finalize a statement.
Would other threads doing reads in parallel on the same connection affect when
the end mark is moved?
Which connection is "the same" one? The reader or the writer? If you
have other reads in progress on the reader connection, then they likely
keep a transaction open, and your SELECT is done within that
transaction, which goes back in time farther than you thought. If you
have other reads in progress on the writer connection, then COMMIT
likely fails.
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?
Realize that a transaction is a property of a connection, not a thread
or a query. Suppose thread A starts a read at time T and ends it at time
T+20; and thread B starts a read on the same connection at time T+10 and
ends it at T+30. Both reads are part of the same unbroken transaction
lasting (at least) from T to T+30. If there's an update committed at T+5
on a different connection, neither read would see it, even though thread
B started after it.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
