On Tuesday, 9 July, 2019 20:34, Andy Bennett <andy...@ashurst.eu.org> wrote:
>However, the wording for WAL mode is confusing me. >isolation.html says 'all transactions in SQLite show "serializable" >isolation.' but it also says 'In WAL mode, SQLite exhibits "snapshot >isolation"'. >Snapshot Isolation and Serializable often (in other engines) mean >different things at commit time ( >https://blogs.msdn.microsoft.com/craigfr/2007/05/16/serializable-vs- >snapshot-isolation-level/ >), but SQLite seems to say that the snapshot isolation is upgraded to >serializable by forbidding readers to upgrade to writers if another >writer got in before them: >'The attempt by X to escalate its transaction from a read transaction >to a write transaction fails with an SQLITE_BUSY_SNAPSHOT error because >the snapshot of the database being viewed by X is no longer the latest >version of the database.' >So far, so good. >However. the definition of SQLITE_BUSY_SNAPSHOT at >https://sqlite.org/rescode.html#busy_snapshot says: >----- >1. Process A starts a read transaction on the database and does one > or more SELECT statement. Process A keeps the transaction open. >2. Process B updates the database, changing values previous read by > process A. >3. Process A now tries to write to the database. But process A's view > of the database content is now obsolete because process B has modified > the database file after process A read from it. Hence process A gets > an SQLITE_BUSY_SNAPSHOT error. >----- >In particular 'Process B updates the database, changing values >previous read by process A.' seems to suggest that values read >by A have to be changed to effect the SQLITE_BUSY_SNAPSHOT >error in A. SQLite does not track individual "rows" or "tables" being changed. In (2) "changing values previously read by process A" means the database has changed since process A's view of the database was created. What exactly changed is immaterial, since tracking of changes only occurs at the "whole database" level. >Is that last quote just imprecise writing or is there really a >difference between SQLite's Snapshot Isolation in WAL mode and >its Serializable isolation in ROLLBACK mode? Yes, there is a difference. In rollback mode, a transaction merely places a shared lock on the database preventing any changes whatsoever from being committed to the database. All outstanding transactions must be "closed" before a commit may proceed (acquire an EXCLUSIVE lock on the entire database including all its rows in all tables). Thus "changes" to the database are "serialized" because they can only occur in series and only when nothing else is "looking" (since looking requires at least a read lock, which will prohibit the update from being committed). WAL mode however, is different. When you commence a "read transaction" in WAL mode you are creating a *snapshot* of the database at that time (actually, the reference to *snapshot* is incorrect, the actual isolation level is "repeatable read"). "repeatable read" markers can be thought of as "timestamps" -- that is, a process holding a "repeatable read" lock against the database can see the database up to the time of its "timestamp" was created (when the lock was obtained). It cannot see changes that are made *after* that time. If another process obtains a "repeatable read" timestamp that is the same as one already obtained by another process, and then commits those changes, the original process is still holding a "repeatable read" lock on the database as it existed BEFORE the change was committed. Thus it cannot update the database because it is seeing a "historical" version of the database and not the "current" view of the database -- that is it is looking at the database as it was at the time it obtained its "repeatable read" timestamp, and cannot be permitted to make changes since it is looking at an "old view" that does not represent an updateable state of affairs. In effect WAL is a "stack of changes" to be applied to the database file. When a read lock is obtained, the position in the stack is remembered. If something else adds more data to the stack, then the original locker is no longer "top of stack" and cannot write changes to the database because it cannot "see" the changes made after it entered repeatable read isolation. see https://www.sqlite.org/wal.html especially 2.2 Concurrency -- 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
