Client: a Toronto moving and logistics company whose cloud operations platform I have built and run as their technology partner for 15 years
Area: the dispatch board, where a booking is saved and a crew earns the job
Type: debugging and root-cause story
In one paragraph
I was cleaning up the production error log and found two error signatures sitting on the exact same line of code, the insert that records a saved booking. One was a duplicate-key error, the other was a deadlock. They looked like the same problem and they were not. The duplicate came from a dispatcher submitting the same booking twice. The deadlock came from two different dispatchers saving at the same instant and the database rolling one of them back. Both surfaced to the dispatcher as a failed save, and both filled the log. The fix was two small changes at the repository layer, no schema change, no migration.
Why it mattered
This insert is where a job gets committed to a crew. If it fails, the dispatcher did the work and the booking silently did not land, and to them it just looks broken. It was also the single biggest backend error signature in the log, a few hundred occurrences, so it was drowning out real problems. When your error log is mostly noise, you stop reading it, and that is how the actual incidents slip past.
The two root causes, plainly
For the readers who just want to know what was actually wrong. Both are on the same insert, but they are different failures and they need different fixes.
- Duplicate submit (error 1062). The table has a unique key of one booking per job, per crew, per slot. When a dispatcher double-clicks or the request retries, the second insert hits that key and throws. This is not a data problem, it is a race on the same row.
- Deadlock (error 1213). Two different dispatchers saving at the same instant each grab a lock the other one needs. The database detects the cycle and kills one of them so the other can finish. The killed one threw an uncaught error instead of trying again.
The interesting part is that a deadlock is not a bug in the usual sense. The database is supposed to do that. It is a normal outcome under concurrency, and the correct response is to retry, not to treat it as a failure.
Finding them
I was reading the production error log through an internal endpoint, grouping rows by signature to see what actually repeats. Two signatures pointed at the same two controllers on the same insert. At a glance they read as one issue, “the booking save is failing”. But the error codes were different. One said 1062 Duplicate entry. The other said 1213 Deadlock found when trying to get lock.
That is the whole clue. Same insert, two distinct database errors, two distinct causes. If I had fixed only the duplicate, the deadlocks would have kept coming, and if I had wrapped the whole thing in one blunt try-catch I would have hidden both instead of handling either.
Fixing the duplicate without hiding it
The old code was a single line: create the row. On a double submit, the second create threw.
I changed it to insertOrIgnore, which is a single database statement that inserts the row or, if it would violate the unique key, does nothing and reports zero rows affected. One statement, so there is no gap between a check and a write where a race could slip through. If it inserted, I return the new row. If it was ignored, I fetch and return the row that already exists.
There was one more thing to get right. After this insert, the code fires an event that notifies the crew and updates the schedule. On a duplicate I must not fire it again, or the crew gets two notifications for one job. So the returned row carries a flag saying whether it was freshly created or already there, and the service skips the notify event when it was a duplicate. A double submit now looks like success to the dispatcher, commits the booking once, and writes nothing to the error log.
Why the deadlock needed the opposite instinct
The duplicate fix is about not throwing. The deadlock fix is about catching and trying again, which feels wrong until you understand what a deadlock is.
When two transactions each hold a lock the other wants, the database cannot let both proceed, so it rolls one back with a deadlock error. That transaction did nothing wrong. It just lost a coin toss. The database literally expects you to run it again. So I wrapped the insert in a small retry, up to three attempts with a short growing pause between them, and it retries only on a deadlock. Any other error, including the duplicate, is not retried, it surfaces straight away. If a deadlock somehow persists past the last attempt, it still throws, so a real, sustained problem is never swallowed.
The design call I want to be honest about
There was a real choice here, and it is worth spelling out because I nearly reached for the wrong tool. My first instinct on “concurrent writes are colliding” was to reach for a queue or a write-to-cache-first pattern. Both are wrong for this.
Writing to the cache first and the database later does not remove the deadlock, it just delays the database write, and now a booking is not durable until the cache flushes. For a write that commits a job to a crew, that is the wrong trade. A queue would help, by serializing the writes so fewer collide, but that turns a synchronous “your booking was saved” into an asynchronous job with its own worker, ordering, and backlog to babysit. That is the right answer at a much larger scale, not for a handful of deadlocks a month.
The database already has the correct answer built in. A deadlock is designed to be retried. The smallest fix that respects how the tool works beat every clever alternative I considered. I noted the queue idea as the scale-up path for later and shipped the retry.
The result
Both errors are handled at their source. A double submit is a clean no-op that still looks successful and commits the booking once. A deadlock retries and succeeds instead of failing the dispatcher. I verified both against a test database: a repeat submit no longer throws and leaves exactly one row, and the retry logic retries a transient deadlock, gives up cleanly on a sustained one instead of looping forever, and never retries an unrelated error.
One thing I deliberately did not do was change the table or its unique key. The key is correct, it is what stops double bookings in the first place. The fix works with the constraint, not around it. No schema change, no migration, no risk to existing data.
Frequently asked questions
What is the difference between a MySQL 1062 duplicate error and a 1213 deadlock?
A 1062 duplicate-key error means the row you are inserting already exists under a unique key, usually from a double submit or a retry. A 1213 deadlock means two transactions each hold a lock the other needs, so the database rolls one back. They can land on the same line of code and look identical, but the duplicate is a race on one row and the deadlock is a collision between two separate writes. They need opposite fixes.
How do you fix a duplicate-key error on a double submit without hiding real errors?
Use a single insert-or-ignore statement so the second submit is a clean no-op instead of a thrown error, and carry back a flag that says whether the row was freshly created or already existed. Any side effect, like a notification or a reward, fires only on a fresh insert. You never wrap it in a broad catch that would also swallow unrelated failures.
Should you retry a database deadlock?
Yes. A deadlock is a normal outcome under concurrency, not a bug in your code. The database rolls back one transaction on purpose and expects you to run it again. Retry a small number of times with a short growing pause, retry only on the deadlock error code, and let every other error surface immediately. If the deadlock still persists after the last attempt, let it throw, so a real sustained problem is never silently swallowed.
Do you need a queue to handle concurrent writes?
Usually not at small scale. A queue serializes writes so fewer collide, but it turns a synchronous save into an asynchronous job with its own worker, ordering, and backlog to manage. For a handful of deadlocks a month, an insert-or-ignore plus a targeted deadlock retry is smaller, durable, and keeps the save synchronous. A queue is the right answer at much larger volume, not the first reach.
Two error signatures on one line of code were two different problems wearing one coat, a duplicate that needed me to stop throwing and a deadlock that needed me to catch and retry. The trap is treating them as one. The lesson I keep relearning is to read the actual error code, not the symptom, and to use the boring built-in answer before reaching for a clever one.