Re: Read Committed transaction with long query

Durumdara wrote:
>>> C1.) begin read committed
>>> C1.) starting this query
>>> C1.) query running
>>> C2.) begin read committed
>>> C2.) update Lookup set Name = "New2" where ID = 2
>>> C2.) commit
>>> C1.) query running
>>> C1.) query finished
>>>
>>> Is it possible to the first joins (before C2 modifications) are
>>> containing "Value2" on the beginning of the query and "New2" on the
>>> end of the query?
>>> So is it possible to the long query is containing not consistent state
>>> because of C2's changing? For example mixing "Value2" and "New2"?
>>
>> No, this is not possible.
>>
>> See
>> http://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-READ-COMMITTED :
>>
>>  When a transaction uses this [read committed] isolation level, a SELECT query
>>  (without a FOR UPDATE/SHARE clause) sees only data committed before the query began;
>>  it never sees either uncommitted data or changes committed during query execution
>>  by concurrent transactions.
>
> Query is meaning statement here?
> For example if I have more statement in one "Query" are they running
> separatedly?
> They can be see the modifications?
>
> Query text (or stored procedure body):
> "insert into ... ;" +
> "update ...;" +
> "select ..."
>
> Are they handled as one unit, or they are handled one by one?
> AutoCommit = False!

"Query" is usually used as a synonym for "SQL statement", but the term
is probably not exactly defined.

To be more precise in this case one could say "a single reading SQL statement".

So if you have several consecutive statements, each one may see different data.

This is the case, no matter if all statements run in one transaction or not.

If you want several statements to see exactly the same data (a "snapshot" of
the database), you have to pack them into one transaction and use isolation
level REPEATABLE READ.

Yours,
Laurenz Albe