Re: Synchronization levels in SR

On Mon, Sep 6, 2010 at 10:02 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Then I respectfully suggest that you're releasing locks too early.
>
> Your proposal would allow a 2nd user to see the results of the 1st
> user's transaction before the 1st user knew about whether it had
> committed or not.
>
> I know why you want that, but I don't think its right.

Well that's always possible. The 1st user might just not wake up
before the 2nd user gets the response back.

The question is what happens if the server crashes and is failed over
to the slave. The 2nd user with the async transaction might have seen
data commited by the 1st user with his sync transaction but was
subsequently lost. Is the user expecting that making his transaction
synchronously replicated guarantees that *nobody* can see this data
unless the transaction is guaranteed to have been replicated or is he
only expecting it to guarantee that *he* can't see the commit until it
can be trusted to be replicated?

For that matter I'm not entirely clear I understand how the timing
here works at all. If transactions can't be considered to be committed
before they're acknowledged by the replica what happens if the master
crashes after the WAL is written and then comes back without a
failover. Then the transaction would be immediately visible even if it
still hasn't been replicated yet.

I think there's no way with our current infrastructure to guarantee
that other transactions can't see your data before it's been
replicated, So making any promise otherwise for some cases is only
going to be a lie.

To guarantee synchronous replication doesn't show data until it's been
replicated we would have to some kind of 2-phase commit where we send
the commit record to the slave and wait until the slave has received
it and confirmed it has written it (but it doesn't replay it unless
there's a failover) then write the master's commit record and send the
message to the slave that it's safe to replay those records.

--
greg