How to make changes to the database sequentially, i.e. line up at a certain moment?

Question

PHP

Hey!
In general, we are currently working on a system where there is a rating, there are users.
All users affect the rating. However, if at the same moment two users performed an action that changes the rating, only one will be counted, because the time that passes between reading the current rating from the database and updating is quite a lot.
I would like to resolve this issue in some way. Line up requests in a queue (I don’t know how yet, most likely in the background) or simply check the current rating 2-3 times before changing the rating to minimize this possibility.
Tell me, I will be very grateful.

Answer 1

Em. what are you doing SELECT rating FROM users WHERE userid = :id, then doing rating++, then doing UPDATE users SET rating=:rating WHERE userid=:id? First, you can just do UPDATE users SET rating=rating+1 WHERE userid=:userid. Secondly, you can line up. When the rating changes, put the action (+ or -), :userid in the queue, then the queue handler will do + or - above the queue rating, but then the user will not see the instant rating change. Thirdly, you can keep user ratings in the cache, make + - in the cache, show from the cache, and synchronize the cache with the database several times a day or on some event.

Answer 2

Don't want to move the rating increment or decrement code to a procedure like incrementRating(@userId)?

Answer 3

You have a table in the database, in it there is a certain field with the current "rating", some event occurs due to which the value of the "rating" field should change (become n ++ or according to some other principle), and if such events occur several at a time, then only one will affect the “rating”?
If so, then you can line up the events that affected the rating into a buffer, then calculate the total impact of the entire buffer on the "rating" and already using this certain sum of influences from all events from the buffer, impose on the current "rating", and, accordingly, for now this happens to accumulate a new buffer.
ps
If you want a more precise answer, please clarify the question.

Answer 4

There is a suspicion that you are not using transactions.
1. Mysql has SELECT… FOR UPDATE
2. Why not use UPDATE… rating = rating + 1 as dali advised above