Designing Concurrency‑Safe Balance Updates: SQL, CAS, Optimistic Lock, and Distributed Locks

Part One discusses various approaches to safely increase or decrease an account balance in a concurrent environment.

Direct SQL update – simple increment but issues with negative balances and inability to read the new value.

update tb_balance set balance=balance+20 where balance=#{old_bal} and user_id=#{user_id}

CAS (compare‑and‑swap) – compare old value, update if unchanged, handle ABA problem by checking affected rows and retrying.

oldBal = balService.getBal(userId);
newBal = oldBal + 20;
int count = balService.updateBal(newBal, oldBal, userId);
Assert.businessInvalid(count == 0, "update error");

Optimistic lock – use a version column to detect concurrent modifications and retry on failure.

int count = balService.updateBal(newBal, old_version, new_version, userId);
Assert.businessInvalid(count == 0, "update error");

Redisson distributed lock – wrap the whole balance operation in a Redis lock to guarantee exclusive access across multiple services.

Part Two shifts focus to order‑status updates where many services read the same status and may act on stale data.

Solution ideas include:

Apply a Redis lock around the read‑modify‑write sequence.

Encapsulate order operations in a single “order” class (single entry point) and enforce locking there.

Use MySQL shared (read) locks to block conflicting writes while allowing concurrent reads.

select ... from order where service_order_id = ? lock in share mode;

The article concludes that adding read locks can prevent inconsistent data but may reduce throughput, so locking should be applied judiciously after careful business analysis.