DongSQL V1.1.0: Engine Enhancements that Supercharge E‑Commerce DB Performance

JD Retail's database team introduces DongSQL V1.1.0, a self‑developed database kernel optimized for high‑traffic e‑commerce scenarios. The release adds several SQL syntax extensions, advanced concurrency controls, and execution‑engine improvements, all validated with extensive performance testing.

1. Syntax Extensions

1.1 RETURNING Clause

The engine extends standard SQL with a RETURNING clause, allowing INSERT, UPDATE, DELETE, and REPLACE statements to return affected rows directly, eliminating extra SELECT round‑trips. Example:

-- INSERT returning auto‑increment ID
INSERT INTO orders (customer_id, order_date) VALUES (1001, NOW()) RETURNING order_id;

-- UPDATE returning modified columns
UPDATE products SET price = price * 1.1 WHERE category = 'electronics' RETURNING product_id, name, old_price, price;

-- DELETE returning deleted rows
DELETE FROM expired_sessions WHERE expire_time < NOW() RETURNING session_id, user_id, expire_time;

Performance tests show TPS improvements of 61% (fixed‑row updates, 16‑thread), 18% (random‑row updates, 128‑thread), and 5‑10% average gains over 20 million operations.

1.2 Hint Syntax Extensions

DongSQL adds a flexible Hint system tailored for e‑commerce, including an Inventory Hint that controls target‑row impact, auto‑commit, and rollback. Example:

UPDATE /*+ TARGET_AFFECT_ROW(1) COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ inventory SET stock = stock - 5 WHERE product_id = 1001 AND stock >= 5;

In a 16‑thread inventory‑deduction benchmark, the hint yields a 215% throughput increase compared with a hint‑free run.

2. Concurrency Control

2.1 CCL (Concurrency Control Layer)

CCL introduces multi‑dimensional rate‑limiting to protect hotspot data during flash‑sale events. Strategies include:

Field‑based limiting: ccl_queue_field(column_name, concurrency) Value‑based limiting: ccl_queue_value(value, concurrency) SQL‑digest limiting: ccl_queue_digest(concurrency) Sample usage:

SELECT /*+ ccl_queue_value(999, 5) */ * FROM products WHERE product_id = 999;

UPDATE /*+ ccl_queue_field(product_id, 8) */ inventory SET stock = stock - 1 WHERE product_id = ?;

Benchmarking shows TPS rising from 573 to 1,337 (133% increase) under 4,096 concurrent flash‑sale requests, while eliminating lock‑contention‑induced system crashes.

2.2 Statement Outline

Statement Outline freezes execution plans for critical SQL, preventing plan instability caused by data changes. DBAs can also inject custom hints for emergency throttling. Example:

CALL dbms_outln.add_index_outline('test_db', '', 1, 'USE INDEX', 'idx_status', '', 'SELECT * FROM orders WHERE status = "PAID"');

CALL dbms_outln.add_optimizer_outline('test_db', '', 1, '/*+ ccl_queue_digest(2) */', 'SELECT * FROM orders WHERE customer_id = 1001');

This feature ensures stable performance and enables manual or automatic rate‑limiting based on SQL fingerprints.

3. Query Optimizations

3.1 Single‑Point Query Bypass

For primary‑key lookups, DongSQL bypasses part of the SQL processing layer and accesses the storage engine directly, reducing CPU overhead. Tests report 20%‑30% QPS gains in container and bare‑metal environments, and 20%‑28% QPS uplift under CPU‑bound high‑concurrency loads.

3.2 Thread‑Pool Redesign

A production‑grade thread pool reuses threads, balances load, and supports priority scheduling, mitigating the overhead of per‑connection thread creation. Benchmark results (8C32G, sysbench, 16 tables × 10 M rows) include:

Read‑only 32‑thread: QPS 141,261 vs 110,658 (27.6% increase)

Read‑only 512‑thread: QPS 131,939 vs 61,580 (114% increase); TP99 latency 118.92 ms vs 297.92 ms (‑60%)

Write‑heavy 512‑thread: QPS 58,166 vs 29,541 (97% increase)

Mixed 256‑thread: QPS 77,961 vs 48,787 (60% increase); TP99 latency 158.63 ms vs 196.89 ms (‑19%)

3.3 Additional Execution Engine Tweaks

Further optimizations include operator refinement, memory‑management improvements, parallel execution enhancements, and an upgraded buffer‑pool with better page‑mutex handling, all contributing to lower I/O contention.

4. Benchmark Summary

RETURNING clause : 61% TPS boost on fixed‑row updates (925→1,490 TPS).

CCL concurrency control : 133% TPS increase in flash‑sale scenario (573→1,337 TPS).

Inventory Hint : 215% TPS uplift for inventory deduction (1,537→4,843 TPS).

Single‑point query : 28% QPS improvement for primary‑key lookups (76,432→98,470 QPS).

5. Future Plans

Continue extending SQL syntax based on business needs.

Integrate machine‑learning models to enhance execution‑plan selection.

Maintain a core kernel team for deep‑level database innovations.

Advance cloud‑native, compute‑storage separation for a high‑performance, low‑cost JD‑specific database product.