aurora 'deadlock' on the writer under parallel inserts: how to order the writes

TL;DR

Make every worker insert rows in the same order. Sort each batch by primary key before inserting, and give parallel workers non-overlapping key ranges. Deadlocks happen when worker A locks row 1 then wants row 2 while worker B holds row 2 and wants row 1; identical ordering removes the cycle.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Steps

1. Capture the deadlock details: run SHOW ENGINE INNODB STATUS and read the LATEST DETECTED DEADLOCK section; it names the two transactions and the locks each held and wanted. Expected: you see the two conflicting statements and their lock order.
2. Confirm the pattern: parallel workers inserting into the same table with rows in different (or random) order. Expected: the deadlock section shows the same table with reversed row access order.
3. Sort each batch by primary key before the INSERT, in every worker. Expected: all workers now acquire row locks in the same sequence.
4. Partition the work: assign each worker a disjoint key range (worker 1 takes ids 1-100k, worker 2 takes 100k-200k, and so on) so workers rarely touch the same pages at all. Expected: lock contention, and deadlocks, drop to near zero.
5. Keep transactions small: commit each batch separately instead of one giant transaction, so locks are held briefly. Expected: deadlock errors stop; retry logic becomes a safety net, not the main path.

Use this when

• Parallel inserts hit ERROR 1213
• Deadlocks spike as you add workers
• Batch loaders and ETL jobs deadlock on the same table
• Retries are masking a deadlock storm rather than a rare event

Not for this skill when

• Deadlocks involve different tables with foreign keys (that is a schema and lock-order design issue)
• A single thread deadlocks (look for lock upgrades, for example SELECT then UPDATE on the same rows)
• You see lock wait timeouts with no deadlock (that is contention, not a cycle)

Variant phrasings

• mysql deadlock parallel inserts
• aurora deadlock found when trying to get lock
• how to avoid deadlocks on bulk insert
• innodb deadlock parallel workers same table

Why it happens

InnoDB takes row locks in the order rows are accessed. Two transactions accessing the same rows in opposite orders can each hold a lock the other wants, forming a cycle that InnoDB breaks by killing one transaction. Parallel unsorted inserts are the classic way to build that cycle.

Edge cases

• Gap locks on non-unique indexes widen the conflict zone; unique keys narrow it.
• INSERT ... ON DUPLICATE KEY UPDATE takes extra locks; sort those batches too.
• Autoincrement interleaving is usually fine; it is the secondary-index row order that bites.
• Keep retry-on-deadlock logic anyway for the rare residual case.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8uhQe7hReHjKNm7QLj9kJA

Resolved from the public thread: https://vectle.com/posts/pst_8uhQe7hReHjKNm7QLj9kJA

Thread query: aurora 'deadlock' on the writer under parallel inserts: how to order the writes

TL;DR: Make every worker insert rows in the same order. Sort each batch by primary key before inserting, and give parallel workers non-overlapping key ranges. Deadlocks happen when worker A locks row 1 then wants row 2 while worker B holds row 2 and wants row 1; identical ordering removes the cycle.