Ruankao E-Commerce Designer (Intermediate): Order Data Growth, Replica Lag and Allocation Deadlocks at an Online Grocery

Order Data Growth, Replica Lag and Allocation Deadlocks at an Online Grocery

An online grocery platform runs MySQL 8.0 with a single primary and one read replica. The orders table has 300 million rows and order_items 1.2 billion; peak writes reach 8,000 per second. Reporting queries run on the replica, which lags up to 20 minutes during batch loads. Customers and customer-service agents see stale order status, and a 'where is my order' call costs ¥6 in agent time. Inventory is allocated across six warehouses; at peak, allocation transactions update several warehouse rows in an order determined by the application's map iteration, and deadlocks occur several times per minute. The company plans same-day delivery in three new cities, which will add four warehouses and double order volume. The team has no sharding middleware and one DBA. Management wants a design that avoids a rewrite and keeps customer-facing reads current.

1. Recommend a partitioning and sharding strategy for orders and order_items. State the key choice, why it fits the access patterns, and what it breaks.

Read the worked answer

Access patterns

Customer order history by customer_id, newest first.
Order lookup by order_id for customer service.
Reporting by date, city and warehouse.
Allocation by SKU and warehouse.

Recommendation: partition before sharding. 300 million orders is large but manageable with partitioning and archival. Partition orders by RANGE on created_at monthly and order_items by the same range, keeping 24 months online and archiving older partitions. This makes purge and date-range reporting cheap and avoids a middleware project the team cannot support with one DBA.

If sharding becomes necessary at 2× volume, shard by customer_id (hash into 16 logical shards), because the dominant customer-facing query is 'my orders'. Use a globally unique order_id with a shard prefix or a Snowflake-style ID so customer service can route by order ID. Keep a small mapping table from order ID to shard.

Why it fits: customer reads are single-shard; order lookups are single-shard via the ID prefix; write scaling is linear in shard count. Monthly partitions keep the working set small and make archival a metadata operation.

What it breaks

Cross-customer and warehouse-level aggregation cannot run on the sharded OLTP cluster. Use CDC (for example Debezium) into a columnar warehouse, not the lagging replica.
Cross-shard joins and global unique constraints become expensive; order_items must be co-located with its order shard.
MySQL partitioned tables require the partition key in every unique key, so order_id alone cannot be the primary key when partitioning by date; use a composite key or accept that limitation.
Rebalancing shards adds operational work; plan for logical shards mapped to physical databases so future moves are possible.

2. Customer service and customers see stale order status because reporting and some reads hit a replica lagging 20 minutes. Propose a read strategy that preserves read-after-write without overloading the primary.

Read the worked answer

The problem is that a 20-minute replica lag violates read-after-write, but routing all reads to the primary would overload it.

Options

Session consistency token. After a write, return the GTID or a monotonic version to the client. On the next read, wait until the replica has applied that GTID, or fall back to the primary. Implement with a short-lived cookie/token and a proxy such as ProxySQL.
Bounded primary routing. Route the customer's own order-status reads to the primary for 60 seconds after a write, keyed by order_id, then to the replica. This is simple and covers the 'just placed' case.
Status read model in Redis. Write order status to Redis synchronously on each transition and serve status from Redis with a database fallback. Redis is a read model, not the source of truth.
Semi-synchronous replication for the orders table so at least one replica has the commit before acknowledgement. This reduces lag but adds write latency.

Recommendation: combine 1 and 2 - a read-your-writes token for the buyer session, plus primary routing for customer-service lookups by order ID, plus a Redis status cache invalidated by the order event. Keep analytics on the warehouse via CDC, not on the lagging replica. Add a lag monitor with an alarm at 30 seconds; if lag exceeds the threshold, automatically route status reads to the primary with a circuit breaker and queue the reporting load.

3. Inventory allocation across six warehouses causes deadlocks at peak. Explain the likely cause and propose a concurrency design, including isolation level and retry policy.

Read the worked answer

Likely cause: each allocation transaction locks multiple warehouse rows, and the application iterates a HashMap of warehouse IDs. Transaction A locks warehouse 3 then 5, while transaction B locks 5 then 3 - a classic lock-order inversion. Deadlocks rise with concurrency and with longer transactions.

Concurrency design

Deterministic lock ordering: sort warehouse IDs ascending before locking or updating.
Keep transactions short: no remote calls, no user interaction inside the transaction.
Single atomic update per warehouse: UPDATE stock SET available = available - ? WHERE warehouse_id = ? AND sku = ? AND available >= ?; check affected rows rather than read-then-write.
Isolation level: consider READ COMMITTED to reduce gap locking; MySQL's default REPEATABLE READ takes more locks for range scans. Verify with a workload test, because the application may rely on repeatable reads elsewhere.
Partition by SKU: a per-SKU allocation queue or hash partition serializes competing orders for the same SKU without cross-SKU contention.
Idempotent reservation: a unique reservation key per order line prevents double allocation on retry.
Retry policy: catch error 1213 (deadlock) and 1205 (lock wait timeout), retry up to three times with exponential backoff and jitter; after that, fail the allocation and release partial reservations.

Metrics: deadlock rate per 10,000 orders, allocation P99 latency, oversell and undersell counts, retry success rate. For same-day delivery, pre-reserve stock by warehouse and city to avoid last-second cross-warehouse splitting.