Re: SERIALIZABLE with parallel query

On Thu, Sep 21, 2017 at 4:13 PM, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com
> wrote:

> On Tue, Sep 19, 2017 at 1:47 PM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
> > During testing of this patch, I found some behavior difference
> > with the support of parallel query, while experimenting with the provided
> > test case in the patch.
> >
> > But I tested the V6 patch, and I don't think that this version contains
> > any fixes other than rebase.
> >
> > Test steps:
> >
> > CREATE TABLE bank_account (id TEXT PRIMARY KEY, balance DECIMAL NOT
> NULL);
> > INSERT INTO bank_account (id, balance) VALUES ('X', 0), ('Y', 0);
> >
> > Session -1:
> >
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> > SELECT balance FROM bank_account WHERE id = 'Y';
> >
> > Session -2:
> >
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> > SET max_parallel_workers_per_gather = 2;
> > SET force_parallel_mode = on;
> > set parallel_setup_cost = 0;
> > set parallel_tuple_cost = 0;
> > set min_parallel_table_scan_size = 0;
> > set enable_indexscan = off;
> > set enable_bitmapscan = off;
> >
> > SELECT balance FROM bank_account WHERE id = 'X';
> >
> > Session -1:
> >
> > update bank_account set balance = 10 where id = 'X';
> >
> > Session -2:
> >
> > update bank_account set balance = 10 where id = 'Y';
> > ERROR: could not serialize access due to read/write dependencies among
> > transactions
> > DETAIL: Reason code: Canceled on identification as a pivot, during
> write.
> > HINT: The transaction might succeed if retried.
> >
> > Without the parallel query of select statement in session-2,
> > the update statement in session-2 is passed.
>

Hi Thomas,

> Yeah. The difference seems to be that session 2 chooses a Parallel
> Seq Scan instead of an Index Scan when you flip all those GUCs into
> parallelism-is-free mode. Seq Scan takes a table-level predicate lock
> (see heap_beginscan_internal()). But if you continue your example in
> non-parallel mode (patched or unpatched), you'll find that only one of
> those transactions can commit successfully.
>

Yes, That's correct. Only one commit can be successful.

> Using the fancy notation in the papers about this stuff where w1[x=42]
> means "write by transaction 1 on object x with value 42", let's see if
> there is an apparent sequential order of these transactions that makes
> sense:
>
> Actual order: r1[Y=0] r2[X=0] w1[X=10] w2[Y=10] ... some commit order ...
> Apparent order A: r2[X=0] w2[Y=10] c2 r1[Y=0*] w1[X=10] c1 (*nonsense)
> Apparent order B: r1[Y=0] w1[X=10] c1 r2[X=0*] w2[Y=10] c2 (*nonsense)
>
> Both potential commit orders are nonsensical. I think what happened
> in your example was that a Seq Scan allowed the SSI algorithm to
> reject a transaction sooner. Instead of r2[X=0], the executor sees
> r2[X=0,Y=0] (we scanned the whole table, as if we read all objects, in
> this case X and Y, even though we only asked to read X). Then the SSI
> algorithm is able to detect a "dangerous structure" at w2[Y=10],
> instead of later at commit time.
>

Thanks for explaining with more details, now I can understand some more
about serialization.

After I tune the GUC to go with sequence scan, still I am not getting the
error
in the session-2 for update operation like it used to generate an error for
parallel
sequential scan, and also it even takes some many commands until unless the
S1
commits.

I am just thinking that with parallel sequential scan with serialize
isolation,
the user has lost the control of committing the desired session. I may be
thinking
a rare and never happen scenario.

I will continue my review on the latest patch and share any updates.

Regards,
Hari Babu
Fujitsu Australia