SQLite, keep connection open or close every time?

[Mark Waddingham]

On 2015-11-26 18:29, Peter Haworth wrote:
> I sometimes have multiple processes accessing the same SQLite database 
> on
> my computer, typically, the application I'm working on and an SQLite 
> admin
> program.  I've also seen discussions on applications with multiple 
> threads
> accessing the same SQlite database. There don't seem to be any adverse
> effects in either case, presumably because the applications implement
> SQLite transactions using BEGIN/END statements.

No - I was incorrect - whilst file locking is advisory on many platforms 
(i.e. not enforced by the OS and just a 'flag' which co-operating 
processes can use to tell whether someone else has locked a file) the 
point here is that the SQLite library of course co-operates with itself 
:)

So, yes, you can have multiple readers accessing the database and at 
most one writer because the SQLite library ensures it uses the 
implementation of the OS file locks appropriately.

> There's certainly issues trying to directly access a SQLite db over a
> network connection but multiple concurrent access to a local db 
> shouldn't
> cause any issues.

 From the SQLite FAQ, that seems to come down to the problem of using 
file locks over NFS shares rather than anything else.

It also seems that multi-thread access to SQLite is fine as long as the 
library is compiled in the correct way. I suspect it would use 
reader/writer type locking there too - ensuring only one thread is 
writing at any one time. However, I suspect there might be a bit more 
overhead there as it would also have to lock access to any caches it 
might use to speed up accesses - unless it has per-thread ones.

Warmest Regards,

Mark.

-- 
Mark Waddingham ~ mark at livecode.com ~ http://www.livecode.com/
LiveCode: Everyone can create apps