How a Simple MySQL UPDATE Solved a Multi‑Threaded Counting Problem

Background

A senior engineer asked the author to implement a feature for the "operate" module: each time a file is processed, a message is sent, the messages are counted, and the count is periodically written to a database.

Problem

The "operate" module is multi‑threaded and updates a shared statistic field directly in the database. Frequent concurrent updates caused data inconsistencies, presumably due to inadequate synchronization.

Proposed Simple Solution

The author recalled a MySQL feature that allows atomic increment of a column using an expression, eliminating the need to read‑modify‑write in application code. The SQL command is: UPDATE table_name SET sum = sum + 5 WHERE id = 1; This statement updates the column "sum" by adding the new count directly in the database, which MySQL executes atomically.

Verification

The author wrote a script to test the statement in the development environment. The tests showed the operation was thread‑safe and performed efficiently enough for the required 5‑second update interval.

Outcome

Because the atomic UPDATE proved safe, the extra counting thread became unnecessary. The "operate" team was instructed to perform the update themselves, simplifying the architecture.

Reflections

The experience underscored two lessons: (1) maintaining a solid knowledge base can prevent over‑engineering solutions, and (2) new techniques should be validated for safety and performance before being adopted in production.