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?User 1 sends vote for Option 4PHP downloads data from MySQL where Option 4 has 49 votesUser 2 sends vote for Option 4PHP downloads data from MySQL where Option 4 has still 49 votesPHP finishes updating request and sets MySQL for Option 4 to have 50 votesPHP Finishes updating second request and sets Option to have 50 votes instead of 51Main 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.
Multiple MySQL update calls per second, can some of them be missed?
php;mysql
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).