Re: A contradiction in 13.2.1

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.

​
​