Re: Commit visibility guarantees

On Mon, May 18, 2009 at 5:14 PM, Sam Mason <sam@samason.me.uk> wrote:
> On Mon, May 18, 2009 at 04:38:36PM -0500, Marsh Ray wrote:
>> The central question: So if I successfully commit an update
>> transaction on one connection, then instantaneously issue a select on
>> another previously-opened connection, under what circumstances am I
>> guaranteed that the select will see the effects of the update?
>>
>> Maybe this is the statement I'm looking for: "in Read Committed mode
>> each new command starts with a new snapshot that includes all
>> transactions committed up to that instant, subsequent commands in the
>> same transaction will see the effects of the committed concurrent
>> transaction".
>
> For read committed that sounds like what I'd expect, row level locking
> buys you a bit more in implementation terms but just complicates the
> formal side as queries don't attempt to do any locking by default. I'm
> not aware of any formally defined semantics that PG tries to be a
> faithful implementation of---i.e. there may be bugs, but when they're
> fixed where are we aiming for.  If somebody could come up with a nice
> set of inductive definitions I'd be interested in seeing what they
> implied as well.

Do you know if this kind of concurrency test is included in pg's
regression tests?

>> But this statement is just an aside when making a
>> different point, and I see other statements like "So the whole concept
>> of "now" is somewhat ill-defined anyway."
>
> Not sure if it's quite as bad as that; transactional semantics go a
> long way to making large classes of problems simple.  The interactions
> between two independent systems that both have transactional semantics
> get very awkward.  Unbounded rollback being a term I remember, but can't
> remember when/why it applies.

At some point, a committed update has got to show up in other
connections' selects or users would obviously complain. However, is
the lag guaranteed to be zero?

>> "This is not normally a big problem if the client applications are
>> isolated from each other, but if the clients can communicate via
>> channels outside the database then serious confusion may ensue." And
>> communication via outside channels is exactly what the app is doing.
>
> Yes, things get awkward when you start doing this.

Yep, awkward city. I'm just trying to figure out what guarantees I do
get out of pg in order to analyze it going forward.

> Is there anyway to keep things "inside" the database, using NOTIFY or
> somesuch?

Unfortunately no, the db really is between external actors that also
have their own kernel-object-fast signaling mechanism. The data
currently being passed via the db could be duplicated over this side
channel, but it would be far more interesting to learn if a basic
assumption was wrong.

> Could you define what you mean by real-time, do you mean
> the strict academic meaning or just that you want "interactive" things
> happening and it would be annoying if they were delayed by a few tens of
> milliseconds (as opposed to someone dieing because something got delayed
> by a millisecond).

It is real-time in the academic definition, but most deadlines are
measured in seconds. Not too different than a web app really.

- Marsh