Re: Read Committed transaction with long query

Hi!

2011/5/12 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:
> Durumdara wrote:
>>Two table:
>>Main
>>Lookup
>>
>>The query is:
>>select Main.*, Lookup.Name
>>left join Lookup on (Main.Type_ID = Lookup.ID)
>
> hat's not correct SQL, but I think I understand what you mean.

Sorry, the from is missed here... :-(

>
>
>>Lookup:
>>ID Name
>>1 Value1
>>2 Value 2
>>3 Value 3
>>
>>Many records is in Main table (for example 1 million).
>>
>>What happens in this case (C = connection):
>>
>>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.

Thanks! Great!

>
> 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!

Thanks:
dd