Re: [GENERAL] Read Committed transaction with long query
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Read Committed transaction with long query
Hi! 2011/5/12 Albe Laurenz laurenz.a...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
