Geek Answers Handbook

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

I am working on a points system that will give users on my site a rating based on their points, I give the user +1 points when someone checks his profile page. Let's say a user has 200 points, at the same time 10 users checked his profile page, from my understanding the code will receive 200 additions 1, and the result will be 200 + 1 = 201, if 10 users at the same time, how will the database work?

Logically, it will consider only one visit, because time users check the profile, whose value is 200, so when updating MYSQL it will always be 201. Am I correct?

+6
source share
1 answer

If you select an existing number of points, add it to the client, and then write the updated value back to the database, then you will have a race condition. As you noticed, it is possible that some submissions will not be taken into account.

Instead, you should try using atomic update and avoid the problem:

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

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

If you use FOR UPDATE with a storage engine that uses page or row locking, the rows checked by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a general lock, allowing other transactions to read verified rows, but not to update or delete them. See Section 14.2.8.3, “CHOOSE ... FOR UPDATE AND CHOOSE ... BLOCK IN BLOCK READING PROMOTION

+17
source

More articles:


All Articles