MySQL updating same row by multiple users like counter

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!

`update` queries are atomic. if 5 people hit the same post at the same time, you'll do 5 updates, each of which can't affect the other ones. Not unless you round-trip the data through your client, which would be bad. e.g `update ... set views=views+1` is atomic. `select views; fetch views. views=views+1; update views` wouldn't be, and would allow trashing of counts. — Marc B, Jun 03 '14 at 14:20
Any way you can post your PHP? Maybe there is something going on there? — ProgrammerWannabe, Jun 03 '14 at 14:27
@MarcB Just because `UPDATES` are atomic does not mean you will get all five updates. *Atomicity* as it relates to an `UPDATE` means that updates are "an all nothing" proposition. "... --either the entire transaction is completed or none of it is." --Hess, *SQL Power*, 2007. MySQL could have some sort of locking mechanism that, combined with buffers, could resolve race conditions, but I believe that is what transactions are for. Could be wrong, though. — Anthony Rutledge, Jun 03 '14 at 15:20
@AnthonyRutledge: it also means that if five separate `update foo set field=field+1` hit the DB at the same time, the end result is `field` being incremented by 5. it's not a sequence of "fetch value, update value, write value" where one query can stomp on another's updates — Marc B, Jun 03 '14 at 15:22
different beast. there's no transactions involved with any of the OP's code. It's just 5 updates. The `update` query itself is atomic. Without transactions involved, there is no way that update #2 could interefere with update #1 (or 3,4,5) because the update is atomic - it cannot be reduced to any smaller operations. — Marc B, Jun 03 '14 at 15:35
@MarcB Do you have a definition of atomic that you can share, because if all five queries are atomic yet can occur simultaneously, they will all end up updating the same beginning value by one and attempting to place result in the table. Scaled to 10,000 and you can see why this might be a problem. — Anthony Rutledge, Jun 03 '14 at 15:39
atomic = cannot be divided into smaller operations. you really aren't getting this. the update cannot be subdivided at the "user" level. internally, yes, it will fetch the current value, it'll increment that value, it'll write that value back to the table. but since the update itself is atomic, those internal details are NOT interruptible by any other query. ONE of those "simultaneous" queries will win the race and do its update. so count goes (say) 5->6. then another of the other 4 will win and do 6->7, etc... at NO TIME could it ever go 5->6->5->etc... that's what atomic means. — Marc B, Jun 03 '14 at 15:41
**IF** transactions were used, and one of those updates were rolled back, then yes, you could get a 5->6->5 sequence. but there are no transactions being used here. — Marc B, Jun 03 '14 at 15:42
@MarcB. Cite your reference. Division is not in question. Everything, in your view, is happening at the same time, which means all the `UPDATE` queries begin with the same beginning value. There must be order, or the value they end up putting in the table will be the same. It's called a race condition. — Anthony Rutledge, Jun 03 '14 at 15:43
@MarcB What you are really referring to is the 'I' in ACID, isolation. "Some degree of isolation must exist for simultaneous transactions to be successful." --Hess, *SQL Power*, p. 37, 2007. — Anthony Rutledge, Jun 03 '14 at 15:53
@MarcB An internal implementation of isolation in MySQL with the InnoDB and MyISAM storage engines. http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html — Anthony Rutledge, Jun 03 '14 at 16:35

score 5 · Accepted Answer · answered Jun 03 '14 at 14:23

5

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.

answered Jun 03 '14 at 14:23

Styphon

10,304
9
52
86

OK this is what I wanted to hear. Almost to easy. But I think it will do the job. If it will have a million users I'm sure I'll have a lot of other problems ;) thx!! – user3216026 Jun 03 '14 at 14:32
Transactions? Could they be an answer, too? – Anthony Rutledge Jun 03 '14 at 14:32
@AnthonyRutledge Never used transactions so I don't know. – Styphon Jun 03 '14 at 14:33
@user3216026 Often the simplest solution is the best. Thankfully many smarter people work to make it this easy for us :). – Styphon Jun 03 '14 at 14:34
@Styphon Maybe, but not always. – Anthony Rutledge Jun 03 '14 at 14:55
If you're going to downvote please leave a comment why so I can improve my answer. – Styphon Jun 03 '14 at 15:02
This would not work for a MyISAM table, given the question's scenario. – Anthony Rutledge Jun 03 '14 at 16:50
Can you please explain why it wouldn't? – Styphon Jun 03 '14 at 19:50
@styphon, can you add where clause to your answer, to help newbies. – David Addoteye Nov 13 '15 at 05:17
@Styphon, Could you listed the problems ? – Kabir Hossain May 25 '16 at 05:44

score 3 · Answer 2 · answered Jun 03 '14 at 14:35

3

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);

answered Jun 03 '14 at 14:35

dev_JORD

110
10

1

Good thing someone knows to add a condition/constraint to the update!! – Anthony Rutledge Jun 03 '14 at 14:49
If the OP actually used the "best answer" he/she will find the logic error pretty quickly. Well, I'd hope anyway. – dev_JORD Jun 03 '14 at 16:09

Anthony Rutledge · Answer 3 · 2014-06-03T16:41:50.663

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

ok thanks for your respond. What do you think about @Styphon answer? — user3216026, Jun 03 '14 at 14:34
@user3216026 I think @Styphon's answer depends on `mycolumn` having a stable value, less I begin to miss count *likes*. :-) However, it is clever. Just don't use it at a bank. — Anthony Rutledge, Jun 03 '14 at 14:37
@user3216026 ...especially since @Styphon's answer would wipe out all like totals and make them equal to `mycolmn + 1`. Don't forget to add a `WHERE` constraint to your SQL query. — Anthony Rutledge, Jun 03 '14 at 14:48

MySQL updating same row by multiple users like counter

3 Answers3

Linked