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

Reply via email to