Hi-
We have a scenario where we have two different database connections to the
same database. Each database connection is running on a separate thread,
but in this situation, we are only using one connection at a time. We are
finding that sometimes one database connection will do a "select" on the
table and not get the correct value that the other database connection
recently updated. The connections are obtained at the beginning of the
program and not closed until the very end.
Essentially, we run a series of tests that are successful, then we run the
same tests again, this time failing.
DBC1 - begin transaction
DBC1 - sets value to 333000
DBC1 - commit transaction
DBC1 - gets value out, is 333000
DBC2 - gets value out, is 333000
DBC2 - begin transaction
DBC2 - sets value to 350000
DBC2 - commit transaction
DBC2 - gets value out, is 350000
DBC2 - sets value to 350100
DBC2 - gets value out, is 350100
*Repeat test*
DBC1 - begin transaction
DBC1 - sets value to 333000
DBC1 - commit transaction
DBC1 - gets value out, is 333000
DBC2 - gets value out, is 350100
I have noticed that if we make a call to sqlite3_enable_shared_cache just
prior to each database connection being opened, the end result is that DBC2
will get the correct value at the beginning of the second test.
Not really knowing the underlying details, it almosts looks like DBC2
doesn't realize the db was changed and uses what it last thought the db held
for that value.
Any thoughts on why we are seeing what we see? I could add the call to
enable the shared cache, but I really want to understand why it doesn't work
without it.
Thanks,
Mark
