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.
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.
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.
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.
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.
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.
Editor note: Security and failure modes require evidence beyond a launch post. We will revisit the assessment as the source is updated.
Features are only part of the story: security and privacy deserve their own review.
I would compare the announcement against independent benchmarks before making a decision.
✎ Write a comment