How Merged Seckill Boosts MySQL Write Throughput by 5× for High‑Concurrency Flash Sales

Background

In 2024 the Xiaohongshu database team found that their custom queue‑based seckill implementation, although ten‑fold faster than the original design, still could not sustain the rapid growth of live‑stream sales, popular note likes, and other high‑frequency purchase scenarios. The target was to handle >10 K orders per second.

Performance Benefit

The merged‑seckill kernel automatically selects the best execution path (merged seckill, queue seckill, or normal update) based on the incoming SQL. Upgrading only the MySQL kernel therefore yields a >5× throughput increase without any SQL changes on the application side.

Benchmark results (sysbench inventory‑deduction model) show:

128 threads: TPS rises from 4 276 to 23 543 (≈5.5×).

1 024 threads: still ~4.7× improvement.

Optimal thread count: 128–256 to avoid excessive context‑switch overhead.

Hotspot Bottleneck Analysis

The classic inventory‑deduction transaction is:

BEGIN;
INSERT INTO inventory_log VALUES (...);  -- log the change
UPDATE inventory SET quantity = quantity - 1 WHERE sku_id = ? AND quantity > 0;  -- deduct stock
COMMIT;

When concurrency rises, the UPDATE on the same row becomes a severe bottleneck, dropping TPS to 100–200 and making the system unusable.

Queue‑Seckill Improvement

The queue‑seckill version stabilises performance by serialising the UPDATE through a per‑row queue, but the row‑lock still limits scalability.

Merged‑Seckill Improvement

In the merged‑seckill design, multiple INSERT statements can run concurrently because they affect different primary keys. The single‑row UPDATE is transformed into a batch operation: several deductions are aggregated into one transaction, eliminating the row‑lock contention.

Overall Design

The merged‑seckill approach merges several SQL statements into a single transaction, requiring modifications to MySQL’s transaction, lock, and binlog subsystems. Its key advantages are:

Zero impact on ecosystem components : All changes stay inside the MySQL kernel; binlog format remains unchanged, so downstream tools (e.g., DTS, Canal) need no upgrades.

Kernel‑only upgrade : No InnoDB format changes, preserving compatibility and rollback capability.

No SQL changes for applications : The merged‑seckill syntax is compatible with the queue‑seckill version, and DBAs can switch modes at runtime.

Cache Visibility

Two problems must be solved for merged‑seckill:

Data visibility : Threads must share a consistent view of inventory data.

Data consistency : Leader‑Follower synchronization must guarantee atomic updates.

Data Visibility Solution

A global cache is added per table. All threads operating on the same table share this cache, whose lifecycle matches the table’s structure, simplifying management.

Leader‑Follower Consistency Protocol

The workflow for a group of concurrent UPDATE statements is:

Three clients issue the same UPDATE. The queue‑PK is bypassed because merged‑seckill is enabled.

Threads compete for an exclusive lock. The first thread becomes the Leader , reads InnoDB data, updates the global cache, releases the lock, and enters a collection state.

Subsequent threads become Followers . Each acquires the lock, copies the global cache to a thread‑local cache, applies its deduction, writes back to the global cache, and releases the lock.

After all Followers have contributed, the Leader re‑acquires the lock, writes the aggregated result to storage via a two‑phase commit (2PC), and wakes the Followers.

Row‑Lock Extreme Optimization

The UPDATE is split into two phases:

Phase 1 – Cache collection : Threads read the global cache, apply deductions locally, and write back to the cache.

Phase 2 – Commit : The Leader writes the aggregated result to the storage engine.

Because Phase 1 of the next group can start while Phase 2 of the current group is committing, overall latency is reduced.

Binlog Parallel Commit

The Leader submits the entire group’s binlog entries as a single batch, enabling parallel persistence of the binlog.

Crash Recovery Optimization

During crash recovery, the binlog group must be treated as an atomic unit. The merged‑seckill writes the whole group’s binlog at once; the Redolog records the before‑and‑after values (e.g., 1000→900), while the binlog records each incremental change (1000→999, 999→998). Recovery therefore requires the complete binlog group to ensure correct rollback or commit.

Seckill Compatibility

The kernel exposes hint keywords that allow toggling between merged‑seckill, queue‑seckill, and normal modes without changing application SQL.

Thanks to the Alibaba Cloud RDS team for technical support and optimization guidance.