A contradiction in 13.2.1

From: Dane Foster <studdugie(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: A contradiction in 13.2.1
Date: 2016-01-27 22:23:09
Message-ID: CA+WxinKL8+EGbV77VWEueSsuAPxJoHuePouVMCObOt5uNoegcA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dane Foster 2016-01-27 22:30:47 Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures?
Previous Message Joshua Berkus 2016-01-27 21:25:36 Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures?