Two error signatures sat on the same line of code. They looked like one problem. They were two, with opposite fixes, and if I had trusted the symptom instead of the error code I would have fixed one and left the other running.
This is the whole story, including the fix I nearly shipped and should not have.
What I was looking at
I was reading a production error log, grouping rows by signature to see what actually repeated rather than what happened most recently. One signature dominated everything else, a few hundred occurrences, all pointing at the same insert: the statement that assigns a unit of work to the person who will do it.
That insert matters more than most. If it fails, someone has done the work of scheduling something and the assignment has not landed. To them it just looks broken. It was also drowning out everything else in the log, which is its own problem. When your error log is mostly noise you stop reading it, and that is how the real incidents get past you.
So: one line of code, hundreds of failures, clearly one bug to fix.
That was wrong.
The clue I almost missed
The signatures grouped together because they came from the same line. But the database error codes were different. One was a duplicate-key violation, MySQL error 1062 Duplicate entry. The other was a deadlock, error 1213 Deadlock found when trying to get lock.
Same statement, two distinct database errors, two distinct causes.
The duplicate came from the same person submitting the same record twice, a double click or a retried request, hitting a unique key that exists specifically to prevent a double assignment. That is a race on one row.
The deadlock came from two different people saving at the same instant, each holding a lock the other needed. The database detected the cycle and rolled one of them back so the other could finish. That is a collision between two separate writes.
Both surfaced to the user as a failed save. Both landed on the same line. Everything about the symptom said one problem, and the only thing that said otherwise was a number in the error code.
If I had fixed only the duplicate, the deadlocks would have carried on at the same rate. And if I had done the obvious thing, which I will come to, I would have hidden both while fixing neither.
The fix I nearly shipped
The obvious move when one line throws hundreds of errors is to wrap it in a try and catch, log it, and move on. It makes the log quiet immediately.
It also would have been the worst possible outcome. A broad catch on that statement swallows the duplicate, swallows the deadlock, and swallows every unrelated failure that line will ever produce, including ones that have not happened yet. The log goes quiet because I stopped listening, not because the system stopped failing.
I want to be honest that this was not a theoretical temptation. A quiet log is a deliverable you can show someone. Two targeted fixes to two different root causes take longer and look like less.
Two causes, two opposite fixes
The duplicate needed me to stop throwing.
I replaced the plain insert with the query builder’s insertOrIgnore, a single statement that either inserts the row or, if it would violate the unique key, does nothing and reports that nothing happened. One statement matters here: any version that checks first and then writes leaves a gap between the check and the write where a second request can slip through. If it inserted, I return the new row. If it was ignored, I fetch and return the row that was already there.
There was a second-order problem hiding in that. After the insert, the code fires an event that notifies people and updates downstream state. On a duplicate submit, that must not fire again, or one action produces two notifications. So the returned row carries a flag saying whether it was freshly created, and the calling code only fires the event when it was. A double submit now looks like success, commits once, and writes nothing to the error log.
The deadlock needed the opposite instinct: catch it and try again.
This 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. That transaction did nothing wrong. It lost a coin toss. The database is explicitly telling you to run it again.
So I wrapped the insert in a small retry, three attempts with a short growing pause, retrying only on the deadlock code. Every other error, the duplicate included, surfaces immediately without retrying. And if a deadlock survives the final attempt, it still throws, so a sustained real problem is never quietly absorbed.
Both changes live in the repository layer, the one place that writes this row. No controller changed, no schema changed, and no migration was needed.
Two fixes, opposite in direction, on the same line of code. One says stop treating this as an error. The other says treat this as an error and then do it again anyway.
The design call worth spelling out
My first instinct on “concurrent writes are colliding” was to reach for infrastructure. Put the writes on a queue, or write to a cache first and persist later. Both were wrong, and it is worth saying why, because both sound more senior than what I did.
Writing to the cache first does not remove the deadlock. It delays the database write and leaves the record non-durable until the flush. For a write that commits work to a person, that is a bad trade dressed up as an optimization. The cache belongs in front of reads, and choosing the right caching layer for the right read is a separate discipline from deciding what a write needs to guarantee.
A queue would genuinely help by serializing writes so fewer collide. It also converts a synchronous “your change is saved” into an asynchronous job with a worker, an ordering guarantee, and a backlog to monitor. That is the right answer at a much higher volume. For a handful of deadlocks a month it is a large amount of new machinery to avoid using a feature the database already has.
The database had the correct answer built in. A deadlock is designed to be retried. The smallest fix that respected how the tool already worked beat every more impressive option I considered. I wrote the queue idea down as the path for later and shipped the retry.
I also deliberately did not change the table or its unique key. That key is not the problem, it is the thing preventing the duplicate in the first place. The fix works with the constraint rather than routing around it. No schema change and no migration, which on a live system is a feature and not a limitation.
What I take from it
Read the error code, not the symptom. Two failures that look identical to the user, land on the same line, and group into the same alert can still be two unrelated problems with opposite solutions. The symptom is what the user experiences. The code is what actually happened.
Be suspicious of the fix that makes the log quiet fastest. Silence is not the same as correctness, and a broad catch buys silence at the cost of every future error on that line.
And check whether the tool already solved it before building something. Retrying a deadlock is not clever. It is what the database documentation tells you to do. I nearly skipped it for something more architecturally interesting, and the interesting version would have been worse in every dimension that mattered.
The hardest part of this was not either fix. It was noticing that there were two problems when everything in front of me said there was one.
Questions I get asked about this
What is the difference between a MySQL 1062 duplicate-key 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 retried request. 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 and look identical to the user, 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 rather than a thrown error, and carry back a flag saying whether the row was freshly created. Any side effect, such as a notification, fires only on a fresh insert. Never wrap the statement in a broad catch, which 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 one transaction back 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 it survives the last attempt, let it throw.
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 a worker, ordering and a backlog to monitor. For a handful of deadlocks a month, an insert-or-ignore plus a targeted retry is smaller, keeps the write durable and keeps the save synchronous. A queue is the right answer at much larger volume.