On Sun, 4 Feb 2024 at 18:19, zwj <sxzwj@vip.qq.com> wrote:
>
> I found an issue while using the latest version of PG15 (8fa4a1ac61189efffb8b851ee77e1bc87360c445).
>
> This issue is related to Megre into and other concurrent statements being written.
> I obtained different results in Oracle and PG using the same statement below.
> Based on my personal judgment, the result set of this statement in pg is abnormal.
>
I would say that the Postgres result is correct here. The merge update
action would have updated the row with id=2, setting year=30, but
since there is a concurrent update, changing that id to 5, it waits to
see if that transaction commits. Once it does, the id no longer
matches, and merge update action is aborted, and the "not matched"
action is performed instead. That's consistent with the result that
you'd get if you performed the 2 transactions serially, doing the
update first then the merge.
Oracle, on the other hand, proceeds with the merge update action,
after the other transaction commits, even though the row being updated
no longer matches the join condition. Not only does that seem to be
incorrect, it's inconsistent with what both Oracle and Postgres do if
you replace the merge command with the equivalent standalone update
for that row: "update mergeinto_0023_tb01 set year = 30 where id = 2"
in the second session. So I'd say that this is an Oracle bug.
Regards,
Dean