Re: Synchronization levels in SR

On Wed, Sep 8, 2010 at 9:32 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Wed, Sep 8, 2010 at 10:07 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Wed, Sep 8, 2010 at 8:30 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>>> And in any event, there is ALWAYS a window of
>>>> time during which the client doesn't know the transaction has
>>>> committed but other transactions can potentially see its effects.
>>>
>>> Yep. The problem here is that synchronous replication is likely to
>>> make the window very big.
>>
>> So what?  If the correctness of your application depends on the
>> *amount of time* this window lasts, it's already broken.  It seems
>> like you're arguing that we should artificially increase lock
>> contention to guard against possible race conditions in user
>> applications.  That doesn't make any sense to me, so one of us is
>> confused.
>
> Yep ;) On second thought, the problem here is that the effects of
> the transaction marked as committed but still waiting for replication
> can disappear after failover.

Ah! I think that's right. So the scenario we're trying to guard
against something like this. A customer makes a withdrawal of money
from an ATM; their bank balance is debited. The transaction tries to
commit. After the transaction becomes visible to other backends but
before WAL is reaches the standby, another transaction begins and
reads the customer's balance. Naturally, they get the new, lower
balance. Crash, master dead. Failover. If another transcation
begins and reads the customer's balance again, it's back to the old
value. So we have a phantom transaction: it appeared as committed and
then vanished again.

So that means we have to make sure that none of the effects of a
transaction can be seen until WAL is fsync'd on the master AND the
slave has acked.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company