On Thu, Oct 14, 2010 at 2:41 PM, Afriza N. Arief <[email protected]>wrote:
> ... sometimes my WinCE application has a problem where the changes made in > one instance a of sqlite3 database not reflected in the other instance b of > the same database file even though they exist in the same process. > > Thread A: > initialize sqlite3 instance a > initialize sqlite3 instance b > Thread B: > modify via instance a > Thread A: > read via instance b > Thread B: > modify via instance a > Thread A: > close sqlite3 instance b > initialize sqlite3 instance b > read via instance b // the changes from a is sometimes not reflected > > I fixed the above problem by using instance a for both Thread A and Thread > B since they are in the same process and hence eliminating instance b. > After exploring more on SQLite documentations, especially http://sqlite.org/lang_transaction.html and http://www.sqlite.org/lockingv3.html#transaction_control , it looks like the problem was caused by pending queries in the database connection. Since there were some pending/unfinished queries, the implicit transaction in one db connections keeps the lock(s) and the transaction becomes long lived. Other connections to the db may not see the changes since they also have pending queries and thus have long-lived transactions as well. Here is roughly how my code was: bool SomeClass::ProductWithTypeExists(const char* type) { sqlite3_reset(); sqlite3_bind(); if (sqlite3_step() == SQLITE_ROW) return true; return false; } and now it becomes bool SomeClass::ProductWithTypeExists(const char* type) { sqlite3_reset(); sqlite3_bind(); bool result = sqlite3_step() == SQLITE_ROW sqlite3_reset(); // always reset() statements after use to mark it as finished and allow implicit transaction to end. return result; } Regards, Afriza _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
