TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
Prevent duplicate events in SQL: idempotency, race conditions and safe retries
Software Engineering · 2026
Software Engineering

Prevent duplicate events in SQL: idempotency, race conditions and safe retries

Why does a repeated API event create duplicate rows? Use stable keys, UPSERT, transactions and outbox patterns to keep data consistent.

Yazılımcının GünlüğüPublished: Nov 17, 20265 min read
JOIN THE DISCUSSION
✎ Write a comment
Reading progress0%

Why one event can arrive twice

A client sends an API request, the server completes its work, but the acknowledgement is lost. The client sees a timeout and retries. Payments, comments, reservations and notifications can then produce duplicate effects even though the user intended only one action. A read-before-insert check in application code is not enough: two workers can both observe a missing row and then insert concurrently. This race condition may be rare in a local test yet common under load. Define the logical identity of an operation in the data model instead of trying to prevent every retry. Robust systems make retries safe rather than treating them as exceptional.

Stable keys and database constraints

An idempotency key identifies repeated attempts at the same logical operation. It must be generated once and reused across retries. A UNIQUE constraint enforces this rule under concurrent SQL writes, while the key's scope should distinguish two genuinely separate orders from two copies of one request. For imported news or comments, source identifiers, canonical URLs and carefully normalized record fingerprints may help. Avoid deduplicating purely by title or date, because different valid records can share those properties. Model-level uniqueness is more reliable than hoping each worker performs an accurate preflight check. Always make the desired domain identity explicit.

Database access flowConceptual sketch · summary of the article's technical flow

UPSERT, transactions and result replay

UPSERT can make first-time and repeated writes follow one predictable rule, but blindly updating on conflict can overwrite previously correct data. Some operations should return the original result instead of executing again. Store the operation key, status and a compact version of its response when necessary. Use a transaction for multiple related tables within the same database. External APIs, object storage and email services are not automatically covered by that transaction. An outbox record can separate committing the intended action from delivering its external effect. Reliability comes from modeling the entire process, not from one clever SQL statement.

Conceptual workflow; adapt it to the actual application requirements.

Resuming interrupted workflows

When work stops halfway through, identify which steps are durably complete. Was the database record created while its media mapping failed? Did a notification fail after its outbox entry was saved? Resume from the missing step instead of replaying everything indiscriminately. Check whether downstream APIs support their own idempotency keys. Bounded exponential backoff reduces pressure while transient dependencies recover. Repeating a broken operation forever merely increases noise and quota usage. Record operation identity, previous state, intended new state and verification so a failure can be investigated without guesswork.

Tests that reveal race conditions

Send a hundred concurrent requests with the same event key; the expected outcome is one logical record. Then send two distinct valid operations for the same user and confirm both survive. Interrupt the connection after commit but before acknowledgement to test retry behavior. Check that conflict handling preserves immutable fields and that failed transactions do not leave half-written rows. If an outbox drives external work, verify its status transitions across a restart. These tests provide stronger evidence than several happy-path manual runs. The goal is zero unintended duplication without collapsing genuinely distinct events or inflating operational cost.

SQL structure and operation results

Imagine a table storing a unique idempotency identifier for each API operation. A UNIQUE constraint protects the key, while status tracks processing, success and failure. A retry using the same identifier can return the saved result when processing has completed or follow an explicit conflict policy while it is still underway. Different operation identifiers may nevertheless race to modify the same resource, so target versions and transactional constraints remain necessary. Database rules and application checks complement one another. Recording operation state separately from the target entity makes responses more predictable and simplifies recovery after network errors.

The dilemma of external delivery

Consider an application that writes an order and then invokes a notification or payment API. The database write can succeed while the external call appears to fail, possibly after the remote system already acted. Rolling everything back indiscriminately can make the situation worse. An outbox row representing pending delivery makes the sequence auditable and restartable. Yet the outbox alone does not guarantee exactly-once delivery. Propagate a stable operation identifier to the recipient where supported and define duplicate handling. Track three separate outcomes: newly committed events, confirmed external deliveries and retries safely suppressed. These metrics make both capacity planning and incident investigation more meaningful.

Be the first reader ✎ Write a comment

Comments

No comments yet. Be the first to contribute.

What might reader discussion look like?Illustrative

These names, comments and editor replies are illustrative, not real visitor posts. Interactions are not saved. Use the real form below to contribute.

Sam Carter

Reading “SQL ile mükerrer olayları önlemek: idempotency, yarış koşulları ve güvenli yeniden deneme” raised this question for me: long-term maintenance cost is important.

Sample editor reply

Editor note: Security and failure modes require evidence beyond a launch post. We will revisit the assessment as the source is updated.

Chris Green

Features are only part of the story: security and privacy deserve their own review.

Jess Taylor

I would compare the announcement against independent benchmarks before making a decision.

✎ Write a comment

Your first and last name will appear publicly alongside your comment.