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
