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:

1. distributed lock in memory, e.g. redis, zookeeper
2. pessimistic lock(select .. for update), you cannot bear update failure while high-frequency collision occurs.
3. optimistic lock(version field), you bears update failure when collision occurs.
4. 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.
5. split data across the machine and synchronized local thread
6. batch fetch and maintain in memory, this cannot avoid concurrency issues but on the other hand can decrease the chances on race condition.
7. 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:

1. mysql last_insert_id
2. building_a_distributed_lock
3. High Performance MySQL