On Fri, Sep 14, 2012 at 8:57 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > 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.
This weaker isolation level is (or at least was when I worked with Oracle databases) set by default. "Serializable" was optional as it imposes more problems for db administrators and requires more careful implementations on programmers side. > 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? Yes, that's right. Pavel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
