Check-then-insert races produce duplicate rows — and "keep the lowest Id" dedup deletes the wrong one

A SharePoint list enforces uniqueness only where you switch it on: one indexed column set to Enforce unique values, never a combination of columns. Any "register once" pattern built as GET-to-check → POST-to-create is a classic time-of-check-to-time-of-use (TOCTOU) race, and the obvious cleanup makes it worse.

Symptom

A list that should hold one row per logical entity (an app registry, a per-user preference, a "seen" marker) shows two rows with identical business keys, created close together (76 seconds apart in the case behind this article). Often they differ only in some secondary field — a version string, a timestamp — which is the tell that two writers ran, or that two builds registered the same entity.

Cause

// two callers run this near-simultaneously
const existing = await getItems(`$filter=Key eq '${key}'`);   // both see []
if (!existing.length) {
  await createItem({ Key: key, ... });                        // both insert
}

The check and the insert are not atomic, and the REST API has no upsert for list items. Two tabs, two web parts, or a fast reload fire both writers before either commits. A check whose key differs from the stored one only in case or a trailing slash misses an existing row just as surely — and every retry or "did it save?" re-check built the same way adds another duplicate.

The tempting fix that loses data

"On registration, if duplicates exist, keep the oldest (lowest Id) and delete the rest."

Don't. Lowest Id means first created, which is not the same as most correct. A very real ordering:

The newer row carries the older version — e.g. a stale bundle served from cache registered last. "Keep lowest Id" would delete the current registration and keep the stale one. And any operation that deletes rows it didn't create, as a side effect of a routine write, is a data-loss incident waiting for the wrong inputs. The one narrow exception is a seeder removing its own copy of an identical default row it inserted seconds earlier — see seed idempotency.

The fix

1. Never delete on the write path. Make the writer find-or-update, matching the business key case-insensitively and normalized (for a site/URL key: lowercase, strip the trailing slash). Normalization prevents a whole class of "duplicates" that are really just /Site vs /site. It won't retroactively merge rows a race already created, but it stops new ones.

2. Deduplicate on read, not on write. Let the consumer collapse duplicates: group by the normalized key and surface only the best row — newest by a real version comparison or by Modified, never by Id/creation order. The physical duplicates stay in the list, harmless, until a human removes them after eyeballing which is which.

3. Serialize the writer client-side. An in-flight flag ("already registering") plus "only write when something actually changed" stops one component's init + onChange from firing two writes in the same tick. This shrinks the race window that (1) and (2) then cover for.

4. If you must delete a duplicate, choose by version/recency, never by Id.

5. When one column can carry the key, let SharePoint enforce it. Store the normalized key in a single line of text column and set it to Enforce unique values: the column must be indexed, the setting cannot be switched on while duplicates exist, and the comparison ignores case. The second POST then fails instead of creating a twin. It does not cover a key made of two columns unless you store the combined key in one column.

Diagnosis

Read the list live and compare the rows — don't guess the cause:

GET /_api/web/lists/getbytitle('<Registry>')/items?$filter=Key eq '<key>'
    &$select=Id,Key,Version,Created,Modified

Identical secondary keys ⇒ a genuine concurrency race or index-lag double-insert. Different keys that only look the same ⇒ a normalization bug (case, trailing slash, encoding) — fix the matching, not the data.

Notes

• Rule of thumb: writes are idempotent upserts; readers resolve duplicates; deletion is a human decision.
• This is the concurrency sibling of silent fallbacks poisoning destructive writes: there a catch → [] fed a delete; here an eventually-consistent [] feeds a duplicate insert. Both come from trusting a read that isn't the ground truth.