Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"
On 25/10/62 23:07, Brannon King 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". 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). Multiple WAL files could be supported in a similar fashion; they just need some kind of unique naming scheme. For recovery, the user would be prompted to select one or none. It doesn't seem that far from Sqlite's current behavior. Thoughts? This branch might interest you: https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2 The "BEGIN CONCURRENT" idea is that two connections may concurrently have independent write transactions based on optimistic read/write page locking. Dan. ~Brannon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"
On Sat, 26 Oct 2019 at 00:07, Brannon King 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
Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"
> > Two users – members of staff – enter data. Each user enters a new > invoice. One of these entries gets rolled back. What should their > software do ? Or should it just return an error message to the user ? > Multi-user data entry is not a part of my intended use case. I think other database engines are better suited for this need. It could be done, though, if you had an easy way to check for conflicts on commit. This change would get us much closer to this than we are now. My particular use case is for data verification. I have one part of the system that does some computation. I then have another part of the system that verifies that computation. I don't want the "verifier" to write to the database; that data will be written by the true data owner. However, the verifier to redo some of the inserts/deletes in order for the computation to come out correctly. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"
On 25 Oct 2019, at 5:07pm, Brannon King wrote: > 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). While lots of software supports rollback, in that it issues an error message rather than crashing, imagine what this would do in real life. Two users – members of staff – enter data. Each user enters a new invoice. One of these entries gets rolled back. What should their software do ? Or should it just return an error message to the user ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
