On 6/23/2014 5:43 AM, João Ramos wrote:
If multiple connections in shared cache mode are treated as a single "real"
connection, should I change the thread mode to Serialized?
What mode do you use now?
In any case, I doubt the thread mode has any bearing on the problem. The
scenario may occur even if no two threads ever run in parallel. It's
only necessary that SELECTs on different connections to shared cache
interleave: you call sqlite3_step on connection A, then later call
sqlite3_step on connection B (while the statement in A has not been
reset) - that second traversal would see the data as of the start of the
first traversal.
I tough this
"single" connection was in terms of page cache
Well, yes. So you have a version of a page in this cache - that version
must be as of the time of the oldest read transaction still outstanding.
So all connections to that shared cache see the same version of that
page, and cannot observe any changes made to it later.
and would not have any
implications on how the connections are accessed (or not) concurrently,
especially transaction control.
Concurrent access and transaction control are unrelated concepts. I'm
not sure why you are bringing them up together. You can observe
transaction isolation effects with a single thread interleaving access
to two database connections. Start traversing a SELECT statement on one
connection; between two calls to sqlite3_step, modify the data on the
other (WAL journal mode would let you); continue traversal of the
statement - it would not observe the changes the same thread has just made.
Either way, the example you gave does not apply. What was happening with my
code was that a transaction A started at T1, inserted a row and was
committed successfully at T2. Another transaction B started at T3 and
didn't see the new row.
Was there, by any chance, a seemingly unrelated transaction C using the
same shared cache that started reading at T0, and has not completed by T3?
I can't see how the fact that transaction A was in
shared cache mode and B in private cache mode
Shouldn't it be the other way round? I thought you said all readers (B
among them) use shared cache, while a writer (like A) uses a private
connection.
could influence the new row
not being seen in transaction B, when its transaction started after
transaction A commit. Doesn't this break ACID?
ACID provides guarantees on when changes made in one transaction will
*not* be seen by another. It says nothing about when changes in one
transaction *will* be seen by another.
Yes, connections sharing a cache are not quite as isolated from each
other as independent private connections (it's even possible to enable
read-uncommitted mode, whereby one such connection can see
not-yet-committed changes made by another). As any other option, shared
cache brings some benefits and some limitations (if it were all
benefits, it wouldn't be an option).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
