How MySQL’s Merged Flash‑Sale Engine Boosts Write Throughput 5×

Background

Flash‑sale (秒杀) is the most typical high‑concurrency scenario for e‑commerce platforms; major events such as Double‑Eleven use flash‑sale capability as a benchmark of database performance. XiaoHongShu’s rapid e‑commerce growth and live‑streaming sales demand order‑processing speeds exceeding 10 k orders per second.

Performance Gains Overview

The merged‑seckill implementation, built on the MySQL kernel, improves write throughput by roughly five times compared with the queued‑seckill approach and up to one hundred times faster than the upstream MySQL community version. The upgrade is completely transparent to business logic: only the MySQL kernel needs to be upgraded, no SQL changes are required.

Performance tests using sysbench’s standard inventory‑deduction model show TPS rising from 4,276 to 23,543 (≈5.5×) with 128 threads, and still a 4.7× gain at 1,024 threads. The optimal thread count is 128‑256 to avoid excessive context‑switch overhead.

Hotspot Bottleneck Analysis

The classic inventory‑deduction transaction consists of BEGIN, INSERT into an inventory‑log table, UPDATE the inventory row, and COMMIT. While INSERTs can run concurrently (different primary keys), the UPDATE targets the same row, creating a critical section protected by a row lock. As concurrency rises, the UPDATE throughput collapses to 100‑200 TPS, causing severe stalls.

begin;
insert into inventory_log value (...); -- insert log record
update inventory set quantity = quantity - 1 where sku_id = ? and quantity > 0; -- deduct inventory
commit;

Comparison of Queued Seckill vs Merged Seckill

Queued seckill improves performance by serializing updates through a queue, but throughput still degrades under extreme concurrency. The merged‑seckill version keeps performance stable by merging multiple SQL statements into a single transaction, eliminating the per‑transaction overhead of row locking.

Overall Design

The merged‑seckill modifies the MySQL transaction, lock, and binlog subsystems. Its advantages include:

Component transparency : All changes are confined to the MySQL kernel; binlog format remains unchanged, so downstream components such as DTS or Canal are unaffected.

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

No SQL changes : The same SQL works with both queued and merged seckill. DBAs can switch between modes at runtime, even running all three (merged, queued, none) on a single instance.

Key Technical Challenges

Data Visibility

MySQL normally provides thread‑local visibility, which is simple but unsuitable for merged‑seckill where multiple threads must share intermediate results. A global cache, scoped to the table, is introduced so that all threads operate on the same in‑memory data structure.

Data Consistency (Leader‑Follower)

The merged‑seckill design follows a Leader‑Follower model. The first thread that acquires the exclusive lock becomes the Leader, reads the InnoDB row, writes the updated value to the global cache, and then releases the lock to allow Followers to read the cached value, apply their own deductions, and write back to the cache. After all Followers finish, the Leader commits the aggregated result in a single 2‑phase‑commit (2PC) transaction, ensuring atomicity.

Row‑Lock Extreme Optimization

By merging updates, the system reduces the number of times the same row lock is acquired. Instead of each transaction acquiring and releasing the lock, a group of transactions shares a single lock acquisition, dramatically lowering lock contention.

Two‑Phase Commit and Parallel Binlog

All transactions in a merged group generate individual binlog entries, but the Leader submits the entire group’s binlog as a single batch, reducing I/O overhead and ensuring atomicity across the group.

Crash Recovery Enhancements

During crash recovery, the system first reconstructs a transaction set from the binlog, then compares it with the redo log. Because the merged‑seckill writes the whole group’s binlog together, recovery must treat the group as an atomic unit; partial rollback would break consistency.

Compatibility and Hints

The merged‑seckill kernel introduces a set of hint keywords that can be used to control the execution path (e.g., forcing merged, queued, or normal update). These hints are backward‑compatible and do not affect existing applications that do not use them.

Overall, the merged‑seckill solution raises flash‑sale write throughput from a few hundred TPS to over 15 k TPS, improves user experience in high‑frequency write scenarios such as inventory, coupons, and likes, and does so without requiring any changes to business‑side SQL.