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.
