Thread

Issue in MERGE with concurrent UPDATE and MERGE

Shruthi Gowda <gowdashru@gmail.com> — 2023-01-05T10:36:19Z

Hi,

While I was running some isolation tests for MERGE, I noticed one issue
when MERGE tries to UPDATE rows that are concurrently updated by another
session.

Below is the test case for the same.


==================== TEST CASE START =============================


 DROP TABLE target;

  DROP TABLE source;


  CREATE TABLE source (id int primary key, balance int);

  INSERT INTO source VALUES (1, 100);

  INSERT INTO source VALUES (2, 200);


  CREATE TABLE target (id int primary key, balance int);

  INSERT INTO target VALUES (1, 10);

  INSERT INTO target VALUES (2, 20);


Session 1:


begin;

UPDATE target SET balance = balance + 1;

select * from target;


Session 2:


begin;

MERGE INTO target t

  USING (SELECT * from source) s

  ON (s.id = t.id)

  WHEN MATCHED THEN

    UPDATE SET balance = t.balance + s.balance

  WHEN NOT MATCHED THEN

    INSERT (id, balance) VALUES (s.id, s.balance);


< MERGE will wait because the rows are locked by Session 1 >



Session 1:


commit;


Session 2:


 SELECT * FROM target;

  commit;


================================ TEST CASE END
=================================



The MERGE fails with the error :

ERROR:  duplicate key value violates unique constraint "target_pkey"
DETAIL:  Key (id)=(2) already exists.



However, the above test case works fine when the target table has only one
matching row with the source table. When there are multiple matching rows
and those rows are concurrently updated, only the first record gets updated
in MERGE. The subsequent records fail to update and return from
ExecMergeMatched( ) from the below place and enter into the WHEN NOT
MATCHED INSERT flow.


(void) ExecGetJunkAttribute(epqslot,

                                              resultRelInfo->ri_RowIdAttNo,

                                               &isNull);

 if (isNull)

     return false;




Regards,
Shruthi KC
EnterpriseDB: http://www.enterprisedb.com