1

I have simple question, I think its not that big of a deal, but I'd still like to ask it as its boggling my mind for past 2 days a lot.

What if I have a poll where instead of inserting answers to MySQL table one per row, it would just change the amount of votes answer got in row with poll data?

So basically my code would just call UPDATE polls SET optionX=optionX+1 WHERE id='$poll_id' instead of INSERT INTO poll_votes (option, ip, poll) VALUES ('X', '$user_ip', '$poll_id') - this is simplified version, but base is the same.

My question is, what happens if 1.000 people would submit their answer at same time. Its there any remote chance of server taking data from MySQL where optionX was not updated by previous call yet, and as the previous call (user) updates it, then new one would override it basically loosing that one vote?

TL;DR: Can this happen?

  1. User 1 sends vote for Option 4
  2. PHP downloads data from MySQL where Option 4 has 49 votes
  3. User 2 sends vote for Option 4
  4. PHP downloads data from MySQL where Option 4 has still 49 votes
  5. PHP finishes updating request and sets MySQL for Option 4 to have 50 votes
  6. PHP Finishes updating second request and sets Option to have 50 votes instead of 51

Main concern for me is, that I am storing all options as one array in one cell, so I can allow users to have stupidly long polls without need to have extremely messy MySQL table structure.

I think chances are really thin, but I am not sure if it can happen or if there are some safeties for such things.

Improve this question
2
  • I edited your question down a little. No need to apologize for asking a question, if we need to migrate it or otherwise change it, we will just do it. Second, asking for off-site resource requests are explicitly off-topic. That was not the core of your question so I simply removed it.
    – user22815
    Jun 4, 2015 at 23:42
  • @Snowman Ah, thanks for explanation and info :) I did read what should I not post here, but I think I missed that one as I am a first time poster here.
    – MiChAeLoKGB
    Jun 5, 2015 at 0:17

1 Answer 1

Reset to default
1

No ACID-compliant database will cause an error or incorrect data in this situation unless you actively try to work against it by explicitly messing with locking hints or other weird stuff that most people do not do.

MySQL is ACID-compliant by default. I have not checked lately, but you may be able to use ancient table types that do not guarantee this. As long as you are using a MySQL version no more than about ten years old and are not pointing a gun at your foot and pulling the trigger, you will not accidentally have a non-ACID-compliant table type.

A: Atomicity. An operation succeeds or fails as a whole. Either the increment works as a unit or it fails as a unit.

I: Isolation. Threads cannot see "partial" results of other threads.

The atomicity ensures that the statement cannot fail halfway through (the Consistency helps here too, e.g. if there is a crash while the SQL is updating a table). Isolation guarantees you do not have the classic threading example of interleaving two increments (which you describe in your question).

Improve this answer
1
  • Ah, so its not possible then that one thread might update table while another thread just downloaded data from it? Thats cool. And I am using MySQL 5.1, so thats not that old.
    – MiChAeLoKGB
    Jun 5, 2015 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.