On Fri, Aug 18, 2023 at 9:21 PM Grant Taylor via cctalk <cctalk@classiccmp.org> wrote: > > On 8/18/23 9:06 PM, Adam Thornton wrote: > > So it doesn't support concurrent access by multiple users, > > I used to think the same thing. > > But, I seem to recall reading an authoritative article, likely on > SQLite's site, talking about how it is possible to have multiple > processes but the same and / or different users access the same SQLite > database file (almost) concurrently.
My understanding is: Concurrent access is fine, except that as soon as one writer begins updating things SQLite will take a lock on the whole database so another writer is going to wait until the first transaction is committed or rolled back before its own transaction can proceed. This also means you can't get a locking issue that could roll back your transaction unexpectedly due to a conflict. And there is now the BEGIN CONCURRENT option which defers locking until COMMIT time, so multiple concurrent writers can just go at it, but it only works well if each writer is changing different data (records that don't share index pages etc.). At COMMIT time, it checks to see if any of the database pages that the transaction was based on (read or written) have changed since the start of the transaction and if so, the COMMIT fails and you have to roll back and try again which of course makes the application code more complex. https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md Overall, I think SQLite is one of the best things ever. Not even Open Source licensed, it's explicitly in the Public Domain. It now implements the vast majority of the SQL standards and you're unlikely to find something you need that it can't do. If you don't need client-server access and don't need a zillion concurrent users, then it's often the best choice. Just the lack of any installation, configuration, startup/shutdown or other management requirements makes it pretty much "implement and forget". It has billions of installations and on the order of a trillion databases in use. The O'Reilly SQL Pocket Guide (4th Ed.) covers SQLite, SQL Server, Oracle, and PostgreSQL making it a handy resource for comparing those systems.
