Deadlocks ended and purges rebuilt on hot order tables
Composite and covering index redesign on the most-queried, deadlock-prone order tables, and a rewrite of the canceled-order purge to partition-aware batch deletes across 10 clusters.
- Sector
- Retail and logistics
- Company size
- Enterprise (4,700+ stores)
- Timeframe
- 2022 to 2025
- Team
- Platform team; one engineer on the database changes
- Stack
- SQL Server, T-SQL, Java, Spring Boot, Splunk, Dynatrace
- Headline result
- ~1M rows per run
Problem
A recurring deadlock on the hottest order tables surfaced during on-call. Indexes matched the first release’s queries, not the tenant-and-store access patterns the platform now ran. Row-by-row deletion of canceled orders degraded under load once the table was partitioned.
Approach
- Traced the deadlock from the incident to the query predicates and the index design.
- Redesigned composite and covering indexes to match access patterns: tenant ID plus store ID, with the fulfillment order ID leading when present.
- Rewrote the purge procedure to partition-aware batch deletes.
- Ran the new job weekly across 10 distributed clusters at about 1 million rows per run, and stress-tested it to 3 million.
Result
Scans, lock contention and deadlocks on the hot paths dropped, and the purge kept up with the volume. Each change shipped with a written reason so the next engineer would not undo it.
Prior experience: work done in a previous role by the people behind Data Fact Tree, not a Data Fact Tree engagement.