Flaky test: test_issue_496_2 fails on MS SQL with PRIMARY KEY violation (non-atomic MERGE upsert)

[vharseko]

Summary

The integration test PluggableBackendImplTestCase.test_issue_496_2 intermittently fails against the MS SQL JDBC backend with a PRIMARY KEY violation. This is a non-deterministic (flaky) failure caused by a concurrency race in the SQL Server MERGE-based upsert, not by any specific change under test.

Observed failure

Seen on CI (PR #663, build-maven (ubuntu-latest, 11)), run 29592627663 / job 87925208615:

MsSqlTestCase > PluggableBackendImplTestCase.test_issue_496_2:1282
» com.microsoft.sqlserver.jdbc.SQLServerException:
   Violation of PRIMARY KEY constraint 'PK__opendj_8__...'.
   Cannot insert duplicate key in object 'dbo.opendj_...'.
   The duplicate key value is (8335fa56...).

Tests run: 30800, Failures: 1, Errors: 0, Skipped: 0

The same commit passed on Ubuntu with Java 17, 21, 25 and 26 (which run the same testcontainers-based MS SQL tests). Only the Java 11 job hit the race in this run — a classic timing-dependent flaky failure. macOS/Windows jobs do not run these tests (no Docker).

Root cause

test_issue_496_2 is a concurrency stress test: 8 threads each perform 1023 txn.update() calls on the same key in the same tree (opendj-server-legacy/src/test/java/org/opends/server/backends/pluggable/PluggableBackendImplTestCase.java:1237-1265).

For the JDBC backend, update(TreeName, key, UpdateFunction) is implemented as read → compute → put(), and put() for MS SQL performs the upsert via MERGE (opendj-server-legacy/src/main/java/org/opends/server/backends/jdbc/Storage.java:393):

merge into <table> old using (select ? h,? k,? v) new
  on (old.h=new.h and old.k=new.k)
  WHEN MATCHED THEN UPDATE SET old.v=new.v
  WHEN NOT MATCHED THEN INSERT (h,k,v) VALUES (new.h,new.k,new.v);

On SQL Server, MERGE is not atomic under concurrency by default: two concurrent statements can both evaluate NOT MATCHED and both attempt the INSERT of the same key, producing a duplicate PRIMARY KEY error. This is documented SQL Server behavior. See Microsoft's guidance and the well-known write-up "UPSERT Race Condition With MERGE".

Suggested fix

Add the WITH (HOLDLOCK) (SERIALIZABLE range-lock) table hint to the SQL Server MERGE target so the upsert is atomic:

merge into <table> WITH (HOLDLOCK) old using (...) ...

This eliminates the duplicate-key race under concurrency, which is exactly what test_issue_496_2 is designed to catch.

Workaround

Re-running the failed job typically makes it pass.