Hi Alexander, Alexander Korotkov <[email protected]> 于2026年2月15日周日 09:23写道： > Oh, sorry I missed the begin statement for s1. The complete case should look > like this. > > s1# create table test (id int primary key, val int); > s1# insert into test values (1,0); > > s2# begin; > s2# update test set val = val + 100; > > s1# begin isolation level repeatable read; > s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc) > ON t.id = s.id > WHEN MATCHED THEN > UPDATE SET val = t.val + s.inc > WHEN NOT MATCHED THEN > INSERT (id, val) VALUES (s.id, s.inc); > (waiting ...) > > s2# commit; > > s1# MERGE 1 > s1# select * from test; > id | val > ----+----- > 1 | 200 > (1 row) >
I tried "update test set val = val + 100;" but the SQL reported a "could not serialize access due to concurrent update" error. It seems that the MERGE command should behave identically to UPDATE when performing a match action. I wrote a fix patch and attached it, and added your test case, too. -- Thanks, Tender Wang
From 9f7267cdc3bf5b34eb95686f288d39b5766142ac Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Tue, 24 Feb 2026 11:04:26 +0800 Subject: [PATCH] Fix MERGE match do update in RR isolation level. --- src/backend/executor/nodeModifyTable.c | 4 ++ .../expected/merge-match-do-update.out | 26 +++++++++++ src/test/isolation/isolation_schedule | 1 + .../specs/merge-match-do-update.spec | 43 +++++++++++++++++++ 4 files changed, 74 insertions(+) create mode 100644 src/test/isolation/expected/merge-match-do-update.out create mode 100644 src/test/isolation/specs/merge-match-do-update.spec diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 793c76d4f82..8d8c9fb54e3 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -3547,6 +3547,10 @@ lmerge_matched: *inputslot; LockTupleMode lockmode; + if (IsolationUsesXactSnapshot()) + ereport(ERROR, + (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), + errmsg("could not serialize access due to concurrent update"))); /* * The target tuple was concurrently updated by some other * transaction. If we are currently processing a MATCHED diff --git a/src/test/isolation/expected/merge-match-do-update.out b/src/test/isolation/expected/merge-match-do-update.out new file mode 100644 index 00000000000..c2cde0de606 --- /dev/null +++ b/src/test/isolation/expected/merge-match-do-update.out @@ -0,0 +1,26 @@ +Parsed test spec with 2 sessions + +starting permutation: b2 update2 b1 merge1 c2 select1 c1 +step b2: BEGIN; +step update2: + UPDATE test SET val = val + 100; + +step b1: + BEGIN ISOLATION LEVEL REPEATABLE READ; + +step merge1: + MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc) + ON t.id = s.id + WHEN MATCHED THEN + UPDATE SET val = t.val + s.inc + WHEN NOT MATCHED THEN + INSERT (id, val) VALUES (s.id, s.inc); + <waiting ...> +step c2: COMMIT; +step merge1: <... completed> +ERROR: could not serialize access due to concurrent update +step select1: + SELECT * FROM test; + +ERROR: current transaction is aborted, commands ignored until end of transaction block +step c1: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 4e466580cd4..95d8597e204 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -58,6 +58,7 @@ test: insert-conflict-do-select test: merge-insert-update test: merge-delete test: merge-update +test: merge-match-do-update test: merge-match-recheck test: merge-join test: delete-abort-savept diff --git a/src/test/isolation/specs/merge-match-do-update.spec b/src/test/isolation/specs/merge-match-do-update.spec new file mode 100644 index 00000000000..046c78826b9 --- /dev/null +++ b/src/test/isolation/specs/merge-match-do-update.spec @@ -0,0 +1,43 @@ +# MERGE UPDATE + +setup +{ + CREATE TABLE test (id int primary key, val int); + INSERT INTO test VALUES (1,0); +} + +teardown +{ + DROP TABLE test; +} + +session "s1" +step "b1" +{ + BEGIN ISOLATION LEVEL REPEATABLE READ; +} +step "merge1" +{ + MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc) + ON t.id = s.id + WHEN MATCHED THEN + UPDATE SET val = t.val + s.inc + WHEN NOT MATCHED THEN + INSERT (id, val) VALUES (s.id, s.inc); +} +step "select1" +{ + SELECT * FROM test; +} +step "c1" { COMMIT; } + +session "s2" +step "b2" { BEGIN; } +step "update2" +{ + UPDATE test SET val = val + 100; +} +step "c2" { COMMIT; } + +# Now with concurrency +permutation "b2" "update2" "b1" "merge1" "c2" "select1" "c1" \ No newline at end of file -- 2.34.1
