SQLite, keep connection open or close every time?

[Mark Waddingham]

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