---
title: "Mitigating Deadlocks in high concurrency environments"
description: "MySQL requires locks before updating existing records, and concurrent upserts that mix new and existing rows can deadlock when gap locks overlap, even when processes target different records. The post describes ETL jobs partitioned by account whose updates nevertheless contend because sequential primary-key ordering places records in shared index gaps. It proposes replacing the existing primary key with a composite key of account and id, aligning InnoDB's physical row ordering with the processes' update partitions; gap locks remain necessary, but are largely compartmentalized within each process. An additional unique index on id preserves standalone record identification and compatibility with existing joins, while application and ETL logic remain unchanged. The reported outcome was removal of artificial delays and significantly higher import throughput through reduced job wait time."
---

# Mitigating Deadlocks in high concurrency environments

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · Aug 14, 2024

**Type:** Problem & solution

## Summary

MySQL requires locks before updating existing records, and concurrent upserts that mix new and existing rows can deadlock when gap locks overlap, even when processes target different records. The post describes ETL jobs partitioned by account whose updates nevertheless contend because sequential primary-key ordering places records in shared index gaps. It proposes replacing the existing primary key with a composite key of account and id, aligning InnoDB's physical row ordering with the processes' update partitions; gap locks remain necessary, but are largely compartmentalized within each process. An additional unique index on id preserves standalone record identification and compatibility with existing joins, while application and ETL logic remain unchanged. The reported outcome was removal of artificial delays and significantly higher import throughput through reduced job wait time.

## Context

Concurrent MySQL upserts of new and existing records can deadlock because gap locks may overlap in the index, including when separate processes update different batches. The described ETL processes operate per account, but sequential primary-key storage can still cause their lock requirements to intersect and significantly affect performance.

## Approach / What changed

Use a composite primary key containing account and id so InnoDB stores records in the same partitioning pattern used by the ETL processes. Keep id as a separate unique index to preserve standalone record identification and compatibility with existing joins, without changing application or ETL logic.

## Takeaways

- Gap locks can overlap across concurrent MySQL upserts even when processes target different records, because locks cover the updated record and the preceding index position.
- A composite primary key based on account and id aligns InnoDB's physical storage with account-partitioned ETL updates, reducing overlap between required gap locks.
- A separate unique index on id preserves the ability to identify records by their original identifier and supports existing joins without application-level logic changes.

**Tags:** [MySQL](https://yomu.fyi/topic/mysql), [Performance](https://yomu.fyi/topic/performance), [Scalability](https://yomu.fyi/topic/scalability)

- Source: [Shopify](https://shopify.engineering/mitigating-deadlocks-in-high-concurrency-environments)
- Source URL: https://shopify.engineering/mitigating-deadlocks-in-high-concurrency-environments
- Ingested by Yomu: 2026-08-30T13:20:24.528Z

[Read original post](https://shopify.engineering/mitigating-deadlocks-in-high-concurrency-environments)