Re: [sqlite] File Locking in WinCE

[Afriza N. Arief]

On Thu, Oct 14, 2010 at 2:41 PM, Afriza N. Arief 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] File Locking in WinCE

[Afriza N. Arief]

Hi,

When I browse SQLite amalgamation file sqlite3.c , I found the following
comment:

/*

** WinCE lacks native support for file locking so we have to fake it

** with some code of our own.

*/

#if SQLITE_OS_WINCE

typedef struct winceLock {

  int nReaders;   /* Number of reader locks obtained */

  BOOL bPending;  /* Indicates a pending lock has been obtained */

  BOOL bReserved; /* Indicates a reserved lock has been obtained */

  BOOL bExclusive;/* Indicates an exclusive lock has been obtained */

} winceLock;

#endif


and I also see some other functions in sqlite3.c that look like replacements
for LockFile(), UnlockFile() and LockFileEx().

Is there any problem with the existing LockFileEx() <
http://msdn.microsoft.com/en-us/library/ee489737.aspx > and UnlockFileEx()<
http://msdn.microsoft.com/en-us/library/ee490757.aspx >?

I am asking this because 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. but now
I plan open the same database from other process so I am a bit worried about
the concurrency in WinCE. I am using WinCE 6.

Thank you,

Afriza
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users