SQLite, keep connection open or close every time?

[Peter Haworth]

Thanks Mark, was beginning to doubt myself!

Somewhere on the SQLite site they indicate SQLite works well as the data
repository for websites with less than around 100k hits per day and has
been successfully used on sites with up to 500k hits per day, but they
don't indicate whether that's if it is implemented with some sort of server
mechanism or just php code embedded in the html to access the db directly.
I believe their own web site is based on a SQLite db but, once again, no
indication of the implementation details.  Of course their website is
almost exclusively read-only.

Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>

On Thu, Nov 26, 2015 at 9:57 AM, Mark Waddingham <mark at livecode.com> wrote:

> 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
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>