[Fwd: Re: SQLite and transactional security]

[Benjamin]

Derek Atkins wrote:

> Does SQLite support multiple applications accessing the same database
> (file) at the same time, or does the application need to provide some
> sort of lock around the database file?  For example, if there were two
> (separate) gnucash processes trying to access the same database, or a
> cron job to pull down price data and stuff it into the database -- do
> we need to assure only one process has the database open(2)'ed?

Sqlite uses operating system file locks to manage concurrency between 
processes and a slightly modified version of the same to handle 
concurrency between threads. Multiple processes and threads can access 
the same sqlite database simultaneously for read access or one process 
or thread can access the database for write access. Locking is done by 
the library its self.

Other than the fact that the lock applies to the whole database the only 
deficiency I see in this area is that sqlite doesn't have any option to 
use blocking operations to obtain the locks. This leads to the 
possibility of an operation that locks the database returning 
SQLITE_BUSY because after a short number of attempts it didn't find the 
database unlocked at any time. In practice this doesn't cause a problem 
unless the database is more often locked than not, for example if you 
hold a transaction open all the time, then close and open it every 
second the readers never get a chance to lock the database themselves. 
Again, in practice this shouldn't cause too man problems. Reading the 
current gnucash pg backend code it's clear that a transaction is only 
opened (and associated table locking performed) when a change is 
committed. If gnucash were to use the same techniques to access an 
sqlite database I suspect that the only difference would be that sqlite 
would be faster to access.

In another interesting twist the last time I mentioned sqlite on this 
mailing list one of the biggest hurdles to acceptance seemed to be the 
lack of time and date functions. Experimental time and date functions 
have now been introduced into the latest sqlite versions.

I have read through the pg backend a few times, now, and I think I have 
the basic philosophies understood. I'm still coming to terms with a 
wider understanding of the qof and higher-level constructs. Perhaps I'll 
have a go at porting the pg backend over to sqlite at some point, unless 
someone has a better idea about a starting point. I am currently on 
holidays so the heat at work is off for the moment, but I'm buying a 
house as well so I don't necessarily have a lot of time :) I will give 
it a go, though. No promises about completion.

Benjamin