On 9/14/2012 10:36 AM, Pavel Ivanov wrote:
On Fri, Sep 14, 2012 at 5:58 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
Wait a minute. If "statement-level read consistency" only applies to two queries in
different transactions, what bearing, if any, does it have on your example of "open, fetch,
update, fetch"? There, everything happens in the same transaction. I'm thoroughly confused.
I'm sorry, I made that example before I carefully re-read Petite's
email and understood what you are concerned about.
I'm not actually concerned about anything. It is difficult for me to
possibly care less about the intricacies of Oracle's transaction
management. It's just that Petite asked whether SQLite supports
something called "statement-level read consistency", and I've never
heard of such a beast, and was confused by the formal definition thereof
that Petite cited.
Let me see if I understand it. Oracle (optionally?) provides transaction
isolation levels weaker than "serializable", whereby transaction A may
see changes made by another transaction B, committed after A started.
However, it guarantees that any individual statement within A, during
its lifetime, will see data either as it existed before B committed, or
as it exists afterward, but never a mix of the two. Whereas two
identical statements executed one after another may very well observe
different data. This is what's called "statement-level read consistency".
Since SQLite only provides serializable transaction isolation level, no
statement in transaction A will ever see any changes made by transaction
B. This is a strictly stronger guarantee than "statement-level read
consistency", rendering moot the question of whether SQLite supports the
same.
In addition to, and independently of, all of the above, Oracle provides
a similar guarantee (whose name was not mentioned in this thread) that a
statement A within a transaction will see all changes by another
statement B within the same transaction if B completes before A starts;
and will not see any changes made by yet third statement C, still within
the same transaction, if C completes after A starts (even if C starts
while A was in progress). SQLite only provides half of that guarantee:
statement A will see the changes made by B; it may see none, some, or
all changes made by C, in an unpredictable way.
Thus, in SQLite it is best to avoid modifying the same data that an
outstanding SELECT statement is iterating over, as the outcome of such a
modification is unpredictable.
Does this sound right to you?
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
