I am building a webapp (Angular frontend, Groovy/Spring/Hibernate/MySQL backend) that will allow users to compete against each other over certain activities. Each activity will have 1 winner and 1 loser. I want a live user ranking system that ranks users (1st to last place) depending on their win/loss record/history. The scoring & ranking is actually based on the ELO rating system and is very similar to the flavor of ELO used by the Chess community. I only mention this because calculating each user's individual performance rating/ELO score is a fairly involved computation and is not as simple as merely adding up the number of wins they've score to date or anything simple like that.
It's also important to mention that a user's ranking is something that needs to be stored in the database and can't simply be something achieved by: (1) sorting all users based on their performance ratings score, (2) finding a particular user in the sorted list, (3) ranking == position in sorted list. Ranking needs to be persisted to the DB and updated frequently.
So this live user ranking system needs to:
- Trigger any time any two users compete against each other and the activity determines a winner/loser; and then
- Take the results of that activity/competition and apply a fairly mathematically complex algorithm to determine the new performance rating (overall score) of both users; and then
- Update their ranking in some DB table (by sorting; highest performance rating ranks 1st, lowest performance rating ranks last, etc.). This process is called a re-ranking and affects all users (shifting them up/down).
The first two items above are pretty straight forward: I can handle them easily in the backend/middleware layer. Because re-ranking could take, say, 30 - 60 seconds if we have a large number of users, I'll likely make the reporting of competiton/activity results asynchronous from the re-ranking of all the users. Meaning the backend receives competition results and stores them, then publishes a messages to a broker that a re-ranking must take place. A consumer listening to that broker then reacts to the message by triggering a re-ranking.
But the 3rd item, performing the re-ranking, is where I foresee possible performance issues. This is because if my app has hundreds of thousands of users and again re-ranking takes, say, up to 60 seconds to run, then anytime any two of those users compete against each other the rankings of all users will be affected and all users will get shifted up/down by some number of rankings. It's also totally possible that 2 sets of users compete against each other around the same point of time, and trigger multiple re-rankings around the same point in time).
In this scenario I'm worried about write/lock contention when the DB is updating all the rankings of all the users, but in the meantime the app can't be in a wait state (waiting for rankings to update) and will need to be reading the user/ranking tables, even if they are being written to.
So I ask: what tricks can I employ (either table structure or optimizations, or perhaps programming tricks in a stored procedure, or perhaps something at the JPA/Hibernate/JDBC data layer, etc.) so that a re-ranking can take place at any time (live user rankings) without locking the user/rankings tables? In other words, its perfectly OK if the rankings table reports that User 12345 has a rank of 45 (45th place) even if a re-ranking is in progress that, when complete, will bump User 12345 up to rank 44. I just don't want blocking/contention.