Transaction Isolation Error

[yanboer]

Transaction Isolation Error

[kainwen]

I think it is the same RCA as you submitted #17134
We understand the problem and are trying to fix now. Will update more.

[yanboer]

Thank you for your response. I hope you have a wonderful day!

[interma]

Some notable notes after test:

1. you will get the correct results when using similar logic via plain Sql
   for example:

ubuntu@gpdb2:~/script$ cat test_for_update2.sql
begin;
	select version from test2 where id = 1 for update;
	update test2 set version=version+1 where id = 1;
commit;

version value (sum of Txn count) is correct after pgbench test.

2. go further: found select for update in UDF doesn't work as expected:

-- create another similar UDF
CREATE OR REPLACE FUNCTION public.test_for_update_long(a integer, b integer)
RETURNS void
LANGUAGE plpgsql
AS $$
declare temp_version integer;
declare temp_time timestamp;
declare sess text;
declare cmd text;
BEGIN
  show gp_session_id into sess;
  raise notice 'sess% entered into test_for_update()',sess;
  perform pg_sleep(0.005);

  select version, now() into temp_version, temp_time from test2 where id = a for update;
  perform pg_sleep(3600); -- hold the row lock a long time
END;
$$;

-- session1: run the UDF
issue=# select test_for_update_long(1, 1);
NOTICE:  sess1889 entered into test_for_update()
(sleeping)

-- session2 (found it didn't be blocked by session1's row lock)
issue=# select version from test2 where id = 1 for update;
 version
---------
    4804
(1 row)

If you use plain Sql, the behaviour is correct:

-- session1
issue=# begin;
BEGIN
issue=# select version from test2 where id = 1 for update;
 version
---------
    4804
(1 row)

-- session2
issue=# select version from test2 where id = 1 for update;
(blocked until session is finished)

Based on the above tests, I think it's not the same issue as #17134 (Txn status isn't consistent between QD and QE). I will investigate more and give updates later.

[interma]

@yanboer
I think got the root cause after did more tests:
One key point is plpgsql UDF doesn't create a transaction explicitly: (refer: https://www.postgresql.org/docs/12/plpgsql-structure.html):

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction.`,

In your test_for_update.sql, though it's a single Txn in the QD, but it will dispatch some single and dependent local Txns into QEs.
So, select version from test2 where id = 1 for update; only holds the row lock in the scope of its local Txn, doesn't include the following update statement (this can explain why my test_for_update_long() doesn't work in my previous comment).
And why it's ok when disable GDD? The reason is QD is blocked by an upgraded lock (not in QE).

For your purpose, you have to start a global Txn, then call the UDF, example:

$ cat test_for_update4.sql
begin; select test_for_update(1, 1); end;

$ pgbench issue -n -P 1 -j 4 -c 16 -t 300 -f test_for_update4.sql
...
NOTICE:  sess121 cmd2398 updated from 4799 to 4800
transaction type: test_for_update4.sql
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 4
number of transactions per client: 300
number of transactions actually processed: 4800/4800

I got the correct result in my env, you can take a try.
Thanks.