SQL Fine-Grained Management and Concurrency Control Best Practices

This document outlines the background, benefits, and best‑practice guidelines for fine‑grained SQL management in a MySQL‑based order system that uses MyBatis as the persistence framework.

Background

Two widely used persistence frameworks, JPA and MyBatis, are compared, and MyBatis is chosen because it allows developers to write custom SQL. Over time, SQL complexity grows, leading to problems such as slow queries, missing indexes, and dirty data caused by concurrent updates of a JSON‑style feature column.

Benefits of SQL fine‑grained management

Improved visibility of risky SQL, faster query performance, reduced OOM risk, prevention of MySQL crashes caused by heavy resource consumption, and higher order‑service stability.

Best‑practice recommendations

1. Do not use automatically generated generic SQL. Example of a problematic <select> mapper:

<select id="selectByExample" parameterType="OrderExample" resultMap="BaseResultMap">
  select
  <if test="distinct"> distinct </if>
  <include refid="Base_Column_List" />
  from table_order
  <if test="_parameter != null"> <include refid="Example_Where_Clause" /> </if>
  <if test="orderByClause != null"> order by ${orderByClause} </if>
</select>

2. Make index usage explicit. Ensure every query can be read to see which index it uses.

3. Avoid dynamic SQL unless absolutely necessary. Dynamic <set> blocks make the mapper hard to read and can cause memory leaks.

4. Update statements must list concrete columns; do not use if inside <set> . Correct example:

<update id="update" parameterType="OrderEntity">
    update table_order
    set feature = #{feature,jdbcType=VARCHAR}
    where order_no = #{orderNo,jdbcType=VARCHAR}
</update>

Incorrect example (dynamic <set> with many if checks) is discouraged.

5. Do not use SELECT * ; list required columns explicitly. Example:

<SQL id="Base_Column_List">
  order_no, sub_order_no, user_id, biz_type, pay_tool, pay_status, out_pay_no
</SQL>

6. Avoid unnecessary ORDER BY and multiple‑column ordering. When ordering is required, ensure the ordered column is covered by an index.

7. Use LIMIT to bound result sets, especially when queries do not use a primary‑key index.

8. Prefer batch inserts over row‑by‑row inserts. Correct batch insert example:

INSERT INTO table_order(col1, col2, …) VALUES ('?','?',…), ('?','?',…), ('?','?',…);

9. Optimize LIKE patterns; avoid leading wildcards.

10. Do not concatenate SQL strings in application code; use prepared statements or mapper parameters.

11. Use SELECT COUNT(*) for counting rows.

12. Remove unused mapper methods (e.g., physical deletes) to keep the API clean.

13. Always check the affected‑row count after UPDATE or DELETE operations. Example in Java:

int effect = repository.update(param1, param2);
if (effect < 1) {
    throw new RuntimeException("update operation failed");
}

14. Avoid list.get(0) on unordered results; enforce ordering or use proper pagination.

Concurrency control

Two mechanisms are presented:

• Pessimistic lock – lock the row with SELECT ... FOR UPDATE inside a transaction.

START TRANSACTION;
SELECT feature FROM table_order WHERE order_no = '100' FOR UPDATE;
UPDATE table_order SET feature = new_feature WHERE order_no = '100';
COMMIT;

Pros: guarantees no concurrent overwrite. Cons: possible deadlocks and reduced throughput.

• Optimistic lock – use a version column.

Version‑based update example:

SELECT feature, version FROM table_order WHERE order_no = '100';
UPDATE table_order SET feature = new_feature, version = version + 1
WHERE order_no = '100' AND version = :oldVersion;

A refined approach adds a field‑specific version ( feature_version ) to avoid conflicts on unrelated columns.

UPDATE table_order
SET feature = :newFeature,
    feature_version = feature_version + 1,
    version = version + 1
WHERE order_no = :orderNo
  AND feature_version = :oldFeatureVersion;

Both methods should be chosen based on workload characteristics; pessimistic locking is common in finance, while optimistic locking suits high‑throughput scenarios.

Conclusion

Effective DB‑layer governance—through clear SQL, proper indexing, controlled concurrency, and rigorous testing—significantly improves data correctness and system performance.