[Bug]: Concurrent UNIQUE-key ODKU returns duplicate visible rows on a KEY-partitioned table #30084

Is there an existing issue for the same bug?

• I have checked the existing issues.

This reports a query correctness failure after concurrent ODKU, separately from the resolved hidden-UNIQUE-index scan-performance issue #29197. Searches for ODKU, key_concurrent_conflicts, concurrent duplicate rows, and recent duplicate-key issues did not identify an open issue accurately tracking this observation. Related issues and attribution limits are listed below.

Branch Name

main

Commit ID

cd8d5abf9665a97d13e50b04840fb74b877fd339

Other Environment Information

• Failing Main MOTR job: Run 38268423576 / Job 114990399929.
• TKE Family weekly-shadow-1x2: one TP CN and two AP CNs. This test connects directly to the single TP CN service, not through Proxy or random AP routing; the uploaded routing proof verifies the TP service endpoint.
• Namespace: mo-main-family-38268423576-1; TP pod: nightly-regression-dis-tp-cn-nvqjf.
• Each CN: 14 CPUs, 55 GiB container memory, 16 GiB FileService memory cache. TN: 14 CPUs, 32 GiB.
• Image: ccr.ccs.tencentyun.com/matrixone-dev/matrixone:commit-cd8d5abf9665a97d13e50b04840fb74b877fd339.
• Verified image digest: sha256:d554cbb460dc128049b329fb0e1d5e2e9c35d9a559cb2986d732b37e3a970b04.
• MOTR: 125b724ac7bddac2d8c634427ce20d7b3bef8f9a; nightly workflow: 27de5806d6a5f2ead8912199edd8ba54b601f491.
• The cluster CR does not override transaction mode/isolation. The tested code defaults to pessimistic RC, not the retired optimistic-RC mode of [Bug]: Optimistic RC concurrent UPDATE exposes duplicate primary-key rows (test-only mode) #29401. An in-session mode dump was not preserved.
• Important configured options: enable-sacrificing-freshness = 1, enable-cn-based-consistency = 0. See the freshness qualification below.
• The final Kubernetes snapshot shows all nine pods Ready with zero restarts. This is not a claim that every resource or service was healthy throughout the test.

Actual Behavior

The case creates a 100,000-row KEY-partitioned table with an integer primary key, a separate integer UNIQUE key, and an integer payload. A preliminary EXPLAIN ANALYZE ODKU executes once and establishes exactly (1,10,1) for uk=10.

It then issues 20 increment operations using a pool of at most five workers. Each operation opens its own autocommit connection, executes one ODKU, and closes the connection. All 20 futures return successfully; the helper has no write retry loop.

After all futures finish, a single UNIQUE-key SELECT returns 18 rows. The bounded diagnostic preserves the first three, all (1,10,16). A later PK SELECT returns three rows, all (1,10,16).

The first failure was printed at 2026-10-12 20:27:49.998 UTC / 2026-10-13 04:27:49.998 Asia/Shanghai. The full case ran from approximately 20:27:43.358 to 20:27:51.039 UTC. The report records 7,588 ms for the case; the later 21:01:46 UTC suite summary is a reprint, not a second execution.

PASS: plain_fixture_100k
PASS: plain_no_full_validation_scan
PASS: plain_conflict_target
PASS: plain_no_insert
PASS: key_fixture_100k
PASS: key_no_full_validation_scan
PASS: key_conflict_target
PASS: key_no_insert
PASS: range_fixture_100k
PASS: range_no_full_validation_scan
PASS: range_conflict_target
PASS: range_no_insert
PASS: pk_only_control_selective
PASS: pk_only_control_result
FAIL: key_concurrent_conflicts :: actual=[(1, 10, 16), (1, 10, 16), (1, 10, 16)] ops=20/20 total=not_checked expected=[(1, 10, 21)] expected_total=100000 matches=18 omitted=15
PASS: key_unique_update_rejected
FAIL: key_rejection_atomic :: actual=[(1, 10, 16), (1, 10, 16), (1, 10, 16)] ops=20/20 total=not_checked expected=[(1, 10, 21)] expected_total=100000 matches=3 omitted=0 prior_key_concurrent_conflicts=failed
SUMMARY: 2 assertion(s) failed

The same output is preserved in the uploaded MOTR artifact, not only in console formatting:

Artifact: tke-family-weekly-shadow-1x2-motr-38268423576-1
Artifact ID: 11774401013
File: run-1791836581604421739/results.jsonl
Case: 14_issue_regression.issue_29197_odku_unique_scan
Fields: actual_output / diff

Why this is actual query-result evidence

• The frozen scenario takes each result from a single run(...)[1] call. matches is len(rows) and actual is rows[:3]; it does not combine results from multiple queries or workers.
• The frozen helper opens connections with autocommit=True and returns list(cur.fetchall()) from a fresh cursor per statement.
• The database name includes the process ID and nanosecond timestamp, limiting ordinary cross-case fixture-name collisions.
• future.result() must succeed before completed_operations is incremented. An operation exception would instead produce scenario_completed failure and bypass this assertion.
• All three original hidden-index selective-scan checks passed; this failure is not another full-scan performance observation.

Expected Behavior

At any valid snapshot, selecting this primary/UNIQUE key should expose one logical row, not multiple copies. Once all successful commits are visible, the row should be exactly (1,10,21) and the table should still contain 100,000 rows.

The current fixture expects v=21 immediately after all worker acknowledgements. However, this cluster enables sacrificing freshness and disables CN-wide commit-timestamp consistency. The exact-version transaction-client contract explicitly allows reduced cross-session freshness. Therefore v=16 alone is not proof that five updates were lost; a follow-up synchronized read is required for that claim. Freshness does not make three or eighteen duplicate visible rows for a unique key a valid result.

Steps to Reproduce

Use the pinned existing MOTR case above against a disposable cluster matching the incident configuration. This is the observed failing nightly procedure, not a claim of deterministic isolated reproduction. Preserve the existing plain/KEY/RANGE controls and query ordering when replaying.

The essential KEY-table portion is:

CREATE TABLE odku_key (
    id INT PRIMARY KEY,
    uk INT UNIQUE,
    v INT
) PARTITION BY KEY(id) PARTITIONS 8;

INSERT INTO odku_key
SELECT result, result * 10, 0 FROM generate_series(1,100000) g;

-- EXPLAIN ANALYZE executes the statement and establishes v=1.
EXPLAIN ANALYZE
INSERT INTO odku_key VALUES (100099,10,999)
ON DUPLICATE KEY UPDATE v=v+1;

SELECT id,uk,v FROM odku_key WHERE uk=10;
-- One row: (1,10,1).

Then execute this statement 20 times using independent autocommit connections, at most five concurrent workers, and wait for every operation to succeed:

INSERT INTO odku_key VALUES (100099,10,999)
ON DUPLICATE KEY UPDATE v=v+1;

On the original observer connection, the existing case then executes:

SELECT id,uk,v FROM odku_key WHERE uk=10;
-- Incident: 18 rows; the first three are (1,10,16).

INSERT INTO odku_key VALUES (100099,10,999)
ON DUPLICATE KEY UPDATE uk=11,v=999;
-- Expected unsupported UNIQUE-key-member update rejection; rejected here.

SELECT id,uk,v FROM odku_key WHERE id=1;
-- Incident: three rows, all (1,10,16).

Additional information

Proven scope and unproven claims

Confirmed: the nightly SQL result contains duplicate visible keys after successful concurrent ODKUs. This is a correctness defect worth tracking independently of #29197's scan-performance fix.

Not established:

• Whether duplicate rows were committed to persistent storage, introduced by CN/logtail visibility, or multiplied by the query/index execution path. Neither __mo_rowid nor the failing SELECT plans were captured.
• Whether increments were actually lost. There is no synchronized follow-up read after the immediate failed checks.
• The table's final total row count: total=not_checked is intentional short-circuit behavior once the returned tuples already mismatch. expected_total=100000 is not an observed count.
• Whether all 18 UNIQUE-query rows equal (1,10,16); only the first three were preserved.
• Whether the rejected UNIQUE-key update caused an additional atomicity violation. The PK read follows that statement, but the result was already invalid beforehand. Treat the two failed assertions as one incident, not two independently proven bugs.
• Whether current Main HEAD, 4.2, or another table shape is affected. Only the tested Main SHA and KEY-table concurrent path are established here.
• The introducing PR, or a new regression between adjacent commits. This investigation did not run a new isolated reproduction or counterfactual build.

Relationship to prior findings

• [Bug]: ODKU with UNIQUE keys performs full hidden-index validation scan after selective target lookup #29197: original performance issue is closed; all its selective-scan controls pass here. Its Oct 12 follow-up records two earlier nightly assertion failures without actual rows, and five passing isolated single-CN repetitions on the preceding incident image. That was insufficient to classify the failure. The new bounded row diagnostics supply the missing correctness evidence; the previous isolated passes do not invalidate the nightly result.
• [Bug]: Optimistic RC concurrent UPDATE exposes duplicate primary-key rows (test-only mode) #29401: duplicate visible PKs were reported in explicitly configured, retired optimistic-RC mode. This incident has a different configured transaction-mode context and workload; a shared cause has not been established.
• [Bug]: REPLACE/UPDATE can commit duplicate primary-key rows on composite-PK table with non-unique secondary indexes #24975: duplicate PKs following REPLACE/UPDATE with composite PK and non-unique secondary indexes. Similar integrity symptoms are not proof of a shared mechanism for this integer-PK/UNIQUE/KEY-partitioned ODKU case.
• [Bug]: ODKU UPDATE expressions evaluate bare columns against the wrong (new) row values #29200: old/new input expression binding error. The serial control here correctly changes v=0 to v=1 rather than using incoming v=999; its issue alone does not explain this concurrent duplicate-row observation.

Narrow next investigation

1. Replay the pinned case/configuration and preserve all returned rows, __mo_rowid, SELECT plans, actual CN/session IDs, and per-operation success/transaction IDs on failure. Do not retry acknowledged writes.
2. Capture UNIQUE, PK and full-scan projections in one controlled read snapshot; compare their row identities and multiplicities. Do not use an optimized COUNT(*) alone as the uniqueness oracle.
3. Preserve immediate reads first, then perform a separate follow-up read after the workers' commits are known to be visible. This distinguishes reduced freshness from persistent wrong results without erasing the original observation.
4. Trace conflict-key/physical-partition locking, snapshot refresh or RC retry, update delete/insert effects, and base/hidden-index logtail visibility using the same operation IDs. These are investigation candidates, not a proven root cause.
5. Only after reproduction distinguish a recent code regression from an older timing-dependent defect, and select the smallest fix supported by that evidence.