Re: Transactions involving multiple postgres foreign servers, take 2

On Tue, Jun 16, 2020 at 8:06 PM Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:
>
> >> > I think the problem mentioned above can occur with this as well or if
> >> > I am missing something then can you explain in further detail how it
> >> > won't create problem in the scenario I have used above?
> >>
> >> So the problem you mentioned above is like this? (S1/S2 denotes
> >> transactions (sessions), N1/N2 is the postgreSQL servers). Since S1
> >> already committed on N1, S2 sees the row on N1. However S2 does not
> >> see the row on N2 since S1 has not committed on N2 yet.
> >>
> >
> > Yeah, something on these lines but S2 can execute the query on N1
> > directly which should fetch the data from both N1 and N2.
>
> The algorythm assumes that any client should access database through a
> middle ware. Such direct access is prohibited.
>

okay, so it seems we need few things which middleware (Pangea) expects
if we have to follow the design of paper.

> > Even if
> > there is a solution using REPEATABLE READ isolation level we might not
> > prefer to use that as the only level for distributed transactions, it
> > might be too costly but let us first see how does it solve the
> > problem?
>
> The paper extends Snapshot Isolation (SI, which is same as our
> REPEATABLE READ isolation level) to "Global Snapshot Isolation", GSI).
> I think GSI will solve the problem (atomic visibility) we are
> discussing.
>
> Unlike READ COMMITTED, REPEATABLE READ acquires snapshot at the time
> when the first command is executed in a transaction (READ COMMITTED
> acquires a snapshot at each command in a transaction). Pangea controls
> the timing of the snapshot acquisition on pair of transactions
> (S1/N1,N2 or S2/N1,N2) so that each pair acquires the same
> snapshot. To achieve this, while some transactions are trying to
> acquire snapshot, any commit operation should be postponed. Likewise
> any snapshot acquisition should wait until any in progress commit
> operations are finished (see Algorithm I to III in the paper for more
> details).
>

I haven't read the paper completely but it sounds quite restrictive
(like both commits and snapshots need to wait). Another point is that
do we want some middleware involved in the solution? The main thing
I was looking into at this stage is do we think that the current
implementation proposed by the patch for 2PC is generic enough that we
would be later able to integrate the solution for atomic visibility?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com