Recently I met concurrency issues while trying to design a table that multiple thread on different hosts can access in a high frequency. Therfore I had to reconsider the solution to this situation.
After digging couples of references, list possbile solutions as below:
- distributed lock in memory, e.g. redis, zookeeper
- pessimistic lock(select .. for update), you cannot bear update failure while high-frequency collision occurs.
- optimistic lock(version field), you bears update failure when collision occurs.
- mysql
last_insert_id function, that only works with the same db connection. This will be infeasible for web application while using db connection pools. - split data across the machine and synchronized local thread
- batch fetch and maintain in memory, this cannot avoid concurrency issues but on the other hand can decrease the chances on race condition.
- You can also create counter table(
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;), and retrieve statistics, just use aggregate queries(retrieve statistics, just use aggregate queries)
When concurrency issues happens in a high frequency rate, it is recommended to use distributed in-memory lock and perssimistic lock in order to keep avoiding update failure and retry logic in optimistic lock.
References: