On Wed, Jan 27, 2016 at 5:59 PM, David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster <studdu...@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
