How to prevent race conditions when updating a user’s balance in a high‑concurrency environment?

[dimaashiftiing]

🏷️ Discussion Type

Question

💬 Feature/Topic Area

Code Search and Navigation

Body

We have a REST endpoint that adds or subtracts from a user’s balance (e.g., /api/users/{id}/balance). Under heavy load, multiple concurrent requests for the same user sometimes produce incorrect final balances (lost updates). We’re using PostgreSQL and a single application instance for now.

What are the best strategies to ensure atomic updates without locking the entire table? Code examples would help.

[vgartg]

You’re facing a classic lost update problem. Here are three reliable solutions, ordered from simplest to most flexible

1. Optimistic locking with a version column
   Add an integer version column to the users table. When updating, check that the version hasn’t changed:

UPDATE users 
SET balance = balance + 20, version = version + 1
WHERE id = ? AND version = ?;

If the row count is 0, retry the whole operation (read new balance, recalculate, update).
Works well when conflicts are rare. No explicit locks, good for read‑heavy workloads

2. Use a row‑level SELECT FOR UPDATE
   Wrap the operation in a transaction and lock only the affected row:

BEGIN;
SELECT balance FROM users WHERE id = ? FOR UPDATE;
-- calculate new balance in application
UPDATE users SET balance = ? WHERE id = ?;
COMMIT;

PostgreSQL will queue concurrent requests for the same row. Simple and safe
Downside: reduces throughput if the same user is updated very often

3. Atomic in‑database arithmetic (best for simple increments)
   If your operation is just addition/subtraction, let the database do it atomically:

UPDATE users 
SET balance = balance + ? 
WHERE id = ? AND balance + ? >= 0; -- optional constraint

This is lock‑free and fastest. No race conditions because the update is a single statement
Return the affected row count to know if the balance would have gone negative (if you need that check)

[jutolin]

I agree