A Retry Storm, 541,000 Stuck Messages, and Why Batching Was the Answer

Our database was deadlocking, our message queue was punching itself in the face, and the fix wasn't "add more servers" — it was asking the queue to do less work, better.

---

Sunday, 11:18 AM

It was EMI due date — the 5th of the month, the day every lending company's infrastructure gets stress-tested for free. My phone started buzzing with CloudWatch alarms:

• Production MySQL (we call it milkyway) memory below 7%
• Read replica lagging 60–194 seconds behind the primary
• CPU pinned between 80 and 93%

Three different alarms usually means one root cause wearing three costumes. So the first job was finding the costume rack.

Act 1: The one-character bug

AWS Performance Insights is the first place I look when a database misbehaves — it shows you exactly which SQL statement is eating the instance. The answer was almost embarrassing. One statement was 70–80% of the entire database load:

DELETE FROM RepaymentScheduleDelays WHERE EntityReference = ?

A delete that touches at most 26 rows per application. The table has an index on EntityReference. This should take a millisecond. So why was the database reading 2.5 million rows per second?

Running EXPLAIN on the read replica told the story:

How the value was bound	Plan	Rows examined
EntityReference = 12345 (a number)	full table scan	159,611
EntityReference = '12345' (a string)	index lookup	1

EntityReference is a varchar column, but the application code was passing the application id as a JavaScript number. MySQL's type conversion rules say that when you compare a string column with a number, it casts the column to a number — for every single row. A cast on the column means the index is useless, so every delete became a 159k-row table scan. On EMI day, that delete ran 16 times a second.

One pair of quotes. A 160,000× difference.

The fix was wrapping the bind in String() at 21 places (the same bug existed in seven deletes and fourteen read queries). We shipped it the same evening and watched the delete vanish from Performance Insights within minutes. Alarms cleared.

I'll be honest about one mistake here: when a post-deploy replica-lag wave hit (the unblocked pipeline drained the day's backlog at 3× speed), we panicked and reverted. That made it worse — the revert brought back the slow read queries on the exact replica that was trying to catch up. We re-deployed twenty minutes later. Lesson: when a fix unleashes a backlog, the discomfort you're seeing is the cure working, not the disease returning.

Act 2: The queue that was hitting itself

With the fires out, I looked at our RabbitMQ broker and found the actual plot of this story: 541,340 messages sitting in a queue called analytical-event.incoming.queue. Five and a half lakh events, some of them weeks old.

This queue feeds our analytics pipeline. Every time a repayment settles, services publish an event; a consumer picks it up and calls an analytics service that rebuilds reporting tables (payment reconciliation reports, loan summaries, that family). Two things had been quietly going wrong for months.

First, the write pattern. For every single event, the analytics service rebuilt a payment's report rows like this:

SELECT existing rows
DELETE row 1 (one statement)
DELETE row 2 (one statement)
...
INSERT row 1 (one statement)
INSERT row 2 (one statement)
...

No transaction wrapping it. On a quiet day this is just inefficient. On EMI day, with dozens of consumers doing this concurrently on the same tables, those interleaved row locks collided and MySQL started throwing deadlocks — its way of saying "two of your transactions are waiting on each other forever, so I'm shooting one."

Second, the retry behaviour. When processing failed, our consumer nacked the message with zero backoff — RabbitMQ redelivered it instantly. Fail, retry immediately, fail again, retry immediately, then two trips through a dead-letter queue with an 18-second pause, then give up.

Put those together and you get a retry storm: a deadlock kills a message's processing, the message comes back instantly, hits the same contended tables, deadlocks again, comes back again. Every failure added load to the thing that was causing failures. The old consumer was logging 364,000 retry-storm error lines per day. The queue wasn't draining; it was churning.

Buying time: turn the tap off, don't break the pipe

Before building anything, we needed the bleeding to stop — it was business hours and this database serves actual customers.

The nice thing about a durable message queue is that it's a buffer you're allowed to lean on. We set the analytical event consumer count to zero. Just that one queue — the other 50+ consumers in the same service (payment marking, mandate handling, webhooks) kept running untouched.

What that cost us: analytics reports went stale for a few hours. What it bought us: the primary database instantly calmed down, customer-facing queries were unaffected, and every event waited safely in the queue instead of hammering the DB in a loop. Messages in RabbitMQ don't expire by default and don't take resources while they sit — the backlog was a to-do list, not a threat.

That's the mitigation pattern worth remembering: when a background pipeline is hurting a shared database, pause the pipeline, not the business. Reports can be late. Payments can't.

Why batching was the actual fix

Here's the reasoning, because "we batched it" is the what, not the why.

The retry storm had a shape: many small units of work, each paying full price for locks and failures. Every event independently locked rows, independently deadlocked, independently retried. The database wasn't struggling with the total amount of work — it was struggling with the number of separate fights happening over the same rows.

Batching attacks exactly that:

1. Fewer, bigger writes mean fewer lock collisions. Instead of N events each doing their own delete-loop and insert-loop, we collect 25 messages, group them, and write each table once:

-- before: up to 50 statements per payment, no transaction
DELETE FROM PaymentReconcilationReport WHERE Id = ?   -- × N rows
INSERT INTO PaymentReconcilationReport (...) VALUES (...)  -- × N rows

-- after: 2 statements per table for the whole batch, in one transaction
DELETE FROM PaymentReconcilationReport WHERE PaymentId IN (...);
INSERT INTO PaymentReconcilationReport (...) VALUES (row1),(row2),...;

A transaction holding locks for 20 milliseconds, once, can't get into nearly as many fights as fifty autocommit statements taken one at a time.

2. Deduplication is free money. On EMI day, the same payment gets five events in an hour. Since the rebuild reads current state from the source tables anyway, rebuilding once covers all five. Inside each batch we dedupe by payment id and application id — five events, one rebuild, four skipped.

3. Retries move inside the process. We wrapped the new writes in a small retry helper: on a deadlock, wait 50–200ms with jitter and try the transaction again, up to three times. A deadlock now costs a silent in-process retry instead of a full trip back through the queue. The storm's fuel line — failure feeding redelivery feeding failure — is cut.

4. The ack contract keeps it safe. The consumer acknowledges messages only after the whole batch succeeds. If anything fails, all 25 go back to the queue. And because processing is idempotent — already-processed events are detected and skipped — a redelivered batch can't double-write anything. At-least-once delivery plus idempotent handlers is the whole game in queue systems.

One design decision worth defending: we only batched the two event types that made up ~95% of the volume. The other twelve low-volume types ride inside the same batch call but run through the old per-event code, untouched. Changing the behaviour of twelve rarely-fired code paths to save 5% of the volume is how you turn one incident into two.

And the rollback story was a single environment variable: BATCH_SIZE=1 flips the consumer back to the exact legacy code path. No redeploy, no code revert.

Paranoia pays: what we did before touching production

Five and a half lakh messages, zero appetite for losing one. So, in order:

1. Backed up the entire queue — a script that consumes every message without acknowledging, streams them to a file, then disconnects. Unacked messages automatically requeue on disconnect, so the queue is untouched and we hold a 177 MB restore file. (This consume-without-ack trick is criminally underused for queue backups.)
2. Rehearsed on a throwaway broker — spun up a local RabbitMQ container, recreated the exact queue topology, pushed test messages through the full lifecycle: batching, failures, dead-letter cycles, rollback. This rehearsal caught a landmine: our dead-letter queue's retry delay is baked into the queue definition itself, and changing that config would make the broker reject the consumer at startup. We'd have found that in production at 2 AM otherwise.
3. Ran six real batches against production from a laptop before the deploy — 150 live events, zero errors, then verified the actual database rows: correct tables updated, timestamps inside the test window, duplicates skipped.

The rollout, which naturally did not go according to plan

Surprise #1: the scaling knob was dead. We'd set the consumer count to 5 and got... one worker. A guard in my own code registered the batch consumer once and silently ignored the other four invocations. The fix made each invocation register an independent worker — verified from a laptop first: one worker drained 5 messages/minute net, three workers drained 142.

Surprise #2: more workers found a new wall. At 50 workers the backlog finally drained fast (~450/min), but the analytics read replica pinned at 100% CPU and the slowest batches started brushing our HTTP timeout. The useful mental model that fell out: for I/O-bound pipelines, throughput ≈ workers ÷ per-item-latency, and batch size cancels out of the equation. Making batches bigger doesn't make you faster; it just makes failures more expensive. Workers are the lever, and the database is the ceiling.

Surprise #3: the ceiling had a name. At 2:30 AM, with consumers scaled back to zero, the replica was still at 100%. Two findings: first, our Node service has no request cancellation — batches already accepted keep running server-side even after the caller dies (orphaned work). Second, and the real jackpot: the per-payment lookup inside every batch was joining a 1.8-million-row table on a column with no index. Seven to nine seconds per lookup, dozens running concurrently. That one missing index was why batches took ~30 seconds instead of ~5. One ALTER TABLE ... ADD INDEX later, the actual bottleneck of the whole pipeline was gone.

Yes — the incident that started with a missing-index-shaped bug ended with another missing index. Databases have a sense of humour.

Three observability traps that nearly fooled us

• The "redelivered" rate looked like an active failure storm. It wasn't. RabbitMQ permanently flags any message that was ever requeued, and months of the old retry storm had flagged most of the backlog. We were draining scar tissue, not generating new wounds. Broker counters (total acks over time), not rate graphs, settled it.
• Our log platform silently fell 27 minutes behind after a network blip, making a healthy consumer look frozen. When logs and reality disagree, trust the broker's numbers and the database's rows — they can't lag.
• Rolling-average rate graphs lie about pulsed workloads. A consumer that acks 25 messages in a burst every 30 seconds will show 0/s, 0.4/s or 0.8/s depending on when the graph samples. Same healthy behaviour, three different panics.

What I'd tell my past self

1. Type mismatches between code and schema are invisible until they're a full-blown outage. EXPLAIN your hot queries with the actual bind types your driver sends.
2. Retry without backoff isn't resilience, it's a DDoS you run against yourself.
3. A durable queue is a shock absorber. Pausing one consumer is a legitimate, reversible, boring mitigation — use it early.
4. Batching works because it changes the shape of load (fewer lock fights, free dedupe, cheaper retries), not because it does less work.
5. Rehearse destructive-adjacent changes on a throwaway broker. The config landmine you find locally is an incident you don't have.
6. Before scaling workers, find your ceiling. Ours was one unindexed column pretending to be a capacity problem.

The backlog that would have taken 75 days to drain at the old rate cleared in under a day. The database never noticed. And next EMI day, the queue will have a very boring morning — which is the highest compliment infrastructure can get.

---

If you want to go deeper on any of this: how database indexes actually work, RabbitMQ consumer acknowledgements, dead-letter exchanges, InnoDB deadlocks, and AWS RDS read replicas are the five rabbit holes most worth your time.