Pavel Ivanov <paiva...@gmail.com> wrote: > 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.
So then it doesn't support statement-level read consistency, as defined in its own documentation? The claim was that, among other things, "as query execution proceeds, only data committed before the query began is visible to the query." But setting myfield to 2 has not yet been committed by the time the query began. The only way it makes sense is if the word "commit" means something other than "commit a transaction" - something like "a data manipulation statement successfully runs to completion". -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
