On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 9/13/2012 4:39 PM, Petite Abeille wrote: >> >> >> On Sep 13, 2012, at 10:24 PM, Igor Tandetnik <itandet...@mvps.org> wrote: >> >>>> In other words, a select should only ever see what existed at the >>>> beginning of the query, and that is that. >>> >>> >>> Again, the concept of isolation level applies to transactions, not to >>> individual queries. >> >> >> Let me rephrase that, by paraphrasing some other DB doc [1]: >> >> "statement-level read consistency. This guarantees that all the data >> returned by a single query comes from a single point in time—the time that >> the query began. Therefore, a query never sees dirty data or any of the >> changes made by transactions that commit during query execution. As query >> execution proceeds, only data committed before the query began is visible to >> the query. The query does not see changes committed after statement >> execution begins." >> >> So… SQLite always provide statement-level read consistency, yes? > > > In SQLite, a query cannot see changes made by other transactions (whether > committed or otherwise) that weren't already committed by the time the > current transaction started. But it may observe changes made by the current > transaction - the transaction the query is running in. If that's what the > definition means by "dirty data", then no, SQLite doesn't provide > statement-level read consistency. > > But I can't help but wonder. Consider this scenario: > > begin; > update mytable set myfield=1; > commit; > > begin; > update mytable set myfield=2; > select myfield from mytable; > commit; > > Are you saying that in Oracle, the SELECT statement would observe myfield=1? > That seems to follow from the definition you quote, but I can't imagine how > such behavior could be useful. In SQLite, the SELECT statement would > certainly see myfield=2.
No, Oralce will return myfield=2 in this case. Oracle's statement isolation level (which is not supported by SQLite) is for cases like this: begin; open cursor c as select myfield from mytable; fetch next row from c; fetch next row from c; update mytable set myfield=2 where ...; fetch next row from c; fetch next row from c; commit; In this case cursor will never observe myfield=2 and this behavior is well defined, documented and guaranteed. Pavel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
