A couple more questions: 1) Does "without encryption" mean still using System.Data.SQLite, just without using a key? 2) Are you using WAL? 3) Do you also see the problem with a single process with two connections? 4) Any chance you could provide a small demo project that demonstrates the problem? 5) Are you using standard System.Data.SQLite, or the SqlCipher version? (http://sqlcipher.net)
I'm pretty fuzzy on this, but here are some (possibly way off base) thoughts... The way I understand things, when you do an update inside of a transaction, the new data is written to the log until it is committed, and then a rollback involves discarding the log page with the changes. This means you expect to see the new data in your "session" but no one else should see the new data until you commit. The question of which version of the data a session sees is, I think, tied to some global counter-type metadata that tracks changes. In Oracle, this would be perhaps a system change number that would determine which undo blocks your session would see versus other sessions. I think SQLite has something similar, and I think this is stored in the database header on the first database page. I also recall that there are a few places in SQLite where it reads data for page 1 using system-level I/O rather than the pager (e.g., direct reads on the database file), and this causes issues for page-level encryptors because they don't get an opportunity to decrypt the encrypted page before SQLite reads that piece of data, so they typically have to hard-code some value at that data location or hope that SQLite ignores what would be an obviously invalid value. I think the change counter is one of these items. Maybe folks with much more understanding could make sense of this, but my thought is that the equivalent of the "system change number" is being mucked up by the page-level encryptor, causing SQLite to get the old data because as far as it knows that's the data at the given (invalid) change count. Well, something like that. Again, probably way off base, but I had a couple of minutes and thought I'd throw it out there... ~Mike -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabrizio Steiner Sent: Thursday, December 15, 2011 9:01 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite reading old data I think the following observation, which I made in the meantime, may be very interesting. Which also gives a hint to a problem with SQLite in combination with the encryption of System.Data.SQLite. I tried the same test with an unencrypted database, and surprisingly the 3 threads do their work without any exception. I executed several test runs with the 3 threads and it wasn't reproducable anymore. Activating the encryption again it's reproducable and exceptions occur due to an old stamp that is being retrieved. Answers to your questions: - No connections are shared at all between threads. So every thread creates 2 private connections. - Updating always occurs in the second connection. - The after-update checks will be perfomed in the same transaction as the update itself. - No thread ever deletes a row. Regards Fabrizio > -----Ursprüngliche Nachricht----- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von Igor Tandetnik > Gesendet: Donnerstag, 15. Dezember 2011 14:26 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] SQLite reading old data > > Fabrizio Steiner <f...@bbtsoftware.ch> wrote: > > I'm currently facing a problem with the System.Data.SQLite ADO.NET > > Provider. I'm using stamps (simple integer column) on the rows in > > order to detect if another user has altered the same datarow during > > the > time the first user started the edit and when he invoked the save. > Every update increases this stamp and the update statement is > constrained with the old stamp. If the update has > > no affected rows, there are different cases which are checked: > > - If the datarow has been deleted, the user is informed about this. > > - If the datarow still exists the current stamp in the database is retrieved. > > - If the db stamp is greater than the reference stamp, the user > > will be informed that another user changed the data in the meantime. > > - If the db stamp is smaller or equal to the reference stamp, an > > exception > is thrown because the stamp can never get smaller. > > > > I tried to simulate 3 users which are updating a datarow in the same > > table, every user changes it's own row. Basically I had 3 threads > > each of one first retrieving a row in one connection. Updating the > > data and > storing it back into the table with another connection. Every thread > made a loop with several edits and a sleep in between. > > Do you have two connections, each shared by three threads; or six > separate connections? Do you ever perform any writing on the > connection that reads the original version of the row? Do you perform > after-update checks on the same connection as the update itself? Do > you wrap the update and the checks together in a transaction? > > How is a particular "datarow" identified? Is it possible for one > thread to delete a row, and then for another to insert it back later > as new (presumably with the version stamp starting back from zero)? > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
