Why Parallel Updates Deadlock in MySQL
MySQL relies on locks to preserve data integrity whenever existing records are modified. When several processes update rows in the same table concurrently, deadlocks may surface—even when each process targets a distinct set of records. The underlying cause is often less about the rows themselves and more about how the database organizes them on disk and the lock ranges it must acquire.
Consider an "up-insert" routine that processes a batch mixing new rows with rows that already exist. MySQL first attempts to insert everything. When it hits a collision on a unique constraint, it recognizes the row needs an update instead. To perform that update safely, MySQL requires a gap lock. A gap lock covers not just the target record but also the record immediately preceding it in the index structure, as the database may need to update the index as well.
When multiple processes perform this same operation in parallel, their gap-lock requests can overlap. Sequential primary keys make this worse: data lands on disk in the same order as the key values, so the ranges covered by different processes' gap locks easily intersect. The more processes run concurrently and the more unique-constraint collisions occur, the higher the deadlock probability. These deadlocks can introduce noticeable application slowdowns, even when the participating processes are working on entirely separate batches.
In one example, an ETL pipeline updated a transactions table in parallel, with each job scoped to a single account. Although each process touched only its own set of transaction records, lock requirements still overlapped and triggered deadlocks.
Aligning On-Disk Order With Access Patterns
A key observation from earlier work on composite primary keys for read performance was that how data is physically stored can be reshaped to match how it is written and updated. The same idea can help with deadlocks: gap locks are still required, but it is possible to compartmentalize the data so the locks each process needs stay within its own partition, sharply reducing overlap.
The fix centers on the primary key, which determines the physical ordering of rows in the InnoDB engine. Because the ETL jobs were already segregated by account, that column was added to a new composite primary key alongside the existing id. Since the jobs were already partitioned this way, no changes to application logic were required—as long as the original id could still serve as a unique record identifier.
To preserve that behavior, a separate unique index was added on the id column alone. This keeps existing joins that reference only id fully compatible.
With the composite key in place, the same ETL processes operate unchanged against the table, but the data is physically grouped per account. The required lock sets no longer overlap across processes, eliminating the deadlock condition.
Operational Impact
This approach resolved the deadlock problem at the database layer, requiring zero changes to the application's logic. It also allowed the removal of artificial delays that had been inserted into processes as a workaround for deadlocks. With those waits gone, the throughput of the import jobs improved markedly, primarily due to the elimination of lock-contention wait time.
