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
