Concept questionHard~9 min

Interview question

How would you design an insert-or-update operation that is safe under concurrent requests?

Design an idempotent upsert without race conditions

Builds a race-safe upsert around database-enforced identity, explicit conflict semantics, retries, ordering, and side-effect boundaries.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I first define the logical identity, such as (TenantId, ExternalOrderId), and enforce it with a unique constraint. Then I use a database-supported atomic upsert or attempt the insert and handle a duplicate-key result by loading or updating the existing row. I do not rely on AnyAsync followed by Add, because two requests can pass that check concurrently. The repeated operation must resolve to one logical record.

Full answer below

Practice focus

Upsert semantics, unique constraints, transaction boundaries, external ids, retries, isolation, and idempotency.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
An upsert is only race-safe when the database enforces the identity being matched. An application-level check followed by an insert leaves a window where two callers can both decide the row is missing.
I first define the logical identity, such as (TenantId, ExternalOrderId), and enforce it with a unique constraint. Then I use a database-supported atomic upsert or attempt the insert and handle a duplicate-key result by loading or updating the existing row. I do not rely on AnyAsync followed by Add, because two requests can pass that check concurrently. The repeated operation must resolve to one logical record.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
I also define what a repeated key means. If the same idempotency key arrives with the same payload, I can return the stored result. If it arrives with different business data, silently overwriting may be corruption, so I reject it or apply an explicit version rule. Webhooks may need both a unique event id and source sequence because deduplication does not solve out-of-order delivery. Any outbox record representing the accepted change is written in the same transaction.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I choose the SQL pattern for the actual database and contention profile rather than assuming one generic MERGE statement is universally safe. On SQL Server, separate insert and update logic can be clearer under heavy concurrency, while the unique constraint remains the final arbiter. I distinguish expected duplicate-key races from unrelated constraint failures and retry only a repeat-safe unit. Concurrency tests release many requests against the same key and assert one row, one logical outcome, and no duplicated side effect. Telemetry tracks conflicts, payload mismatches, retries, and hot keys. If the source operation can be replayed after an uncertain response, I persist the idempotency key and prior response long enough to cover that retry window.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Checking for existence and then inserting without a unique constraint.
Treating every repeated key as permission to overwrite different data.
Deduplicating event ids while ignoring source ordering.
Retrying an upsert that also repeats email, payment, or message side effects.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot name the unique key for idempotency.
Has no concurrency story.
Does not distinguish duplicate request from changed data.
No retry or conflict behavior.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

What key makes the operation idempotent?
How would you handle two different payloads for the same external id?
How would this interact with an outbox message?

Continue practice

Move to the linked follow-up, next path step, prerequisite, or deeper variant.

1 linked

Try a variant

Answer a similar question in a different context.

Idempotent POST

Opening drill

Concept questionHard~9 min

Interview question

How would you design an insert-or-update operation that is safe under concurrent requests?

Design an idempotent upsert without race conditions

Builds a race-safe upsert around database-enforced identity, explicit conflict semantics, retries, ordering, and side-effect boundaries.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

Full answer below

Practice focus

Upsert semantics, unique constraints, transaction boundaries, external ids, retries, isolation, and idempotency.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
An upsert is only race-safe when the database enforces the identity being matched. An application-level check followed by an insert leaves a window where two callers can both decide the row is missing.
I first define the logical identity, such as (TenantId, ExternalOrderId), and enforce it with a unique constraint. Then I use a database-supported atomic upsert or attempt the insert and handle a duplicate-key result by loading or updating the existing row. I do not rely on AnyAsync followed by Add, because two requests can pass that check concurrently. The repeated operation must resolve to one logical record.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
I also define what a repeated key means. If the same idempotency key arrives with the same payload, I can return the stored result. If it arrives with different business data, silently overwriting may be corruption, so I reject it or apply an explicit version rule. Webhooks may need both a unique event id and source sequence because deduplication does not solve out-of-order delivery. Any outbox record representing the accepted change is written in the same transaction.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I choose the SQL pattern for the actual database and contention profile rather than assuming one generic MERGE statement is universally safe. On SQL Server, separate insert and update logic can be clearer under heavy concurrency, while the unique constraint remains the final arbiter. I distinguish expected duplicate-key races from unrelated constraint failures and retry only a repeat-safe unit. Concurrency tests release many requests against the same key and assert one row, one logical outcome, and no duplicated side effect. Telemetry tracks conflicts, payload mismatches, retries, and hot keys. If the source operation can be replayed after an uncertain response, I persist the idempotency key and prior response long enough to cover that retry window.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Checking for existence and then inserting without a unique constraint.
Treating every repeated key as permission to overwrite different data.
Deduplicating event ids while ignoring source ordering.
Retrying an upsert that also repeats email, payment, or message side effects.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot name the unique key for idempotency.
Has no concurrency story.
Does not distinguish duplicate request from changed data.
No retry or conflict behavior.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

What key makes the operation idempotent?
How would you handle two different payloads for the same external id?
How would this interact with an outbox message?

Continue practice

Move to the linked follow-up, next path step, prerequisite, or deeper variant.

1 linked

Try a variant

Answer a similar question in a different context.

Idempotent POST

How would you design an insert-or-update operation that is safe under concurrent requests?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue practice

Try a variant

How would you design an insert-or-update operation that is safe under concurrent requests?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue practice

Try a variant