On Mon, Sep 30, 2019 at 2:07 PM Keith Medcalf <kmedc...@dessus.com> wrote:
> On Monday, 30 September, 2019 02:06, Dominique Devienne < > ddevie...@gmail.com> wrote: > >On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf <kmedc...@dessus.com> > wrote: > >> On Sunday, 29 September, 2019 01:28, Gwendal Roué < > gwendal.r...@gmail.com> wrote: > > >> >Those N reader connections allow concurrent database reads. Those > >> "reads" are generally wrapped in a deferred transaction which provides > >> snapshot isolation. > > >> No, it provides REPEATABLE-READ isolation. There is no actual > >> "snapshot" taken and no snapshot exists. > > > You are merely not seeing data written to the WAL transaction log at a > > point-in-time subsequent to the point in time at which you commenced > > the "repeatable-read". > > >I don't see where you are going with this Keith. > >Repeatable-reads "in the past" *are* snapshots IMHO. > > Generally no. "Snapshot Isolation" does not really exist, though some > RDBMS have created it to permit more opportunistic updates. Reads are at > the Repeatable-Read isolation level (both for WAL and DELETE journal > modes), and writes are Serialized. Snapshot Isolation is an invention of > the MVCC folks to theoretically permit greater update concurrency at the > expense of serializable isolation and introduces anomalies into the > database read and write processing that cannot occur when the updates are > serialized, and which generally requires the application programmer to take > extra steps to ensure database consistency. > > >The WAL file *does* contain enough information combined with the main > >database file pages, to logically *and* physically represent a "snapshot" > >of the DB at that point-in-time. > > For the purposes of reading only yes, I suppose you could call it a > "snapshot", except that it isn't. It is just a point-in-time > repeatable-read. You can only upgrade a transaction from read to write if > you are holding the "top" snapshot (that is, you must be seeing the entire > database, not a point-in-time version of it). > > >So not calling it a "snapshot" is a stretch at the very least. What is a > "snapshot" > >according to you, if that's not it? > > Snapshot Isolation is implemented by a bunch of different databases that > do not conform to the SQL Standard and it introduces anomalies into the > update process that cannot be introduced when using Serializable > Isolation. > > https://en.wikipedia.org/wiki/Isolation_(database_systems) > https://en.wikipedia.org/wiki/Snapshot_isolation > > >And also why do you think Richard, who knows a thing or two about > >databases, called these API *snaphot* then? > > Because it is a convenient descriptor, perhaps? They may in fact be > considered to be a snapshot of the database as it existed at some point in > the past (without full view of all committed transactions) however only the > "top" snapshot, the one that has a view of all committed transactions is > permitted to update/write to the database. > > >I'm genuinely curious here. I think I disagree with you, but most time I > >do, I'm wrong, so I'd like to understand, really. --DD > > The isolation is either repeatable-read for read transactions, or > serializable for writes. It is not Snapshot Isolation. > So although one may consider that what you are looking at is a "snapshot" > of the database that existed at a particular point-in-time, it should not > be confused with "snapshot isolation" which is an entirely different beast > altogether. > So I guess our main difference here, is that I have no qualms at all with point-in-time *read-only* repeatable-read transaction being called a "Snapshot". MVCC (which to me is synonymous with snapshots) is more about read-consistency across statements (i.e. a read transaction) that does *not* prevent writes. Using snapshots, I can parallelize access to several tables across connections (and threads), ensuring read-consistency in several separate transactions and connections. I used to do that in Oracle, and I'm glad that I can try to do it in SQLite too now. There are caveats of course, like controlling checkpointing, but I can live with that. --DD PS: Note that I never used "Snapshot *Isolation*" myself. For me, Snapshot = point-in-time read-consistency. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
