How We Tamed Database Lock Contention in High‑Volume Inventory Allocation

Background

In the WMS system, inventory is a core sub‑service providing inbound, outbound and stock services. The inventory‑location service is complex, handling high‑concurrency order placement where hot‑row updates in MySQL cause severe row‑lock contention.

Problem & Risks

During large‑scale promotions, many hot‑SKU orders target the same inventory row, leading to long lock wait times, slow SQL, connection leaks, memory exhaustion and potential database crashes.

Mitigation Measures

Database Account Isolation

Separate DB credentials, dedicated service groups, and configure thread pools (e.g., 8C machines, JSF pool 400) to limit connections per cluster.

Flow‑Control at Detail Level

Set throttling thresholds based on load‑test results to keep system stable while meeting throughput requirements.

Request Merging

Combine multiple identical SKU requests into a single DB update to reduce hot‑row concurrency.

Message‑Queue Peak‑Shaving

Split large transactions into smaller ones, serialize processing per SKU, and use ordered or partial‑ordered MQ to avoid deadlocks.

Removing Unique Index from Transaction Log

Replace unique index checks with Redis‑based de‑duplication, acknowledging trade‑offs.

NoSQL Pre‑allocation (evaluated)

Load MySQL data into Redis for flash‑sale level pre‑allocation, but discarded due to consistency challenges.

Implementation Details

Service Interaction

Outbound location requests are sent to inventory via MQ, which processes the strategy, updates inventory rows, writes transaction logs, and acknowledges results. A scheduler polls unfinished requests and rolls back on failures.

Queue Isolation

Each cluster uses independent MQ topics, further divided by job type (order‑routing, automatic outbound, high‑priority). Limits are set per topic to protect the database.

Message Types

Strict ordered messages

Partial ordered messages (used in this project)

Partitioned ordered messages

Performance Test Results

Tests on a 32‑CPU, 128‑GB DB and 4‑CPU, 8‑GB application instances showed that the optimized solution maintained stable CPU usage and throughput across non‑hot‑SKU and hot‑SKU scenarios, significantly reducing lock wait times.

Rollout & Impact

The solution was first deployed in high‑traffic warehouses, then gradually expanded to other clusters, effectively lowering promotion risk and ensuring system stability during peak traffic.