Optimizing Transaction SQL for High‑Concurrency Sales Events: Comparing Two Transaction Approaches

This article examines the high‑concurrency scenario of major sales events (e.g., Double‑11, 618) where the system must sell as many items as possible without exceeding inventory, making the database the primary bottleneck.

Before the analysis, three assumptions are made:

Business servers and the database reside in the same data center.

Network request latency is 3 ms and statement execution time is 0.2 ms, with all operations succeeding.

When the request volume reaches the database limit, TPS is calculated as TPS = 1000ms / lockTime (non‑locking time can be concurrent).

The core transaction flow is: check stock, create an order, decrement stock, and commit; if any step fails, roll back.

Transaction Variant 1 (Insert → Update)

begin;
select stock from goods where id = 1;
if (stock > 0) {
insert into order values(...); // insert order
update goods set stock = stock - 1 where id = 1 and stock > 0; // decrement stock
}
if (updateCount > 0) {
commit;
} else {
rollback;
}

Transaction Variant 2 (Update → Insert)

begin;
select stock from goods where id = 1;
if (stock > 0) {
update goods set stock = stock - 1 where id = 1 and stock > 0; // decrement stock
insert into order values(...); // insert order
}
if (updateCount > 0) {
commit;
} else {
rollback;
}

The difference lies only in the order of the insert and update statements. From a TPS perspective, Variant 1 is superior.

Why Variant 1 Performs Better

Each SQL statement (including commit or rollback ) requires a synchronous network round‑trip from the business service to the database. In Variant 1, only one network request (the commit ) occurs after the row lock, whereas Variant 2 incurs two network requests (the insert and the commit ) while the lock is held.

TPS Calculation

Using the earlier assumptions:

Variant 1

One network request (3 ms) for the commit:

TPS = 1000ms / 3ms = 333.33

Variant 2

Two network requests (2 × 3 ms) for the insert and commit:

TPS = 1000ms / 6ms = 166.67

Thus Variant 1 achieves roughly double the throughput of Variant 2, and the gap widens as more statements are added.

Further Optimization

If the commit could be eliminated (e.g., by relying on auto‑commit or a DBA‑level shortcut), the lock time would shrink to the statement execution time (0.2 ms), yielding:

TPS = 1000ms / 0.2ms = 5000

Such an optimization requires coordination with the database administration team.

Conclusion

When writing a transaction, place row‑locking operations as close to the commit as possible without affecting business logic; this minimizes lock duration, reduces network round‑trips during the lock, and maximizes TPS.