Re: A contradiction in 13.2.1

On Wed, Jan 27, 2016 at 5:59 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster <studdugie(at)gmail(dot)com> wrote:
>
>> Hello,
>>
>> I'm trying to understand concurrency in PostgreSQL so I'm slowly reading
>> through chapter 13 of the fine manual and I believe I've found a
>> contradiction in section 13.2.1.
>>
>> ​My understanding of the second sentence of the first paragraph is that
>> read committed mode never sees "changes committed during query execution by
>> concurrent transactions". For example let's assume two transactions, A & B,
>> and the following:
>>
>> - A started before B
>> - B starts before A commits
>>
>> My understanding of the second sentence means that if A commits before B
>> then any updates made by A will continue to be invisible to B because B's
>> snapshot was before A committed. Now if I'm wrong about this then there is
>> no contradiction forthcoming.
>> The final sentence of the first paragraph is where I find the
>> contradiction. It says: "Also note that two successive SELECT commands
>> can see different data, even though they are within a single transaction,
>> if other transactions commit changes after the first SELECT starts and
>> before the second SELECT starts​"
>> ​.
>>
>> So the mental model I've built based on the first four sentences of the
>> first paragraph is that when a transaction starts in read committed mode a
>> snapshot is taken of the (database) universe as it exists at the moment of
>> its creation and that it's only updated by changes made by the transaction
>> that created the snapshot. So for successive SELECTs to see different data
>> because of updates outside of the transaction that created the snapshot is
>> a contradiction.
>>
>> Now my guess is that I'm thinking about it all wrong so if someone in the
>> know could shed some light on where/how my mental model breaks down I would
>> appreciate it.
>>
>> Regards,
>>
>> Dane
>>
>
> ​The main thing to remember is that "query != transaction".​
>
> A1 - BEGIN;
> ​A1 - SELECT FROM a
> B1 - BEGIN;
> B2 - UPDATE a
> B3 - COMMIT;
> A2 - SELECT FROM a - again
> A3 - COMMIT;
>
> Since the commit in B3 occurs before the second select A2 in READ
> COMMITTED the query A2 *will see* the update made in B2. But B3 must
> complete in its entirety for A2 to see it otherwise "it never sees [...]
> changes committed during query execution by concurrent transactions". The
> concurrency is with the individual statement A2 and not the entire A
> transaction. This is why it is called "READ COMMITTED" because within
> transaction A externally committed data is able to be read.
>
> David J.
>
>
> ​You are correct, I was conflating query w/ transaction. But it's clear
now. Thank you​
​
​.​

Dane

​