Postgres Pro
Downloads
Home   >   mailing lists

Thread: Read Committed transaction with long query

Read Committed transaction with long query

From
Durumdara
Date:
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

Re: Read Committed transaction with long query

From
"Albe Laurenz"
Date:
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

Re: Read Committed transaction with long query

From
Durumdara
Date:
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

Re: Read Committed transaction with long query

From
"Albe Laurenz"
Date:
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