Hi,
I'm reading the docs:
https://sqlite.org/isolation.html
...and I need some clarification!
It is easy to see that ROLLBACK mode is SERIALIZABLE because all concurrent
readers have to leave before a PENDING lock is upgraded to an EXCLUSIVE
lock.
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.
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?
Thanks for your help!
Best wishes,
@ndy
--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users