Обсуждение: Read Committed transaction with long query
Hi! Two table: Main Lookup The query is: select Main.*, Lookup.Name left join Lookup on (Main.Type_ID = Lookup.ID) 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"? Thanks for your help! Regards: dd
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. >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. 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. Yours, Laurenz Albe
Hi! 2011/5/12 Albe Laurenz <laurenz.albe@wien.gv.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
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