A contradiction in 13.2.1

Dane,

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.

This is (almost) true for the REPEATABLE READ or SERIALIZABLE modes.
(However, the snapshot is taken as the first non-transaction-control
statement in the transaction starts.)

For "READ COMMITTED", the docs read (emphasis added):
"In effect, a SELECT query sees a snapshot of the database as of the
instant the *query* begins to run. However, SELECT does see the effects
of previous updates executed within its own transaction, even though
they are not yet committed."

(
http://www.postgresql.org/docs/9.5/static/transaction-iso.html#XACT-READ-COMMITTED
)

So if you re-run the same query multiple times within a single
transaction, you might get different results depending.

best regards,

-hannes

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster <studdugie@gmail.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.

​
​

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

On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster <studdugie@gmail.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

​