[Bug]: Concurrent same-key PUT/PATCH upserts can fail or create duplicate logical rows

[aaronburtle]

Title

Concurrent same-key PUT/PATCH upserts can fail or create duplicate logical rows on relational database providers

Description

REST PUT and PATCH operations with a caller-supplied primary key perform an upsert. On SQL Server, PostgreSQL, and Data Warehouse SQL, the current implementation decides between UPDATE and INSERT using multiple database operations without first serializing requests for the same key.

At READ COMMITTED, two concurrent requests targeting the same missing primary key can both observe that the row does not exist and independently choose the insert path.

Depending on the provider:

• SQL Server can return a duplicate-key, conflict, or deadlock error for one request.
• PostgreSQL can return a unique-constraint or serialization-related failure for one request.
• Data Warehouse SQL may create duplicate logical rows because configured DAB key fields are not necessarily backed by an enforced unique constraint.

This is a time-of-check/time-of-use race. The operation is transactional, but the initial existence decision is not protected against another same-key upsert making the result stale before the insert occurs.

Expected behavior

Concurrent upserts targeting the same entity and primary-key value should serialize before checking row existence:

• Exactly one request should insert the missing row and return 201 Created.
• Subsequent concurrent requests should observe the inserted row, update it, and return 200 OK.
• No request should fail with a duplicate-key, deadlock, conflict, or database-operation error.
• Exactly one logical row should remain for the key.
• Existing update/create database-policy behavior must remain unchanged.
• Upserts targeting different keys should remain concurrent where supported.

Scope

Affected relational providers:

• SQL Server
• PostgreSQL
• Data Warehouse SQL / Azure Synapse

MySQL is excluded because its concurrent upsert path is addressed separately in PR #3734. Cosmos DB is also outside this issue's scope.

Implementation considerations

Provider-specific serialization is required:

• SQL Server: acquire and hold an update/key-range lock for the PK existence decision.
• PostgreSQL: acquire a transaction-level advisory lock derived from the source and complete primary key before checking existence.
• Data Warehouse SQL: use a held exclusive source-table lock for insert-capable upserts because configured logical keys may not be physically enforced.

The lock must be held through the complete ambient upsert transaction and released automatically on commit, rollback, cancellation, or exception.

Test coverage

Add concurrent same-key integration coverage for both PUT and PATCH where supported:

• Start several upserts against the same initially-missing key.
• Assert exactly one 201 and all remaining responses are 200.
• Assert no 4xx/5xx database-operation failures.
• Assert exactly one row remains for the logical key.

Add SQL-generation tests for provider-specific locking behavior, including composite primary keys and update-only fallback paths.

Version

all

What database are you using?

Azure SQL

What hosting model are you using?

No response

Which API approach are you accessing DAB through?

No response

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct