The last transaction should always be the final one. In a a multiprocess/threaded application how can one make assumptions on the order of updates?
Sreekumar On Fri, Feb 10, 2012 at 8:16 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Sreekumar TP <sreekumar...@gmail.com> wrote: > > How is this different from two threads each with a db connection in a > > single process? > > If each thread uses its own separate connection, it should be no different > - you would observe the same issue. > > > Moreover the journal mode is WAL. Hence the writer should be able to > append > > changes to the WAL file as there are no other write transaction. > > Your problem is with a transaction stat starts as a reader, and later > tries to become a writer. This is only possible if the reader is observing > the most recent state of the database, that is, if there were no writes > since it started. > > Consider: > > // initial setup > create table t(count integer); > insert into t values (0); > > /* 1 */ select count from t; > /* 2 */ update t set count = count + 10; > /* 1 */ update t set count = count + 1; // (!) > /* 1 */ select count from t; // (!!) > > /* 1 */ and /* 2 */ mark operations performed by two separate > transactions. Imagine that such a sequence were possible, and the update at > (!) succeeded. What value should count have after this update? If it's 11, > then a select at (!!) would effectively observe a change written by a > different transaction, violating transaction isolation. If it's 1, then an > observer in yet third connection could see the count go up, then down - > which is surprising as the update statements only ever increment it. > > Neither outcome is particularly appealing, so the sequence is prohibited > altogether. > > There are several ways in which transactions that start as readers and > later promote themselves to writers may cause problems. It's best to avoid > such situations: if you know that you may need to write eventually, start > your transaction with BEGIN IMMEDIATE, then it would be marked as a writer > from the outset. > -- > 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
