Why Sorted Inserts Are the Solution Nobody Talks About
Deadlocks are one of those production problems that appear suddenly, trigger urgent alerts, and get “fixed” with a retry loop under pressure. The application retries, the errors disappear, and everyone moves on.
The problem is that retries treat the symptom, not the cause. The deadlock rate stays the same, CPU usage goes up, latency increases, and the system quietly degrades under load.
In many bulk write workloads, the real fix is simpler and more mechanical than most engineers expect. Sort your inserts.
What a deadlock actually is
A deadlock occurs when two transactions are each holding a lock that the other transaction needs next. Neither can proceed. The database detects the cycle, aborts one transaction, and forces a rollback. Your application sees an error and, if it is written defensively, retries.
Transaction A holds lock on Row X and wants Row Y
Transaction B holds lock on Row Y and wants Row X
→ Neither transaction can proceed
→ Database detects a cycle and kills one
Deadlocks are not random. They are a deterministic consequence of lock acquisition order.
Why retries are not a real solution
Retry logic is essential. You should always retry transactions that fail due to deadlocks. But retries do nothing to reduce the probability of deadlocks. They simply push the cost into CPU consumption, lock churn, and tail latency.
If two concurrent operations deadlock once, they can deadlock repeatedly under contention. Retries hide the failure, but they do not fix the underlying access pattern that causes it.
The root cause in most bulk write scenarios
In bulk inserts or bulk updates, deadlocks almost always come from inconsistent lock ordering.
Consider two background jobs inserting rows into the same table:
Both jobs insert large batches concurrently. Each transaction acquires row or index locks in a different order. Eventually, each job holds a lock the other wants next. A cycle forms. Deadlock.
The database is doing exactly what it is designed to do. The design flaw is in how the application orders work.
The fix: consistent lock acquisition order
If all transactions acquire locks in the same order, deadlocks become impossible.
For bulk inserts or updates, the simplest way to guarantee this is to sort the batch by the primary key or by the leading columns of the clustered index before writing.
If your table’s primary key is customer_id, every batch should be sorted by customer_id before insertion.
records.sort(Comparator.comparing(Record::getCustomerId));
batchInsert(records);
Now Job A and Job B request locks in the exact same order. One may wait for the other, but it will never hold a lock that blocks the next lock the other transaction needs. Waiting can occur. Cycles cannot.
This does not eliminate contention. It eliminates deadlocks.
Why this works at the database level
Most relational databases acquire locks in index order. When rows are processed in the same order as the index, transactions request locks monotonically. They never “go backwards.”
Deadlocks require at least one transaction to lock a later row while waiting on an earlier one that another transaction holds. Sorted inserts prevent that pattern entirely.
This approach works just as well for bulk updates and deletes, as long as the operation order matches the index order used by the database.
What about gap locks and isolation levels
In isolation levels like REPEATABLE READ, the database may acquire gap locks as part of range scans to prevent phantom rows. These gap locks can interact with insert intention locks and create deadlocks even when inserts are sorted.
When deadlocks persist despite sorted inserts, the usual fixes are:
Sorted inserts are not magic. They solve lock order problems, not every concurrency issue.
The retry loop still matters
Even with perfect ordering, deadlocks can still happen in rare edge cases involving metadata locks, foreign keys, or complex multi‑table updates.
Retry logic is still required. The difference is that retries become a safety net rather than your primary defense. Once lock acquisition order is consistent, deadlock frequency drops dramatically, and retry overhead becomes negligible instead of constant.
Deadlocks feel mysterious when treated as bugs. They become predictable once you recognize them as ordering problems.