Smart user rank computation

Question

I have a card game, where users are ranked by how much games whey won. Overall rating is precomputed in order to be able to load it fast, but I have issues regarding rank computation.

Rank is computed as follows:

SET @userRank = 0;

UPDATE t_ratings AS r
JOIN
  (
    SELECT
        userId, (@userRank := @userRank + 1) as rank
        FROM (
        SELECT
            r.userId,
            r.solvedCount,
            r.playedCount
       FROM
        t_ratings AS r                        
    ORDER BY r.solvedCount DESC , r.playedCount ASC) AS t
  ) AS rt
  ON rt.userId = r.userId
SET r.rank = rt.rank

But recently I've started getting the following error sometimes:

Deadlock found when trying to get lock; try restarting transaction

Therefore I would like to know if there is a better way to compute user ranks to avoid deadlocks?

Answer 1

This is how I would do it in SQL Server... not sure if mySQL has windowed functions as I'm not a mySQL guy.

With    cte As
(
        Select  Rank() Over (ORDER BY r.solvedCount DESC , r.playedCount ASC) As [rank], 
                userID
        From    t_ratings As r
)
Update  tr
Set     [rank] = c.[rank]
From    t_ratings tr
Join    cte c
        On  tr.userID = c.userID