Hi,
Mark Mielke wrote:
> Where does the expectation come from?
I find the seat reservation, bank account or stock trading examples
pretty obvious WRT user expectations.
Nonetheless, I've compiled some hints from the documentation and sources:
"Since in Read Committed mode each new command starts with a new
snapshot that includes all transactions committed up to that instant" [1].
"This [SERIALIZABLE ISOLATION] level emulates serial transaction
execution, as if transactions had been executed one after another,
serially, rather than concurrently." [1]. (IMO this implies, that a
transaction "sees" changes from all preceding transactions).
"All changes made by the transaction become visible to others and are
guaranteed to be durable if a crash occurs." [2]. (Agreed, it's not
overly clear here, when exactly the changes become visible. OTOH,
there's no warning, that another session doesn't immediately see
committed transactions. Not sure where you got that from).
> I don't recall ever reading it in
> the documentation, and unless the session processes are contending over
> the integers (using some sort of synchronization primitive) in memory
> that represent the "latest visible commit" on every single select, I'm
> wondering how it is accomplished?
See the transaction system's README [3]. It documents the process of
snapshot taking and transaction isolation pretty well. Around line 226
it says: "What we actually enforce is strict serialization of commits
and rollbacks with snapshot-taking". (So the outcome of your experiment
is no surprise at all).
And a bit later: "This rule is stronger than necessary for consistency,
but is relatively simple to enforce, and it assists with some other
issues as explained below.". While this implies, that an optimization is
theoretically possible, I very much doubt it would be worth it (for a
single node system).
In a distributed system, things are a bit different. Network latency is
an order of magnitude higher than memory latency (for IPC). So a similar
optimization is very well worth it. However, the application (or the
load balancer or both) need to know about this potential lag between
nodes. And as you've outlined elsewhere, a limit for how much a single
node may lag behind needs to be established.
(As a side note: for a multi-master system like Postgres-R, it's
beneficial to keep the lag time as low as possible, because the larger
the lag, the higher the probability for a conflict between two
transactions on different nodes.)
Regards
Markus Wanner
[1]: Pg 8.3 Docu: Concurrency Control:
http://www.postgresql.org/docs/8.3/static/transaction-iso.html
[2]: Pg 8.3 Docu: COMMIT command:
http://www.postgresql.org/docs/8.3/static/sql-commit.html
[3]: README of transam (src/backend/access/transam/README):
https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/src/backend/access/transam/README#L224
