Re: Transactions involving multiple postgres foreign servers, take 2

On Thu, 18 Jun 2020 at 08:31, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:
>
> > okay, so it seems we need few things which middleware (Pangea) expects
> > if we have to follow the design of paper.
>
> Yes.
>
> > I haven't read the paper completely but it sounds quite restrictive
> > (like both commits and snapshots need to wait).
>
> Maybe. There is a performance evaluation in the paper. You might want
> to take a look at it.
>
> > 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?
>
> My concern is, FDW+2PC without atomic visibility could lead to data
> inconsistency among servers in some cases. If my understanding is
> correct, FDW+2PC (without atomic visibility) cannot prevent data
> inconsistency in the case below. Initially table t1 has only one row
> with i = 0 on both N1 and N2. By executing S1 and S2 concurrently, t1
> now has different value of i, 0 and 1.

IIUC the following sequence won't happen because COMMIT PREPARED
's1n1' cannot be executed before PREPARE TRANSACTION 's1n2'. But as
you mentioned, we cannot prevent data inconsistency even with FDW+2PC
e.g., when S2 starts a transaction between COMMIT PREPARED on N1 and
COMMIT PREPARED on N2 by S1. The point is this data inconsistency is
lead by an inconsistent read but not by an inconsistent commit
results. I think there are kinds of possibilities causing data
inconsistency but atomic commit and atomic visibility eliminate
different possibilities. We can eliminate all possibilities of data
inconsistency only after we support 2PC and globally MVCC.

>
> S1/N1: DROP TABLE t1;
> DROP TABLE
> S1/N1: CREATE TABLE t1(i int);
> CREATE TABLE
> S1/N1: INSERT INTO t1 VALUES(0);
> INSERT 0 1
> S1/N2: DROP TABLE t1;
> DROP TABLE
> S1/N2: CREATE TABLE t1(i int);
> CREATE TABLE
> S1/N2: INSERT INTO t1 VALUES(0);
> INSERT 0 1
> S1/N1: BEGIN;
> BEGIN
> S1/N2: BEGIN;
> BEGIN
> S1/N1: UPDATE t1 SET i = i + 1; -- i = 1
> UPDATE 1
> S1/N2: UPDATE t1 SET i = i + 1; -- i = 1
> UPDATE 1
> S1/N1: PREPARE TRANSACTION 's1n1';
> PREPARE TRANSACTION
> S1/N1: COMMIT PREPARED 's1n1';
> COMMIT PREPARED
> S2/N1: BEGIN;
> BEGIN
> S2/N2: BEGIN;
> BEGIN
> S2/N2: DELETE FROM t1 WHERE i = 1;
> DELETE 0
> S2/N1: DELETE FROM t1 WHERE i = 1;
> DELETE 1
> S1/N2: PREPARE TRANSACTION 's1n2';
> PREPARE TRANSACTION
> S2/N1: PREPARE TRANSACTION 's2n1';
> PREPARE TRANSACTION
> S2/N2: PREPARE TRANSACTION 's2n2';
> PREPARE TRANSACTION
> S1/N2: COMMIT PREPARED 's1n2';
> COMMIT PREPARED
> S2/N1: COMMIT PREPARED 's2n1';
> COMMIT PREPARED
> S2/N2: COMMIT PREPARED 's2n2';
> COMMIT PREPARED
> S2/N1: SELECT * FROM t1;
> i
> ---
> (0 rows)
>
> S2/N2: SELECT * FROM t1;
> i
> ---
> 1
> (1 row)
>

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services