On Sat, 26 Oct 2019 at 00:07, Brannon King <countpri...@gmail.com> wrote:
> This is a request for a small change to the handling of multiple > connections. I think it would significantly enhance the usefulness there > via allowing multiple "views" of the data. > > Consider that I have two simultaneous connections to one file, named Con1 > and Con2. They could be in one process or one thread -- that's irrelevant. > Either one may write to the DB; we don't know yet. For starters, assume > that their journal mode is MEMORY. > > Both connections begin with "begin transaction". Already I'm dead in the > water; one of those will fail presently with "database is locked". This is not true, unless you're using BEGIN IMMEDIATE or BEGIN EXCLUSIVE whose express purpose is to obtain a lock. BEGIN TRANSACTION's default mode is DEFERRED, which does not obtain any locks until the DB is actually queried (causing it to obtain a read-lock) or modified (causing it to obtain a write-lock). Read-locks and write-locks can coexist, except during the window when the DB file is actually being modified. This period is protected by an exclusive lock, and is generally brief unless you have a transaction which modifies lots of pages and spills sqlite's memory cache before COMMIT is reached. The only time you get "database is locked" is (1) if a connection requests the write-lock (ie. tries to modify the DB) when another connection already owns it, or (2) if a connection requests a read-lock while a writing connection is updating/ready to update the DB _and_ said update takes longer than the busy timeout configured for the connection. > But it > doesn't need to be that way! Each connection can have its own journal file, > especially if it's in memory. Once one connection commits, the other > connection will no longer be allowed to commit. It will be forced to > rollback (or perhaps rebase if there are no conflicts). > If the other connection isn't allowed to commit, how is this materially different from the semantics currently provided? Why would sqlite wait until a client tries to COMMIT before raising an error when it already knows that another write transaction is in progress? -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
