How DongSQL’s Syntax Extensions and Concurrency Controls Supercharge E‑commerce Performance

Syntax Extension Optimizations

RETURNING Clause

DongSQL adds a RETURNING clause to INSERT, UPDATE, DELETE and REPLACE statements so that the modified rows are returned directly, eliminating extra SELECT queries.

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

-- UPDATE returning updated rows
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 impact: fixed‑row updates gain 61% higher TPS at 16 concurrent threads and 44% lower latency; random‑row updates improve TPS by 18% at 128 threads; large‑scale updates (20 M rows) see a 5‑10% TPS increase.

Hint Syntax Extension

DongSQL introduces a flexible hint system. The Inventory Hint controls the number of affected rows and automatically commits on success or rolls back on failure.

-- Inventory deduction with hint
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% TPS improvement.

Concurrency Control Optimizations

CCL (Concurrency Control) Mechanism

Multi‑dimensional rate limiting is provided via three functions:

ccl_queue_field(column_name, concurrency)   -- field‑based throttling
ccl_queue_value(value, concurrency)        -- value‑based throttling
ccl_queue_digest(concurrency)              -- SQL‑digest throttling

Example 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 = ?;
SELECT /*+ ccl_queue_digest(10) */ * FROM hot_products WHERE status = 1;

Flash‑sale test with 4096 concurrent requests shows TPS rising from 573 to 1,337 (133% increase) and a marked reduction in hotspot lock contention.

Statement Outline and Custom Hint Management

Statement Outline locks execution plans for critical SQL to avoid plan instability caused by data changes. DBAs can inject custom hints for emergency overload handling.

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');

Benefits: stable performance, intelligent rate‑limiting, enterprise‑grade plan management.

Query Optimization Improvements

Single‑Point Query Bypass

For primary‑key equality queries DongSQL bypasses part of the SQL layer and accesses the storage engine directly, delivering 20‑30% faster execution in container and physical environments and 20‑28% QPS gains under CPU‑bound high concurrency.

Thread‑Pool Optimization

An enterprise‑grade thread pool provides thread reuse, load balancing and priority scheduling, reducing thread‑creation overhead.

Low concurrency (32 threads): QPS 141,261 vs 110,658 (27.6% increase).

High concurrency read‑only (512 threads): QPS 131,939 vs 61,580 (114% increase); TP99 latency 118.9 ms vs 297.9 ms (‑60%).

Write‑heavy scenarios: up to 97% QPS improvement at 512 threads.

Mixed read/write (128‑256 threads): QPS gains 46%‑60% and latency reduction ~19%.

Additional Execution Engine Optimizations

Operator optimizations, improved memory management, refined parallel execution and an upgraded buffer‑pool with better mutex handling reduce I/O lock contention.

Benchmark Summary

RETURNING clause – fixed‑row update: TPS 925→1,490 (61% gain).

CCL concurrency control – flash‑sale: TPS 573→1,337 (133% gain).

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

Single‑point query bypass – primary‑key lookup: QPS 76,432→98,470 (28% gain).

Future Roadmap

Continue syntax extensions driven by business needs.

Introduce AI‑assisted execution‑plan selection.

Maintain a dedicated kernel‑level R&D team for deep optimizations.

Further separate compute and storage for cloud‑native high performance.