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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
