MySQL updating same row by multiple users like counter [duplicate]

Question

I'm trying to increment a like counter for a users post. A post in my MySQL table has a field likes which shows how many likes this specific post has. now what will happen if multiple user like the same post at the same time? I think this will result in a conflict or not increment correctly? How can I avoid this, do I need to lock the row or which options do I have?

my query could look something like this:

UPDATE posts
    SET likes = likes + 1,
    WHERE id = some_value

also when a user unlikes a post this should decrement --> likes -1

Thanks for any help!

Answer 1

It's a simple enough query to run:

UPDATE mytable SET mycolumn = mycolumn + 1;

That way even if you have multiple people liking at the same time the queries won't run at exactly the same time and so you'll get the correct number at the end.

Queries such as these run in fractions of a second, so you don't need to worry about multiple users clicking on them unless you've got millions of users, and then you'll have lots of problems to do with queries.

Answer 2

Someone liking the same post at the same time will only cause issues in long and complex queries.

This should be more than sufficient for an increment otherwise the entirety of SQL would be rendered useless.

UPDATE posts
SET likes = likes + 1
WHERE id = some_value

You could always run this query twice programmaticly and see what happens. I can guarantee that it will go from 0 to 2.

someTableAdapter.LikePost(postID);
someTableAdapter.LikePost(postID);

Answer 3

What you described is called a race condition (as in, race to the finish. Kind of like playing musical chairs with two people, but only one chair). I believe if you research transactions you may be able to implement your code and have pseudo-concurrent likes. Here's a link to the MySQL Manual.

MySQL 5.1 Manual: Transactional and Locking Statements

YouTube: MySQL Transactions

---

"MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.

MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, allowing only one session to update those tables at a time, making them more suitable for read-only, read-mostly, or single-user applications. "

MySQL Manual, 8.7.1: Internal Locking Methods

---

But, if transactions are too much, make sure you are using at least the InnoDB storage engine and you should be alright if you are using an ACID compliant database with the the proper level of isolation.

There are lots of good references, but hopefully I've pointed you in the right direction.

Anthony