Mitigating Deadlocks in high concurrency environments - Shopify

This post details the implementation of composite primary keys in MySQL to mitigate deadlocks during concurrent up-insert operations. By including the `account` column in the composite primary key alongside the existing `id`, data is physically ordered on disk by account. This compartmentalizes gap locks within each process instance, preventing overlaps and dramatically reducing deadlock occurrences without requiring application-level logic changes. A unique index on `id` was added for backward compatibility.