TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
Ten retries must not create ten rows: idempotent SQL writes
Software Engineering · 2026
Software Engineering

Ten retries must not create ten rows: idempotent SQL writes

Solve duplicate records with stable identity, uniqueness constraints, UPSERT behavior and safe retry boundaries.

Yazılımcının GünlüğüPublished: Nov 16, 20263 min read

Duplicates are often an identity problem

A client may time out after the server has committed the record, then retry the same request. Disabling a button helps usability but is not a correctness guarantee. Start by defining the identity of an event: for imported reviews, prefer a stable source identifier; otherwise consider a carefully designed combination of normalized content, author and date. Hashing the text alone can wrongly merge independent comments by different people.

Database constraints form the final boundary

A UNIQUE index or primary key prevents concurrent requests from creating duplicate rows for the same identity. SQLite's INSERT ... ON CONFLICT syntax makes conflict handling explicit. In this example, an existing source event is updated instead of duplicated. The chosen key must genuinely represent one logical event rather than just a convenient field.

CREATE TABLE import_events (
  source_id TEXT PRIMARY KEY,
  status TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
INSERT INTO import_events(source_id, status, updated_at)
VALUES ('src-138', 'processed', '2026-11-16')
ON CONFLICT(source_id) DO UPDATE SET
status=excluded.status, updated_at=excluded.updated_at;

UPSERT does not make every side effect idempotent

Writing one database row does not automatically deduplicate email delivery, R2 uploads or outbound API calls. Side effects require their own operation identifier, a transactional outbox pattern or checks against previously completed work. Operational logs and business records should be linked so that retries do not inflate successful-event counts.

Historical cleanup requires referential safety

Before consolidating old duplicates, produce an audit showing why candidate records represent the same event. Deleting only a parent row may damage linked images, comments and publication state. Use backups, verified matching, atomic updates and post-change checks; keep ambiguous cases out of automatic merges.

0 reads

Comments

0 real comments

No real reader comments yet.

✎ Write a comment

Comments are reviewed before publication.