Re: Sync Rep: First Thoughts on Code - Mailing list pgsql-hackers

Good answers, Markus. Thanks.<br /><br /> I've bought the thinking of several here that the user should have some
controlover what they expect (and what optimizations they are willing to accept as a good choice), but that commit
shouldstill be able to have a capped time limit.<br /><br /> I can think of many of my own applications where I would
chooseone mode vs another mode, even within the same application, depending on the operation itself. The most important
requirementis that transactions are durable. It becomes convenient, though, to provide additional guarantees for some
operationsequences.<br /><br /> I still see the requirement for seat reservation, bank account, or stock trading, as
synchronizingusing read-write locks before starting the select, rather than enforcing latest on every select.<br /><br
/>For my own bank, when I do an online transaction, operations don't always immediately appear in my list of
transactions.They appear to sometimes be batched, sometimes in near real time, and sometimes as part of some sort of
dayend processing.<br /><br /> For seat reservation, the time the seat layout is shown on the screen is not usually
lockedduring a transaction. Between the time the travel agent brings up the seats on the plane, and the time they
selectthe seat, the seat could be taken. What's important is that the reservation is durable, and that conflicts are
notintroduced. The commit must fail if another person has chosen the seat already already. The commit does not need to
waituntil the reservation is pushed out to all systems before completing. The same is true of stock trading.<br /><br
/>However, it can be very convenient for commits to be immediately visible after the commit completes. This allows for
laziermodels, such as a web site that reloads the view on the reservations or recent trades and expects to see recent
commitsno matter which server it accesses, rather than taking into account that the commit succeeded when presenting
thenext view.<br /><br /> If I look at sites like Google - they take the opposite extreme. I can post a message, and it
remembersthat I posted the message and makes it immediately visible, however, I might not see other new messages in a
threaduntil a minute or more later.<br /><br /> So it looks like there is value to both ends of the spectrum, and while
Ifeel the most value would be in providing a very fast system that scales near linear to the number of nodes in the
system,even at the expense of immediately visible transactions from all servers, I can accept that sometimes the
expectationsare stricter and would appreciate seeing an option to let me choose based upon my requirements.<br /><br />
Cheers,<br/> mark<br /><br /><br /> Markus Wanner wrote: <blockquote cite="mid:494CFFFF.2060200@bluegap.ch"
type="cite"><prewrap="">Hi,
 

Mark Mielke wrote: </pre><blockquote type="cite"><pre wrap="">Where does the expectation come from?
</pre></blockquote><prewrap="">
 
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).
 </pre><blockquote type="cite"><pre wrap="">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?   </pre></blockquote><pre wrap="">
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:
<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/8.3/static/transaction-iso.html">http://www.postgresql.org/docs/8.3/static/transaction-iso.html</a>

[2]: Pg 8.3 Docu: COMMIT command:
<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/8.3/static/sql-commit.html">http://www.postgresql.org/docs/8.3/static/sql-commit.html</a>

[3]: README of transam (src/backend/access/transam/README):
<a class="moz-txt-link-freetext"
href="https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/src/backend/access/transam/README#L224">https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/src/backend/access/transam/README#L224</a>
 </pre></blockquote><br /><br /><pre class="moz-signature" cols="72">-- 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>