Replay the Feed, Never a Duplicate: Idempotent Upserts in MySQL

I run The DJ Calendar, a site that tracks tour dates for the world's biggest DJs. Every show on it arrives through feeds from Bandsintown and Ticketmaster, and those feeds arrive again and again: on a schedule, after a retry, sometimes two runs at once. That gives the database one job that's easy to say and easy to get wrong: the same show must always land as the same row, no matter how many times it's ingested or which process gets there first. The obvious approach, and the race inside it The first version most of us write looks like this: $exists = $db->prepare('SELECT id FROM events WHERE artist_id = ? AND venue_id = ? AND event_date = ?'); $exists->execute([$artistId, $venueId, $eventDate]); if (!$exists->fetch()) { $db->prepare('INSERT INTO events (artist_id, venue_id, event_date) VALUES (?, ?, ?)') ->execute([$artistId, $venueId, $eventDate]); } Enter fullscreen mode Exit fullscreen mode It works perfectly in testing, because in testing only one process runs at a time. In production, two ingestion runs overlap. Worker A runs its SELECT and sees nothing. Worker B runs its SELECT a moment later and also sees nothing, because A hasn't inserted yet. Both insert. Every fan following that artist now sees the show twice. The bug lives in the gap between the read and the write. You can't close that gap from application code without locks, and locks bring their own problems. Let the database decide The fix has two parts. First, tell the database what "the same show" means. Feeds don't give you stable IDs you can trust across sources, but they do give you an artist, a venue and a start time. That combination is the show's natural key, so make it a constraint: CREATE TABLE events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, artist_id INT UNSIGNED NOT NULL, venue_id INT UNSIGNED NOT NULL, event_date DATETIME NOT NULL, -- always UTC ticket_url VARCHAR(500) DEFAULT NULL, is_sold_out TINYINT(1) NOT NULL DEFAULT 0, UNIQUE KEY uq_events_natural (artist_id, venue_id, event_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Enter fullscreen mode Exit fullscreen mode Now duplicates aren't just discouraged. They're impossible. And because the rule lives in the database rather than in application memory, it survives crashes, restarts and every future script that touches the table. Second, write in one atomic statement. Instead of asking and then acting, do both at once: INSERT INTO events (artist_id, venue_id, event_date, ticket_url, is_sold_out) VALUES (:artist_id, :venue_id, :event_date, :ticket_url, :is_sold_out) ON DUPLICATE KEY UPDATE ticket_url = VALUES(ticket_url), is_sold_out = VALUES(is_sold_out); Enter fullscreen mode Exit fullscreen mode If the show is new, it's inserted. If it already exists, the existing row is updated with the latest ticket link and sold-out status. There's no gap between the check and the write, so there's nothing for a second worker to slip through. Run the same feed once or a hundred times and you get the same table. Knowing what actually happened MySQL reports an upsert's outcome through the affected-row count: 1 for an insert, 2 for an update, and 0 when the row already matched. That makes ingestion logs genuinely useful: $stmt->execute($params); switch ($stmt->rowCount()) { case 1: return 'inserted'; case 2: return 'updated'; default: return 'unchanged'; } Enter fullscreen mode Exit fullscreen mode A healthy re-run of an unchanged feed reports almost everything as unchanged. If a run suddenly reports hundreds of inserts, something upstream changed, and you find out from a log line instead of from users. The takeaway When correctness depends on "this should only exist once," don't enforce it with a check in application code. Encode the rule as a constraint and write with a statement that can't race. Your ingestion becomes safe to retry, safe to run in parallel, and safe to replay after a crash. The full schema, with every design decision documented inline, plus a live demo where you can watch two workers race in both modes, is on the project page.