On Thu, Sep 21, 2017 at 4:13 PM, Thomas Munro <thomas.mu...@enterprisedb.com > wrote:
> On Tue, Sep 19, 2017 at 1:47 PM, Haribabu Kommi > <kommi.harib...@gmail.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