SQLite, keep connection open or close every time?

[Peter Haworth]

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.

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.

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 12:49 AM, Mark Waddingham <mark at livecode.com> wrote:

> On 2015-11-25 22:19, Peter Haworth wrote:
>
>> Maybe I'm missing something but I don't understand why the issue of
>> concurrent users keeps coming up in threads about SQL dbs.  There are
>> perfectly good mechanisms built into every SQL implementation to preserve
>> the integrity of the db, that's ACID is all about, or at least the "I" in
>> it.  They require that correct programming techniques are used of course,
>> but without concurrency control, an SQL db would be pretty much useless.
>>
>
> SQLite is designed as a single-file database to be mutated by a single
> process.
>
> The ability to have multiple processes write to the same database file has
> nothing to do with ACID compliance - in order for that to be possible, the
> operating system would have to provide strong guarantees on file locking
> which none do in all situations (UNIX based systems, for example, tend to
> only have advisory locks, not guaranteed locks meaning that processes have
> to co-operate).
>
> Indeed, an SQLite database is perhaps not really any different from the
> data files you point a MySQL, or PostgreSQL instance at. I'd suspect they
> would get really screwed up if you tried to run two servers pointing at the
> same files and, indeed, that isn't how they are meant to be used as the
> point of using a 'database server' is to allow concurrent multi-user
> access. Of course you could write an database server which uses SQLite as
> the datastore (some have!) - but that is a distinctly different use-case
> for which SQLite is intended for in terms of 'direct access'.
>
> 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
>