How to add a number to a current value in mysql (multiple times at the same time)?

Question

I'm working on a points system that will give users on my site a ranking based on their points, I'm giving the user +1 points whenever someone checks his profile page. Let's say the user has 200 points, on the same exact time 10 users checked his profile page, from my understanding the code will get 200 adds 1 and the result will be 200 + 1 = 201 if the 10 users are in at the same time, how will the database act?

Logically it will count only one visit because the time users checked the profile the value was 200, so when the MYSQL update happens it will be always 201. Am I correct ?

Answer 1

If you select the existing number of points, add one in the client, and then afterwards write the updated value back to the database then you have a race condition. As you point out, it's possible that some views won't be counted.

Instead you should try to use an atomic update and avoid the problem:

UPDATE user SET points = points + 1 WHERE id = 42

---

An alternative is to read with SELECT ... FOR UPDATE. From the documentation:

If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that permits other transactions to read the examined rows but not to update or delete them. See Section 14.2.8.3, “SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads”