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. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
